Replies

Any commonality between the users that are seeing the lag time? Anything that seperates them from users who don’t see the lag time? Say, version of Outlook running on the client, method of connectivity, membership in distribution or security groups?

Any commonality between the users that are seeing the lag time? Anything that seperates them from users who don’t see the lag time? Say, version of Outlook running on the client, method of connectivity, membership in distribution or security groups?

[indent]

---

DoCmd.OutputTo acTable, strTableName, “HTML(*.html)”, “F:CenterExportLogsstrTableName.html, False, “””

---

[/indent]

Try: DoCmd.OutputTo acTable, strTableName, “HTML(*.html)”, “F:CenterExportLogs ” & strTableName & “.html “ , False, “”

[indent]

---

DoCmd.OutputTo acTable, strTableName, “HTML(*.html)”, “F:CenterExportLogsstrTableName.html, False, “””

---

[/indent]

Try: DoCmd.OutputTo acTable, strTableName, “HTML(*.html)”, “F:CenterExportLogs ” & strTableName & “.html “ , False, “”

Ah, the beauty of the Search function! My situation was similar, but for multiple MSQuery query tables on multiple worksheets. I surrounded the code from Hans with the below and it worked wonderfully — cheers!

Dim strOldPath As String
Dim strNewPath As String
strOldPath = "C:Excel"
strNewPath = "C:Databases"

Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets
Dim strName As String

'Loop through each worksheet
For Each wSht In allwShts
wSht.Activate

    For Each QueryTable In ActiveSheet.QueryTables
        
        strName = QueryTable.Name
        With ActiveSheet.QueryTables(strName)
            .CommandText = Replace(.CommandText, strOldPath, strNewPath)
            .Connection = Replace(.Connection, strOldPath, strNewPath)
        End With
        MsgBox strName & "Complete"
        
    Next

Next wSht

MsgBox "Every MSQuery has been changed.", vbInformation

Ah, the beauty of the Search function! My situation was similar, but for multiple MSQuery query tables on multiple worksheets. I surrounded the code from Hans with the below and it worked wonderfully — cheers!

Dim strOldPath As String
Dim strNewPath As String
strOldPath = "C:Excel"
strNewPath = "C:Databases"

Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets
Dim strName As String

'Loop through each worksheet
For Each wSht In allwShts
wSht.Activate

    For Each QueryTable In ActiveSheet.QueryTables
        
        strName = QueryTable.Name
        With ActiveSheet.QueryTables(strName)
            .CommandText = Replace(.CommandText, strOldPath, strNewPath)
            .Connection = Replace(.Connection, strOldPath, strNewPath)
        End With
        MsgBox strName & "Complete"
        
    Next

Next wSht

MsgBox "Every MSQuery has been changed.", vbInformation

Assuming you have a time/date stamp, transaction# and customer# in the transaction table, base a new query on the transaction table and bring down the fields in the order that I listed them, making sure the time/date stamp is the furtherst to the left. Sort descending by date/time stamp. Add criteria so you select transactions for the customer you wish. Right click in the top pane of the query designer and chose Properties. You’ll see a Top Values property you can set — enter the number 5 to get the last five transactions.

Assuming you have a time/date stamp, transaction# and customer# in the transaction table, base a new query on the transaction table and bring down the fields in the order that I listed them, making sure the time/date stamp is the furtherst to the left. Sort descending by date/time stamp. Add criteria so you select transactions for the customer you wish. Right click in the top pane of the query designer and chose Properties. You’ll see a Top Values property you can set — enter the number 5 to get the last five transactions.

Straight away, let me say that my Exchange knowledge is pretty shallow. After the unexpected “retirement” of our network admin, we ran into the same trouble. I would hope that the period for which bad mail is kept, or the choice not to keep bad mail at all would be a user configurable option in Exchange; a consultant told us it wasn’t and that our only choice was to delete the Bad Mail folder and create a new one. I wrote this batch file (!!) that does just that; we use Scheduled Tasks to kick it off each morning at 2 AM. Best of luck!

REM Purpose: To remove the BadMail file in Exchange
REM          which tends to get filled with files in 
REM          accordance with MS KB# 324958.
REM Created By: Shane Sargent
REM Created On: 01/23/2004
REM 
REM Modified By:
REM Modifed On:
REM Modified Comment:

REM @echo off

REM *** Change directory to correct spot ***
chdir /d C:Program FilesExchsrvrMailrootvsi 1

REM *** Rename old BadMail directory ***
rename BadMail BadMail_Old

REM *** Create new BadMail directory ***
mkdir BadMail

