Solved: Import Multiple Tables

[lifeson]

How do I get Excel to import multiple access tables into excel
I have the following to do 1 table
Sub GetTable()

Dim DBFullName As String
Dim Cnct As String, src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

' Database information
DBFullName = "C:\Program Files\FieldSalesApplication\PremierPlusField.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset

With Recordset
' Filter
src = "SELECT * FROM TblQuote"
.Open Source:=src, ActiveConnection:=Connection
' Write the field names
Sheets("tblQuote").Select
Cells.Clear
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
Set Recordset = Nothing
End With

But without replicating all that for every table I want is there an easier/more logical approach

[T-J]

Just make the GetTable subroutine general in which you pass 2 arguments:

1. the name of the table used in the SQL statement
2. the sheet where the recordset should be written to
Sub GetTable(tbl As String, sht As Worksheet)
'replace relevant lines of code with these below
src = "SELECT * FROM " & tbl
sht.Select
End Sub
Call the subroutine like this:
GetTable "TblQuote", Worksheets("tblQuote")
GetTable "tblSuppliers", Worksheets("sheet2")

[lifeson]

Thanks TJ
Great 1st post :hi: :clap: :thumb

I modified it slightly to be

Sub GetTable(ws As Worksheet, filter As String)

an example to calll the routine is

GetTable Worksheets("TblQuoteLine"), "SELECT * FROM TblQuoteLine " & _
"WHERE (((TblQuoteLine.LeadNumber) = " & leadID & ") " & _
"And ((TblQuoteLine.quoteID) = '" & quoteID & "') " & _
"And ((TblQuoteLine.ResourceType) = '" & resourceID & "')) " & _
"ORDER BY TblQuoteLine.EntityTypeID;"

Thanks again