How can I pass Parameters into Access Queries

[Viddy]

I have a Query which, when run from Access displays a dialog box requesting that a value is entered. The query works fine and returns the required information.

From within VBA (Excel) when I call this query using ADO command and parameter objects, it does not seem to have any parameters passed to it.

Is it possible to do this in Access - call a parameterised query, and if so how?

Regards

Viddy

 

[nberryman]

I don't have the answer but you could try posting this question the Microsoft Access Modules (VBA Coding) forum.

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[substitute]

I find running parameter queries from VBA very awkward. A parameter query is an MS kludge to save people writing VBA in the first place.

Mostly I would use a form or inputbox to prompt for the parameter, insert the result into the appropriate place in the SQL string for the query then use an appropriate VBA statement to execute the SQL. The exact code depends on what you want to do.

If you are just going to display a datasheet, you can convert the query to a datasheet form. A form CAN be passed a parameter which can be used in the "on open" event procedure to filter the query or even modify the SQL of the query.

As you are working from Excel I suspect the execute SQL approach is most likely.

 

[Viddy]

Thanks for the response, the problem I have is that the Query was developed and is in use in several other reports/db tasks. If I hard code the SQL into the spreadsheet then if, or more likely when, someone changes the Query the two will get out of sync!

All I want to do is pass in a week beginning date to run the same query that is used for the generation of a timesheet, to do some excel analysis tasks.

Thanks,

Regards

Viddy

 

[vedicman]

VIDDY

The way that works for me is to create public variables in EXCEL to hold the values that would normally be used as parameters when running the query from Access. I obtain the values for those variables by having the user enter the values into specified cells (EG: C7 )on the spreadsheet.

In the example below, they would enter: Braves

Assign Braves to the myTeam variable as below, instead of assigning it directly in the sub as used in my sample

myTeam = Range("C7&quot.Value

Additionally, I remove the parameters from the Access query.

Here is a code sample for the ADO portion of the process:

Sub RunParamQueryWithADO()

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim myQueryName As String
Dim myTeam As String

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "data source = W:\WAREHOUS\CalendarItems.mdb"
cn.Open

Set rst = New Recordset
myQueryName = "[qryGetPlayers]"
myTeam = "Braves"

Dim strSQL As String

strSQL = "SELECT * FROM " & myQueryName & "" & _
" WHERE Team = '" & myTeam & "'"

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cn
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open (strSQL)
Debug.Print rst.GetString 'used for testing only
End With

'do something with recordset then close rst & cn

End Sub

I hope this is helpful

Franco

 

[substitute]

I see your difficulty. I've looked around for you and can't see any clean solution to feed the parameters in your query. Vedicman has one approach which is similar to one in a FAQ on this forum.

Another approach is to change the query so it gets it's paramters from a linked one row table - just create a table with field names the same as the parameter names and add it to the query grid. For interactive use you can make a form for this table with a button to launch the query. For non-interactive (Excel) use you can update the table from VBA using a recordset object. I don't do ADO but in DAO it is something like:

set oDB = opendatabase.....
set oRST = openrecordset("yourtable&quot
oRST.movefirst()
orst!parameter1 = yourvalue
oRST.update
set oRST = nothing
set oDB = nothing

Good luck