Sub GetTable()
'This sub will retrieve all the data in the "Customers" table in
'Northwind
'Declare variables
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path as String
'This line will define the Object "Ws" as Sheets("Sheet1")
'The purpose of this is to save typing Sheets("Sheet1")
'over and over again
Set Ws = Sheets("Sheet1")
'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code
Path = "c:\msoffice\access\samples\northwind.mdb"
'This set of code will activate Sheet1 and clear any existing data
'After clearing the data it will select cell A1
Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
'This will set the RecordSet to all records in the Customers table
Set Rs = Db.OpenRecordset("Customers")
'You could instead set the RecordSet to, for example, the records
'where the Country Code is "UK", without quotes. To do this, replace
'the line above: Set Rs = Db.OpenRecordset("Customers") with the
'following:
'
'Set Rs = _
'Db.OpenRecordset("SELECT * FROM Customers WHERE Country = 'UK';")
'This loop will collect the field names and place them in the first
'row starting at "A1"
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next I
'The next line simply formats the headers to bold font
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold=True
'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet1).
Ws.Range("A2").CopyFromRecordset Rs
'This next code set will just select the data region and
'auto-fit the columns
Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
Rs.Close
Db.Close
End Sub