Bulk Insert/update in Access

[Jhakda]

Hi All
I'm fetching lot of records from a central DB and i have to insert/ update these records in my local Access DB. Right now, i'm opening and closing 1 connection per record i insert/update in Access. I know this is wrong. In Java(which i'm more used to) i have bulk insert/update option(i keep adding to a batch and insert/update in 1 shot). I'm looking for a similar functionality in VBA--> Access. Also, to maintain atomicity of the operation, i have to roll back all the inserts/updates if an exception occurs midway through (i insert in 3 different tables). I know howto achieve this in Java, but require help in VBA.
Can someone throw some light?
Connection code is listed below. It expects a well formed SQL statement(strSQL )
Thanks a Lot

Jhakda

Dim objConnection As New ADODB.Connection
    Dim objCommand As New ADODB.Command

On Error GoTo ErrHandle


    Set objConnection = OpenConnection("C:\MyDB.mdb", "Microsoft.Jet.OLEDB.4.0")

    If strSQL = "" Then Exit Function
    With objCommand
        .ActiveConnection = objConnection
        .CommandType = adCmdText
        .CommandText = strSQL
        .Execute
    End With
    InsertUpdateSQL = True
    Set objCommand = Nothing
    CloseConnection
    Exit Function

[QVeen72]

Hi,

Not sure about this, but you can try..
Instead of Command object.. Try RecordSet object of ADO.. You can Open the recordset in adLockBatchOptimistic mode (for the Dest DB) , disconnect it from the Database ..
Now add all the records.. manipulate records.. and Then Reconnect it to the Database, and use RST.UpdateBatch

This would definately Increase the speed...

I did not get you.. what you mean by :
"i'm opening and closing 1 connection per record i insert/update in Access"...?

Regards
Veena

[Jhakda]

Hi
Thanks for your help.
I will try out and let you know
When i said i'm opening 1 connection per record, i meant was that when i am inserting/updating the records, right now, i'm opening 1 connection, doing the operation and closing it
then i open anotehr connection for the second one and so on. I know this is wrong, thats why the question

Jhakda

[Jhakda]

Hi
Anotehr request, can i get some code sample?
I'm a bit rookie.Managed with Google and some help from others till now, Not much into VB
Jhakda