Excel / Access / Begin Trans 2

[Shaves]

I have an Excel macro that inserts and updates records in an Access Database using ADO.

Users have commented about the performance when these tasks are completed.

I have found some commands for Access (BeginTrans, CommitTrans) that may speed up the performance.

My question is (1) can I use these commands in Excel to enhance the performance in Access, (2) Can this be done with ADO?, and (3) Any suggestions on getting started would be GREATLY appreciated.

Thanks for the help........

 

[JerryKlmns]

Shaves

Use the Execute method against the connection object and use the options adCmdText+adExecuteNoRecords = 1 + 128 = 129

strSQL="INSERT INTO ... "
Conn.Execute strSQL, , 129

so the provider wouldn't have to evaluate what type is strSQL (table-query-SQL statement) and do not return any records.

Avoid any recordset objects, since you are running action queries. BeginTrans - CommitTrans - RoleBackTrans wont speed up process.
Of course other processing may need fine tumming but I cant geuss any more on your code

 

[Shaves]

Here is the code I'm using. Do you see anything that could speed this up. The database is on a network drive and the users are saying it takes up to 30 seconds to update the database.

Dim Connection As New ADODB.Connection
Dim RecSet As New ADODB.Connection
Set Connection = New ADODB.Connection

Connection.ConnectionString = ConnectionString
Connection.Open

Dim MyItems As Integer
Dim x As Integer
MyItems = lstVchUp.ListCount - 1
x = 1

For vIdx = 1 To lstVchUp.ListCount - 1

Dim VL(1 To 18)

B = B + 1

VL(1) = txtVndr
VL(2) = txtLoc
VL(3) = lblInvNo

Dim ID As Date
ID = Cells(a, 21)

VL(4) = lblInvDte
VL(5) = Val(lblPayAmt)
VL(6) = txtAccDte
VL(7) = txtPymtMsg
VL(8) = lstVchUp.List(vIdx, 0)
VL(9) = lstVchUp.List(vIdx, 1)

If UCase(lstVchUp.List(vIdx, 0)) = "VMTIRERE" Then
VL(10) = lstVchUp.List(vIdx, 8)
End If

VL(11) = Val(lstVchUp.List(vIdx, 2))
VL(12) = lstVchUp.List(vIdx, 3)
VL(13) = lstVchUp.List(vIdx, 7)
VL(14) = lstVchUp.List(vIdx, 4)
VL(15) = Val(txtMileage)
VL(16) = txtUnitA
VL(17) = txtUnitB
VL(18) = txtUnitC

vSql = "INSERT into [VoucherLines] ([VendorID],[VendorLocation],[InvoiceNumber],[InvoiceDate],[GrossInvAmt],[AcctDate],[PaymentMessage],[ItemID],[Description],[QuantityVouchered],[DistribAmt],[Acct],[DeptID],[ProdCode],[Qty_Mileage],[Unit#],[Claim#],[VoucherComments]) VALUES (""" & VL(1) & """,""" & VL(2) & """,""" & VL(3) & """,""" & VL(4) & """,""" & VL(5) & """,""" & VL(6) & """,""" & VL(7) & """,""" & VL(8) & """,""" & VL(9) & """,""" & VL(10) & """,""" & VL(11) & """,""" & VL(12) & """,""" & VL(13) & """,""" & VL(14) & """,""" & VL(15) & """,""" & VL(16) & """,""" & VL(17) & """,""" & VL(18) & """)"

Set RecSet = Connection.Execute(vSql, dbrows, adCmdText Or adExecuteNoRecords)

vSql = ""
Set RecSet = Nothing
Erase VL

x = x + 1

Next vIdx

tmp = "VOUCHERED"

vSql = "UPDATE [BSFBillingHeaderFile] SET [VoucherStatus] = """ & tmp & """, [VoucherID] = """ & eEmpl & """, [VoucherDate] = #" & ThisDate & "# WHERE [InvoiceNo]= """ & vInvNum & """ "

Set RecSet = Connection.Execute(vSql, dbrows, adCmdText Or adExecuteNoRecords)

Cells(vStrRow, 118) = "VOUCHERED"

Connection.Close

Thanks for the help....

 

[SkipVought]

Thanks Jerry! ==> *

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}