How to use VBA to access a table in Access

[wut]

There's a table in an Access file that I need to import to Excel. How do I accomplish that?

 

[mvptomlinson]

http://www.google.com/search?q=excel+import+data+from+access+table

Excel 2007: Data tab > Get External Data section > From Access button. Follow the prompts to select a table, query, etc.

 

[wut]

Not using '07...

Need to do it using VBA...

 

[Ruddles]

Show us your table and tell us which fields you need.

 

[wut]

I don't have them. I would actually prefer not to have the entire thing written for me. I would rather write it, but I don't know how to begin. How to start up Access, how to open the file, how to access the particular table, how to copy the information over.

 

[mvptomlinson]

1. Open Excel
2. Turn on Macro Recorder
3. Use Data > Import External Data > New Database Query
4. Follow the prompts to select a database/table you want to import
5. When finished and the data is appearing in your spreadsheet, stop the macro recorder.
6. Open the VB Editor (ALT+F11)
7. Double-click on the Module that was just created (likely Module1) and view the code.

That is essentially what you need. There are certain parts that are specific to the database you're opening (the path, the version of Access, etc.) and some which are there by default.

 

[Ruddles]

I don't want to spoil the excitement of discovery for you but there's a certain minimum amount of information you're going to need. This sort of thing will do the job and you can build on it. Create a new worksheet and paste this into a new standard code module, changing the bits in red to suit:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Public Sub CopyFromRecordset()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Const SheetName As String = "[COLOR=red][B]Sheet1[/B][/COLOR]"
  Dim dbData As DAO.Database
  Dim rsData As DAO.Recordset
  Dim iField As Integer
  Dim iPtr As Integer
  
  Set dbData = DAO.OpenDatabase("[COLOR=red][B]Database.mdb[/B][/COLOR]")
  Set rsData = dbData.OpenRecordset("[B][COLOR=red]tblWhatever[/COLOR][/B]")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  For iField = 0 To rsData.Fields.Count - 1
    Sheets(SheetName).Cells(1, iField + 1) = rsData.Fields(iField).Name
  Next iField
  
  Sheets(SheetName).Range("A2").CopyFromRecordset rsData
    
  rsData.Close[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]End Sub[/SIZE][/FONT]

You'll need to add a reference to the Microsoft DAO Objects Library in VBA (Tools > References).

In place of a simple table name, you could use a SQL query string such as:-

[FONT=Courier New][SIZE=1]Set rsData = dbData.OpenRecordset("SELECT * FROM " & TableName & " WHERE Surname='Smith';")[/SIZE][/FONT]

At the point where I do a CopyFromRecordset, you could instead choose to loop through the recordset like this:-

[FONT=Courier New][SIZE=1]  Debug.Print rsData.RecordCount & " records"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]  rPtr = 1
  Do Until rsData.EOF
    rPtr = rPtr + 1
    Debug.Print rsData!Surname   [COLOR=green]' use ! to refer to the field by its name
[/COLOR]    Debug.Print rsData.Fields(0) [COLOR=green]' use . to refer to the field by its position
[/COLOR]    rsData.MoveNext
  Loop
  rsData.Close
[/SIZE][/FONT]

The rest I leave up to you (and Google).

 

[wut]

Ruddles, your post is exactly the skeletal template I was looking for. Thanks.

I never would have tried the macro-recorder. It's introduced me to three or four different properties I probably wouldn't have thought to attempt on my own.

By combining these approaches, I'll be able to integrate some really important lines (eg, "FieldNames = False" and "AdjustColumnWidth = False"), and also condense the code as significantly as possible.

A++

 

[Ruddles]

Then there's DDE:-

Option Explicit
 
Sub GetSQLQueryResults()
    
  Dim iChannel1 As Integer
  Dim iChannel2 As Integer
    
  Dim sSQL As String
  Dim vData As Variant
  Dim rPtr As Long
  Dim cPtr As Long
 
  Sheets("Sheet1").Cells.ClearContents
  
  Shell "MSAccess " & "c:\folder\database1.mdb", vbMinimizedNoFocus
    
  iChannel1 = DDEInitiate("MSAccess", "System")
  DDEExecute iChannel1, "[OpenDatabase database1]"
  
  sSQL = "SELECT * FROM tblProducts " _
       & "WHERE Colour='Silver' ORDER BY Price DESC;"
  
  iChannel2 = DDEInitiate("MSAccess", "database1;SQL " & sSQL)
  vData = DDERequest(iChannel2, "All")
  DDETerminate iChannel2
  DDEExecute iChannel1, "[CloseDatabase]"
  DDEExecute iChannel1, "[Quit]"
  DDETerminate iChannel1
  
  If Not IsArray(vData) Then
    MsgBox "Failed: Access returned " & CStr(vData) & "!" & Space(15), vbOKOnly + vbExclamation
    DDETerminateAll
    Exit Sub
  End If
    
  For rPtr = LBound(vData, 1) To UBound(vData, 1)
    For cPtr = LBound(vData, 2) To UBound(vData, 2)
      Sheets("Sheet1").Cells(rPtr, cPtr) = vData(rPtr, cPtr)
    Next cPtr
  Next rPtr
  
End Sub

Useful links:-

Using Microsoft Access as a DDE Server
2003: http://msdn.microsoft.com/en-us/library/aa172266(v=office.11).aspx
2007: http://msdn.microsoft.com/en-us/library/bb242443(v=office.12).aspx

Using the DoCmd Object
2003: http://msdn.microsoft.com/en-us/library/aa223114(v=office.11).aspx
2007: http://msdn.microsoft.com/en-us/library/bb214210(v=office.12).aspx