Use ADO to retrieve data from Access Parameter Query

[CodeMonkey]

Hi All
can someone please help with some sample ADO code - I need to run an Access
Parameter Query *from EXCEL* in my remote Access Database, which expects two
parameters and return the data to sheet1.

Thanks for any help.

Regards
Andrew

 

[Guest]

Here is a function that returns a recordset based on a SQL statement.

Private Const m_cDBLocation As String = "D:\ForecastNew.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function


The first for arguments for the function are the parts of the SQL Statement
and the last argument is whether you want to leave the coneection to the
database open. Leave it open if you wnat to update the record in the
database. You can get the SQL statment from the access database by changing
the vie of the query to SQL. You will need to do some playing to replace the
parameters with the variables.

HTH

 

[CodeMonkey]

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
OLEDBatabase 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

 

[Guest]

HI CodeMonkey

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.

Well if it works and you know how to do it what's the question???

I can post you some code but I m not sure what it is your having
problems with?
Do you want to pass the parameter per an Excel cell or
an input box or hard coded as in your example or is that
not even the problem?

Good Luck
TK