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