Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002

    Unanswered: AOIndex is not an index in this table

    Hi all,

    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!!



  2. #2
    Join Date
    Jul 2002
    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.


    edit: spelling

  3. #3
    Join Date
    Jul 2002
    Hi Mulligan,

    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.



  4. #4
    Join Date
    Jul 2002
    Hi Rob.

    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:
    Option Explicit
    Dim cn1
    Dim cn2
    Dim rs
    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

    I hope this helps.


  5. #5
    Join Date
    Jul 2002
    Hi Mulliagan,

    i took your suggestion last night, and i was being a tad dim as i know how to programatically attach to a database and run SQL (Duh!! my asp knowledge went out the window )

    the tables from the second database are actually linked into the local one so i was able to create a connection to that one and run two queries.

    set objConn = createobject("adodb.connection")"DSN=ambromark")

    query = "INSERT INTO tbl_aspinfo (User_ID, Event, Updated, [Date])"
    query = query & " SELECT tbl_web_info.User_ID, tbl_web_info.Event, tbl_web_info.Updated, tbl_web_info.Date"
    query = query & " FROM tbl_web_info"

    query1 = "INSERT INTO tbl_asp_unsubscribe (User_ID, Event, Updated, [Date/Time])"
    query1 = query1 & " SELECT tbl_web_unsubscribe.User_ID, tbl_web_unsubscribe.Event,"
    query1 = query1 & " tbl_web_unsubscribe.Updated, tbl_web_unsubscribe.[Date/Time] "
    query1 = query1 & " FROM tbl_web_unsubscribe"

    set objConn=nothing

    and then close the database connection. This runs cleanly on my machine, but now for the big test of the server. We will have fun and games i bet

    thanks for all your help. if you have any suggestions about my code please feel free to tell me, i always like a bit of feedback!



  6. #6
    Join Date
    Jul 2002
    No problem. It looks good. Let me know if it works or not


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts