Hi,
I'm an old fashioned guy and use DAO all the time to open a database and pull back querys.
Although I include apath to the C: drive, at work I use full UNCs such as \\server\folder\database.m
I normally manually type the field names to match the query in the firstrow of a worksheet, and then tell the CopyFromRecordSet method to drop the data in the cell below the first label.
Public Sub DataQuery()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A2")
Set db = DAO.OpenDatabase("C:\Datab
Set qd = db.QueryDefs("MyQueryToRun
Set rs = qd.OpenRecordset
If rs.RecordCount > 0 Then
rng.CopyFromRecordset rs
End If
db.Close
qd.Close
rs.Close
End Sub
Main Topics
Browse All Topics
by: sebastienmPosted on 2004-09-01 at 10:22:30ID: 11955451
Hi,
1. ODBC-DSN with no specific file.
I recorded the creation of a query using the external data feature of excel:
- menu Data > Import (or Get) External Data
- choose 'Excel Files' as data source (not the defined dsn file)
- then choose a file
Generated code:
With ActiveSheet.QueryTables.Ad
& ";DBQ=E:\Book2.xls;Default
& ";PageTimeout=5;", Destination:=Range("A1"))
As you can see, you can use the odbc-dsn to connect to Excel FIles, but you can see here you can just specify the file name dynamically (i do not have Book2 registered anywhere... not in my DSN manager either)
2. How To Use ADO with Excel Data from Visual Basic or VBA
---> http://support.microsoft.c
There, you'll see examples for:
-Jet Provider Using a Connection String
-ODBC Provider Using a DSN-Less Connection String
-ODBC Provider Using a Connection String with a DSN
I hope this helps
Regards,
Sebastien