I recently wrote some code to do just that. I am new to VB programmin
so this code is probably a bit unwieldy and not as effecient as i
could be but here it is. To get the SQL statements i just went int
the access database that I wanted to query. Designed the query i
Access (which is relatively straight forward) then switched to SQL vie
to get the statement for the query. I broke it up into parts to mak
it a little easier to read but you could just copy and past it directl
into the VB code and it should work. Anyway, here is the code tha
goes through all the connecting, querying and the filling into excel.
I cut out some parts that weren't relevant so there may be a fe
undeclared variables here and there but I think I got the importan
bits. Hope it helps.

P.S. sorry about the lack of formatting. oh well.



Public Sub QueryWarrantyDatabase()


Dim conn As ADODB.Connection
Dim WarrantyData As ADODB.Recordset

Dim SQLSelect As String
Dim SQLFrom As String
Dim SQLWhere As String
Dim SQLQuery As String

Dim DatabasePath As String


'Set DatabasePath
DatabasePath = "G:\QUALITY\Warranty Database\Warrant
Database.mdb"



'Create the connection to the Warranty Database
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open DatabasePath
End With

'Define the SQL query string for Warranty returns
SQLSelect = "SELECT [Main Data Table].[Project Number], _
[Main Data Table].[Model Number], [Main Dat
Table].[Serial Number], [Main Data Table].[Customer], " _
& "[Main Data Table].[Description Of Failure], [Mai
Data Table].[Root Cause Of Failure], [Main Data Table].[Year O
Manufacture], " _
& "[Main Data Table].[Month Of Manufacture], [Main Dat
Table].[Year Of Return], [Main Data Table].[Month Of Return], [Mai
Data Table].[Product Line], " _
& "[Main Data Table].[Unit Age (months)], [Main Dat
Table].[Warranty/Non Warranty/Warranty Period But Non Warrant
(W,NW,WP)]"
SQLFrom = "FROM [Main Data Table]"
SQLWhere = "WHERE ((([Main Data Table].[Month Of Return])=" & Mont
& ") AND (([Main Data Table].[Product Line]) Like 'custo
brushless'));"

SQLQuery = SQLSelect & " " & SQLFrom & " " & SQLWhere

'Open a new Recordset to hold the Warranty Data
Set WarrantyData = New ADODB.Recordset

'Fill the Recordset with data returned from query
With WarrantyData
.ActiveConnection = conn
.Open SQLQuery, conn, adOpenDynamic, adLockBatchOptimistic
End With




'Update "Warranty Query" sheet with warranty data

WarrantyData.MoveFirst
Do While Not WarrantyData.EOF
With Worksheets("Warranty Query")
.Cells(NewRow, 1) = WarrantyData(0)
.Cells(NewRow, 2) = WarrantyData(1)
.Cells(NewRow, 3) = WarrantyData(2)
.Cells(NewRow, 4) = WarrantyData(3)
.Cells(NewRow, 5) = WarrantyData(4)
.Cells(NewRow, 6) = WarrantyData(5)
.Cells(NewRow, 7) = WarrantyData(6)
.Cells(NewRow, 8) = WarrantyData(7)
.Cells(NewRow, 9) = WarrantyData(8)
.Cells(NewRow, 10) = WarrantyData(9)
.Cells(NewRow, 11) = WarrantyData(10)
.Cells(NewRow, 12) = WarrantyData(11)
.Cells(NewRow, 13) = WarrantyData(12)
End With
NewRow = NewRow + 1
WarrantyData.MoveNext
Loo

--
Message posted from http://www.ExcelForum.com