Close Current Database is taking a long time to run....

[chanman525]

Hi all, I have a module that imports a lot of .txt file information into several different databases. However, we have recently moved to Windows 7 (Office 07) and it seems that the part of my code that closes each database is taking a very long time to run. Here is my code...

Function transfer_input_table()
On Error GoTo transfer_input_table_Err
Dim appAccess As New Access.Application
Dim trans As Database
Dim rec As Recordset
Dim frm As Form
Dim tb As TextBox
Dim usr As String


    
    DoCmd.SetWarnings False

    'update Status
    Forms!input_date!lblStatus.ForeColor = 65535
    Forms!input_date!lblStatus.Caption = "Importing Receiving History"
    Forms!input_date.Refresh
    Forms!input_date.Repaint

    DoCmd.TransferDatabase acExport, "Microsoft Access", "\\fil0990mp01\data1\home\Planning\Administration\Accts Payable\Transaction Histories\Receive.mdb", acTable, "input_date", "input_date", False
    
    appAccess.OpenCurrentDatabase ("\\fil0990mp01\data1\home\Planning\Administration\Accts Payable\Transaction Histories\Receive.mdb")
    appAccess.RunCommand acCmdCompactDatabase
    appAccess.CloseCurrentDatabase 
    DoCmd.TransferDatabase acExport, "Microsoft Access", "\\fil0990mp01\data1\home\Planning\Administration\Accts Payable\Transaction Histories\Receive.mdb", acTable, "input_date", "tblImportdate", False

The line that is really taking a long time to run is "appAccess.ClosecurrentDatabase". This code also repeats itself numerous times, and each time it pauses for a long time on the CloseCurrentDatabase line.

Has anyone seen this before? Do I need to add or change something to my code to help this run faster?

Thanks for any help you can offer.

Alg

 

[PHV]

What happens if you comment out this line ?
appAccess.RunCommand acCmdCompactDatabase

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

The "CloseCurrentDatabase" still takes a very long time to run.