The code below is a portion of a sample I found online. It reads the database table and puts in excel. The problem is that I
cannot write to the database. When I try to add rst1.Fields(0) = "Bob" I get an error 3251. What do I need to do to be able to write to this DB?
Sincerely,
Bob Hiller
Lifts for the Disabled LLC
Sub Import_AccessData()
Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long
'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(1)
'Path to the database.
stDB = "c:\db1.mdb"
'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"
'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT * FROM table1"
'Clear the worksheet.
wsSheet1.Range("A1").CurrentRegion.Clear
With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With
With rst1
.Open stSQL1, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With
With wsSheet1
.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
End With
rst1.Fields(0) = "Bob"
'Release objects from the memory.
rst1.Close
Set rst1 = Nothing
cnt.Close
Set cnt = Nothing
End Sub
Bookmarks