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

Recommended Answers

All 3 Replies

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.