Help needed with simple MS Access query through Excel

[minette]

Hi All

I have the following code, which opens a query in an Access database and imports the data into excel. However, I want to add a parameter in the query, e.g. "where date between 1/01/2006 and 30/09/2006", but I'm not sure how or where to add it to the code. It might be useful if it can pop up a box so that I can type it in there, as opposed to having it hard-coded into the query.

Also, when I open the query in Access, it always prompts me for my username and password. Is there any way of adding this to the code so that I won't have to type this in each time?

Any help would be greatly appreciated.

Many thanks
Minette


Sub test()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ws As Worksheet, i As Integer
Set ws = ThisWorkbook.Sheets.Add
Set db = OpenDatabase("C:\Noida WB\WORKbasket.mdb", False, True)
Set rs = db.OpenRecordset("Noida_with _pol_nos", dbOpenSnapshot)
With ws
For i = 1 To rs.Fields.Count
.Cells(1, i) = rs.Fields(i - 1).Name
Next i
.Range("a2").CopyFromRecordset rs
End With
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[Andrew Poulsom]

Here is an example using SELECT with OpenRecordset:

http://www.erlandsendata.no/english/index.php?d=envbadacimportdao

and an example of supplying a username and password:

http://msdn.microsoft.com/archive/en-us/dnardao/html/daotoado.asp?frame=true