Autolaunch of macro...

[THoey]

I am working on an automated script that will duplicate an Oracle 7.3.4 table into an Access 97 table. I created the link, wrote the function and macro to perform the emptying and load of the Access table, and am now working on the command line to open the MDB, and run the macro.

The problem is this. The first time I run the macro from within Access, the code works fine, the next time I go to run it, it can't connect to the database. If I run it from the batch file, it fails every time.

The following is the function I wrote (really simple):

Public Function fncRefreshAgentList()
    Dim strSQL As String
    DoCmd.SetWarnings False
    strSQL = "DELETE * FROM AgentList"
    DoCmd.RunSQL (strSQL)
    strSQL = "INSERT INTO AgentList SELECT * FROM eisadmin_tbl_agent_list"
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
End Function

The Macro just sets warnings off, runs the code, sets the warnings back on and then quits Access.

The batch file that I wrote to open the MDB and run the macro is:

"c:\Program Files\Microsoft Office\Office\msaccess.exe" "H:\temp\training.mdb" /x "mcrUpdateAgentList"

Any ideas?

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[huzza]

Hi Terry,
it may be worth trying it with all the warnings on to make sure it doesn't give some message that may explain why it would fail on the second run through.
Also what is the exact message it gives when it can't connect (is it that all time favorite ODBC call failed)?

Does the table contain an autonumber as this wouldn't be reset between runnings i.e. the autonumber maybe trying to start record 1 at 100 for eaxmple.
Richard