VBA Opening Access Table from Excel - Permission Error



I have some VBA code in Excel that opens an Access Table and inputs records
from Excel. The code runs well the first time, but if I want to run it
again, I need to close the workbook and re-open. I am not familiar enough
with the programing connection between Excel and Access to troubleshoot.

Below is my code... It breaks the second time around when it tries to open
the table at "rs.open". Any thoughts?

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
Set rs = New ADODB.Recordset
rs.Open "<<<TABLE NAME>>>", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 1
Do While Len(Range("A" & r).Formula) > 0
With rs
.Fields("CO") = Range("A" & r).Value
.Fields("SRC APP") = Range("B" & r).Value
.Fields("ACCOUNT") = Range("C" & r).Value
.Fields("BRANCH") = Range("D" & r).Value
.Fields("TRAN CODE") = Range("E" & r).Value
.Fields("TR") = Range("F" & r).Value
.Fields("TRAN DATE") = Range("G" & r).Value
.Fields("SEQUENCE") = Range("H" & r).Value
.Fields("DATE") = Range("I" & r).Value
.Fields("AMOUNT") = Range("J" & r).Value
.Fields("DESC") = Range("K" & r).Value
.Fields("DOC NO") = Range("L" & r).Value
End With
r = r + 1
Set rs = Nothing
Set cn = Nothing


I can't tell which version of Access you are using, but I'm guessing it's
pre-2007, based on this line:
"Provider=Microsoft.Jet.OLEDB.4.0; "

Make sure you close everything that you open. You seem to be doing that,
but maybe there is an instance of Access still running in the background.

Something like this at the end of your code may work...

Set mdb = Nothing
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question