REM *** Delete contents of, and old BadMail directory;
REM do it in quiet mode and don't ask permission to delete the folder. ***
rmdir /s /q BadMail_old

Edit — link to Knowledge Base article: KB 324958

Straight away, let me say that my Exchange knowledge is pretty shallow. After the unexpected “retirement” of our network admin, we ran into the same trouble. I would hope that the period for which bad mail is kept, or the choice not to keep bad mail at all would be a user configurable option in Exchange; a consultant told us it wasn’t and that our only choice was to delete the Bad Mail folder and create a new one. I wrote this batch file (!!) that does just that; we use Scheduled Tasks to kick it off each morning at 2 AM. Best of luck!

REM Purpose: To remove the BadMail file in Exchange
REM          which tends to get filled with files in 
REM          accordance with MS KB# 324958.
REM Created By: Shane Sargent
REM Created On: 01/23/2004
REM 
REM Modified By:
REM Modifed On:
REM Modified Comment:

REM @echo off

REM *** Change directory to correct spot ***
chdir /d C:Program FilesExchsrvrMailrootvsi 1

REM *** Rename old BadMail directory ***
rename BadMail BadMail_Old

REM *** Create new BadMail directory ***
mkdir BadMail

REM *** Delete contents of, and old BadMail directory;
REM do it in quiet mode and don't ask permission to delete the folder. ***
rmdir /s /q BadMail_old

Edit — link to Knowledge Base article: KB 324958

Mmmmm…bananas! The trick is to embed the WHEN…ELSE statement withing the CASE statement. OK, let’s make a fake table to play with. Run this SQL script to make a table named Product that has 3 fields (an identity field named ID, and two fields to hold text values named Color and Durability):

CREATE TABLE [dbo].[Product] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Color] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Durability] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Populate the new table. In the first record, make Color be “Yellow” and Durability be “Fragile”. Now populate a few more records; go with: Yellow/Durable, Green/Fragile, and Green/Durable.

If the Access query looks like:

SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
  AS WhatIsIt
FROM Product

then the SQL Server query looks like this:

SELECT *, 
  CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
      'Banana'
    ELSE
      'Not Banana'
  END
AS WhatIsIt
FROM Product P

Mmmmm…bananas! The trick is to embed the WHEN…ELSE statement withing the CASE statement. OK, let’s make a fake table to play with. Run this SQL script to make a table named Product that has 3 fields (an identity field named ID, and two fields to hold text values named Color and Durability):

CREATE TABLE [dbo].[Product] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Color] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Durability] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Populate the new table. In the first record, make Color be “Yellow” and Durability be “Fragile”. Now populate a few more records; go with: Yellow/Durable, Green/Fragile, and Green/Durable.

If the Access query looks like:

SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
  AS WhatIsIt
FROM Product

then the SQL Server query looks like this:

SELECT *, 
  CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
      'Banana'
    ELSE
      'Not Banana'
  END
AS WhatIsIt
FROM Product P

Without knowing the data structure of the large table or the types of queries you’re running against it, it’s a bit tough to say, so I’ll speak in generalities.

(1) Judicious use of indexes may speed the queries, though will increase the size of the Access database.
(2) Splitting the large data table into multiple smaller tables may help out. It may violate some basic relational design theory, but, often performance counts more than theory.
(3) Examine the way you apply criteria in your queries to squeeze out some performance gains. For example, if you’re applying criteria to a numeric field with IN(3, 25, 36) you should put the value that occurs most often in the first position; same principle applies to immediate if (IIF) statements.

Or after those considerations you may just elect to upsize to SQL Server, Oracle, DB2 or MySQL. Best of luck!

Without knowing the data structure of the large table or the types of queries you’re running against it, it’s a bit tough to say, so I’ll speak in generalities.

(1) Judicious use of indexes may speed the queries, though will increase the size of the Access database.
(2) Splitting the large data table into multiple smaller tables may help out. It may violate some basic relational design theory, but, often performance counts more than theory.
(3) Examine the way you apply criteria in your queries to squeeze out some performance gains. For example, if you’re applying criteria to a numeric field with IN(3, 25, 36) you should put the value that occurs most often in the first position; same principle applies to immediate if (IIF) statements.

Or after those considerations you may just elect to upsize to SQL Server, Oracle, DB2 or MySQL. Best of luck!

I do have a full version of Adobe, and nope, no dice.

Once the PDF is created, I can manually add bookmarks for where the value in the header section changes, or split the big PDF into multiple PDFs, but I have to believe that some person more clever than I has automated that process and is willing to make a reasonable profit for their efforts.