Bulk insert records into Access using Vbscript?

To argue for my proposal to use a command in a transaction, I wrote this script.

To argue for my proposal to use a command in a transaction, I wrote this script: Dim sAct : sAct = "trout" If goWAN. Exists( "a" ) Then sAct = goWAN( "a" ) Dim nRecs : nRecs = 10 If goWAN. Exists( "n" ) Then nRecs = CLng( goWAN( "n" ) ) Dim sMFSpec : sMFSpec = goFS.

GetAbsolutePathName( "..\data\ut. Mdb" ) Dim oConn : Set oConn = CreateObject( "ADODB. Connection" ) Dim oRs : Set oRs = CreateObject( "ADODB.

Recordset" ) Dim nRec, oCmd, nRA, aData, oParm oConn. Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sMFSpec Set oRs. ActiveConnection = oConn oConn.

Execute( "DELETE FROM tLines" ) WScript. Echo "#Recs:", oConn. Execute( "SELECT COUNT(SampleText) FROM tLines" ).

Fields( 0 ) WScript. Echo sAct Select Case sAct Case "trout" Case "bob" oRs. CursorLocation = adUseClient oRs.

CursorType = adOpenKeySet oRs. LockType = adLockBatchOptimistic Case "eh" End Select WScript. Echo "oRs.

CursorLocation: ", oRs. CursorLocation WScript. Echo "oRs.

CursorType: ", oRs. CursorType WScript. Echo "oRs.

LockType: ", oRs. LockType Select Case sAct Case "trout", "bob" oRs. Open "tLines", oConn, , adLockBatchOptimistic For nRec = 1 to nRecs oRs.

AddNew oRs( "SampleText" ) = "This is line " & nRec Next oRs. UpdateBatch oRs. Close Case "eh" oConn.

BeginTrans Set oParm = CreateObject( "ADODB. Parameter" ) With oParm . Name = "A" .

Type = adVarChar . Value = "" . Direction = adParamInput .

Size = 100 End With Set oCmd = CreateObject( "ADODB. Command" ) With oCmd Set . ActiveConnection = oConn .

CommandText = "INSERT INTO tLines (SampleText) VALUES (?)" . CommandType = adCmdText .Parameters. Append oParm End With ReDim aData( 0 ) For nRec = 1 to nRecs aData( 0 ) = "This is line " & nRec oCmd.

Execute nRA, aData, adExecuteNoRecords + adCmdText Next oConn. CommitTrans End Select WScript. Echo "#Recs:", oConn.

Execute( "SELECT COUNT(SampleText) FROM tLines" ). Fields( 0 ) WScript. Echo "First:", oConn.

Execute( "SELECT TOP 1 * FROM tLines" ). Fields( 0 ) oConn. Close called with /n:200 and /a:trout it shows: #Recs: 0 trout oRs.

CursorLocation: 2 oRs. CursorType: 0 oRs. LockType: 1 ... xpl.

Vbs(246, 11) Provider: Number of rows with pending changes exceeded the limit. So I think, I reproduced your problem correctly. For /a:bob: #Recs: 0 bob oRs.

CursorLocation: 3 oRs. CursorType: 1 oRs. LockType: 4 #Recs: 200 First: This is line 1 xpl.

Vbs: Erfolgreich beendet. (0) 19.74219 secs So setting oRs. CursorLocation = adUseClient oRs.

CursorType = adOpenKeySet oRs. LockType = adLockBatchOptimistic as Bob (and Microsoft) adviced is one solution to your problem. To get some speed, I put an command into a transaction: oConn.

BeginTrans Set oCmd = CreateObject( "ADODB. Command" ) ... ReDim aData( 0 ) For nRec = 1 to nRecs aData( 0 ) = "This is line " & nRec oCmd. Execute nRA, aData, adExecuteNoRecords + adCmdText Next oConn.

CommitTrans Result: #Recs: 0 eh oRs. CursorLocation: 2 oRs. CursorType: 0 oRs.

LockType: 1 #Recs: 200 First: This is line 1 xpl. Vbs: Erfolgreich beendet. (0) 1.47656 secs From 20 to 2 secs (without any properties fiddling) seems not bad to me.

Oh that is brilliant! Thanks very much. Really appreciate the work you put into that.My script has gone from taking so long I could make a cup of tea and come back and it would still be running to about 5 seconds.

– Doctor Trout Jul 5 at 18:10.

If you are using OLEDB then you need to set the CursorLocation property to adUseClient as per the following KB article: support.microsoft.com/kb/261297 You might also consider running in smaller batches if it's slow doing 100k at a time EDIT: yes, adUseClient needs to be defined as = 3, or just use the number 3 in it's place.

So I came across that article, and I tried adding in: rs. CursorLocation = adUseClient but I got an error saying that "arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" and I couldn't quite figure out what was causing that. – Doctor Trout Jul 4 at 17:51 Actually, I think this might be the answer, but for some reason "rs.

CursorLocation = adUseClient" doesn't work, instead I have to use "rs. CursorLocation = 3" - let me just test this... – Doctor Trout Jul 4 at 19:54 It's still slow as hell though... god I hate Access... – Doctor Trout Jul 4 at 20:26 Perhaps consider firing a bunch of insert statements at the database instead of using a recordset object? – Treborbob Jul 4 at 8:19.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.