Updating Access From Excel

[Shaves]

I have an Excel macro that is used to insert records into one table and update another table in an Access database. An Excel form is loaded with data, the user reviews the info, makes any changes, and then pushes a button. The button kicks off code to create accounting lines which are inserted into the first table and then updates a second table to indicate the invoice has been processed. The database is on a network drive and the data must travel over a network before updating and inserting records. Users are saying it takes up to 30 seconds to update the database.

Does anyone see anything in the code below that could be changed or have any suggestions on how to make the "insert" and "update" process run faster? I'm kind of new at creating cross-application macros; especially ones that are used by field users. Any help or suggestions to resolve this issue would be GREATLY appreciated. Thanks for all of the help.....

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

 

[xlbo]

ones that are used by field users

I think that may be your sticking point. If this is used by people logging in from home to the work network (via vpn or whatever), there's gonna be some lag going on...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question