Read/Write Access DB from VBA in Excel
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?
Lifts for the Disabled LLC
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.
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
.Open stSQL1, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
rst1.Fields(0) = "Bob"
'Release objects from the memory.
Set rst1 = Nothing
Set cnt = Nothing
Last edited by excelbobabc; 10-14-2008 at 07:59 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By greenbirdcity in forum Excel General
Last Post: 10-07-2008, 10:45 PM
By chrismann85 in forum Excel Programming / VBA / Macros
Last Post: 01-08-2008, 01:35 PM
By leonxxvii in forum Excel Programming / VBA / Macros
Last Post: 08-16-2007, 07:36 PM
By vikas.bhandari in forum Excel Programming / VBA / Macros
Last Post: 07-16-2007, 05:47 AM
By NewTubaBoy in forum Excel General
Last Post: 12-27-2006, 11:12 AM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1