Jim
thanks for the response, but I believe that you misunderstood the question.
I want to run an existing Access *Parameter* Query and pass 2 params to the
query. I have managed to do this using the Parameter object in ADO now. Here
is my code for the benefit of all (most code in the ngs seems to be DAO):
Sub CollectDataByRegion()
Dim com As ADODB.Command
Dim col As Integer
Dim Param1 As ADODB.Parameter
Dim Param2 As ADODB.Parameter
Cells.Clear
Set com = New ADODB.Command
Dim rst As ADODB.recordset
com.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=PathToDatabase\MyDatabase.mdb;Persist Security Info=False;Jet
OLEDB
atabase Password=SomePassword"
com.CommandText = "MyQuery"
Set Param1 = com.CreateParameter(, adDate, adParamInput)
Param1.Value = "10/02/04"
com.Parameters.Append Param1
Set Param2 = com.CreateParameter(, adDate, adParamInput)
Param2.Value = "10/10/04"
com.Parameters.Append Param2
Set rst = com.Execute
'Write the field names
For col = 0 To rst.Fields.Count - 1
Range("A1").Offset(0, col).Value = rst.Fields(col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset rst
rst.Close
Set com = Nothing
End Sub
Regards
Andrew