ADO Connection Opens as Read Only

[adam6b]

Below is the vba I am using to connect to an Access 2007 database from Excel
2007 to import data to a table. The code works well the first time I run it,
but on subsequent runs, it fails at ".AddNew" with a run-time error of 3251.
That sounds like the issue is the connection method, but I think
adOpenDynamic and adLockOptimistic should work... Also, if I run this code
in excel and then try to open my database, it will only open as Read Only. I
am not very familiar with this coding or methodology; I had to copy this
section in. Any thoughts would be appreciated...


Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=<<<FILE LOCATION>>>;Persist Security Info=False;"
Set rs = New ADODB.Recordset
rs.Open "<<<TABLE NAME>>>", cn, adOpenDynamic, 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

 

[mie via AccessMonster.com]

My guest is..

Set rs = NEW ADODB.Connection
'--put this line.
rs.CursorLocation = adUseServer
rs.Open "<<<TABLE NAME>>>", cn, adOpenDynamic, adLockOptimistic,
adCmdTable

I am using MySQL and not M.Access as backend. as far as i know you need to
set cursor location.. so thats is my guest.

 

[mie via AccessMonster.com]

ops typo,, not guest but guess

My gues is..

Set rs = NEW ADODB.Connection
'--put this line.
rs.CursorLocation = adUseServer
rs.Open "<<<TABLE NAME>>>", cn, adOpenDynamic, adLockOptimistic,
adCmdTable

I am using MySQL and not M.Access as backend. as far as i know you need to
set cursor location.. so thats is my guest.

Below is the vba I am using to connect to an Access 2007 database from Excel
2007 to import data to a table. The code works well the first time I run it,

[quoted text clipped - 36 lines]

cn.Close
Set cn = Nothing