Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 1 of 1

Thread: Retrieve data from Access-database to Excel with ADO

  1. #1
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451
    A quite frequent questions on several forums is how to connect to a so called Access-database (per se MS Jet Database) and retrieve data to Excel.

    The tips also shows how we can use several Recordset with one connection.

    One of the critical process when working with database is to reduce the time we are connected to the database, i e working with the Recordset. If we experience a slow datatransfer we then may consider to disconnect the Recordset(s).

    In the tip You will see how easy it is to create a disconnected Recordset.

    What we need:
    * Microsoft Windows 2000 or above
    * Microsoft Excel 2000 or above
    * MDAC 2.5 or above
    * A Jet database (mdb)

    Step 1
    Add a reference to the Microsoft ActiveX Data Object x.x library.

    Step 2
    Insert following procedure in a standardmodule:

    Code:
    Option Explicit
    Sub Import_AccessData()
    Dim cnt As ADODB.Connection
    Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
    Dim stDB As String, stSQL1 As String, stSQL2 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 rst2 = 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 Production_E1"
    
    'The 2nd raw SQL-statement to be executed.
    stSQL2 = "SELECT * FROM Production_E2"
    
    '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 rst2
    .Open stSQL2, cnt 'Create the recordset.
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With
    
    With wsSheet1
    .Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
    .Cells(2, 2).CopyFromRecordset rst2 'Copy the 2nd recordset.
    End With
    
    'Release objects from the memory.
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing
    cnt.Close
    Set cnt = Nothing
    End Sub
    For those of You who prefer to use a tool instead You may check out:
    SQL Tester

    Kind regards,
    Dennis
    Last edited by XL-Dennis; May 27th, 2004 at 08:18.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. XL Formulas to Retrieve Data From Access
    By exquoria in forum Excel and/or Access Help
    Replies: 10
    Last Post: October 29th, 2005, 05:05
  2. retrieve MS access data into Excel
    By rmLaurel in forum EXCEL HELP
    Replies: 2
    Last Post: June 22nd, 2004, 22:37
  3. Replies: 8
    Last Post: April 3rd, 2004, 10:13

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
  •  
porno