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

What error is it giving you. everything looks fine to me

"adam6b" wrote:

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

Mike,

It is giving me a permission error, like I do not have permission to open
the table. Again, it only occurs the second time I run the macro. It is
like Excel still has the table open and it is locked or something...

What do you think?

"Mike" wrote:

What error is it giving you. everything looks fine to me

"adam6b" wrote:

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