appending data

[Thai]

Hello I have two sql statements that run one right after another to append records to an ms access table from another table. The problem is that if I let them run immediately after each other only the first one gets through correctly, but if I put a 5 second timer between each append, they all go in. Why is this and is there a fix?

code that fails:
cn.execute strSQL1
cn.execute strSQL2
cn.execute strSQL3
cn.execute strSQL4

code that works:
cn.execute strSQL1
sleep(5)
cn.execute strSQL2
sleep(5)
cn.execute strSQL3
sleep(5)
cn.execute strSQL4


sleep(x) is a function I wrote that uses a timer object to pause for 5 seconds.

Thanks,
Thai

[Clunietp]

try setting the buffer size to 64 before executing the SQL statements. This should minimize buffering, and write the changes to disk sooner...

changes must be made in increments of 64

this is all I can think of, but maybe it might help...if it does, please let us know

    Dim cn As Connection
    Set cn = New Connection
    
    cn.Open "Provider=Microsoft.Jet.oledb.4.0;data source=nwind.mdb"
    cn.Properties("Jet OLEDB:Max Buffer Size") = 64

[Thai]

cool thanks Tom, can you also see if you know the answer this other problem:

When I link a table using ADO/ADOX with the ADOX.Catalog and then runt he SQL statement to append using the data in that linked table immediately after, it says it can't find the linked table, but if i wait 5 seconds, it will run fine. same type of problem, different place, any answers?

Thanks again,
Thai

[Thai]

Unforunately, setting the max buffer size did not work . Any other ideas? I didn't mention it earlier but the cn is an ado connection.

Thanks,
Thai

i did face the same problem too, i think the reason is that ADO executes commands asynchroniously (the same code would work perfectly with DAO, by the way).

A quick and dirty solution could be, to close and reopen the connection between your queries (of course a performance penalty). another way could be to play around with ADO events to find out when the previous query has finished executing, or try seperate transactions for each query.

hope one of my suggestions helps,

Sascha

[Clunietp]

unfortunately, I can't recreate the problem

here is my code, it runs fine

    Dim cn As New Connection
    Dim i As Long
    
    cn.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=Nwindnew.mdb"
    
    For i = 1 To 1000
        cn.Execute "Insert into Table1 values ('asdf" & i & "', 'qwer" & i & "', 'zxcv" & i & "')"
    Next i

I get no errors and all my records are in the database. The database is an Access 2000 DB

if you want to send me your project, I could take a look

[email protected]

[Thai]

Actually, I found out why the appending problem was happening. When linking a table with the ADO/ADOX like I mentioned earlier, it takes a bit for it to reflect the changes. This also happens when you DELETE a table with ADO/ADOX, so the sql statement was using the same table twice or three times when it ran becaue the DELETE/LINK sequence had not kicked in yet. That is why only a few records from the different tables appended. Thank you for all of your help though, I've figured out a solution to the linking/deleting problem which fixed everything else

Thai