+ Reply to Thread
Results 1 to 1 of 1

Read/Write Access DB from VBA in Excel

  1. #1
    Registered User
    Join Date
    10-02-2008
    Location
    Michigan
    Posts
    68

    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?

    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
    Last edited by excelbobabc; 10-14-2008 at 07:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 10-07-2008, 10:45 PM
  2. Linking an Access Database with Excel
    By chrismann85 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-08-2008, 01:35 PM
  3. Useing Access VBA code in Excel !
    By leonxxvii in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2007, 07:36 PM
  4. writing excel VBA Query for fetching data from Access
    By vikas.bhandari in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2007, 05:47 AM
  5. Visual Basic Between Excel and Access
    By NewTubaBoy in forum Excel General
    Replies: 9
    Last Post: 12-27-2006, 11:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1