VBA Opening Access Table from Excel - Permission Error

[adam6b]

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; " & _
"<<<DATABASE FILE LOCATION>>>;"
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
.AddNew
.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
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

 

[ryguy7272]

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

mdb.Quit
Set mdb = Nothing
End Sub

 

[adam6b]

I am using Excel 2007.. Using your code below, what do I need to set mdb
equal to?