i have some problems with a database system i have created.
I have two databases:
1 - local - sits on the file server and stores customer names etc
2 - Live - collects data via an ASP page
Then i have a routine that downloads database and appends the data from the live to the local.
1:30am Live is downloaded
2:00am Append Queries are run (Two Queries via one Macro)
2:10am PSKIll (Batch File) is run to kill access
now what happens is the procedure happens and then two lock files are created on both the databases. when you enter the local databases these clear. but the live database is corrupted.
The error: (appears when you try and open the live file)
AOIndex is not an index in this table
Even using jetCompact does not clear it. I am forced to manually delete the file and copy a new one over. this is a problem if this is happening everyday as i am the only technical person on site, and i am not their too often.
Can anyone tell me why this happening and how to stop it?? i have looked around and have found nothing so far!!
Can you define "kill Access". I've had problems killing Access with a process manager whilst it has a database open in the past. Problems ranged from a corrupt database that could be repaired to a completely dead .mdb file. It sounds like you're doing some damage to whatever table AOIndex is on.
One alternative might be to run a local script to perform all the updates/appens/whatever instead of relying upon Access to do it. You can quickly write a VBScript file that is executed with wscript.exe and connects to several databases and drags data from one to the other, and then cleanly closes down the connections.
i am using PSKill a Dos command (run from a batch file) that kills any instance of MS Access.
where would i find information on the VBScripts on how to write one and do the close etc. I just need to make sure that it is clean and works i dont care if it needs more work as long as i can go in and do what i have too.
Yes, I know pskill. It definitely sounds like the act of not closing down the database cleanly is corrupting your database file. As for the VBScript, you might want to check out the ASP forums for some ideas. If you create an ASP file but name it myfile.vbs instead, with a few tweaks (notably, not being able to access any of ASP's built-in objects such as Application and Session) you should be able to connect to two different databases and run your updates. Something like this:
Set cn1 = CreateObject("ADODB.Connection")
Set cn2 = CreateObject("ADODB.Connection")
cn1.Open <ConnectionString to first database>
cn2.Open <ConnectionString to second database>
Set rs = CreateObject("ADODB.Recordset")
rs.Open <query on first database>, cn1
Do Until rs.EOF
' read from first database and execute SQL to append to the second
Set rs = Nothing
Set cn1 = Nothing
Set cn2 = Nothing
You'll need to execute the script with the Windows Scripting Host (wscript.exe). A tip: if it doesn't seem to work, check any anti-virus software you have running isn't disabling scripts. That one had me stumped for a few hours one morning