Parameterized Query in Ms Access 2003 Using Vba

Is it possible to pass parameters programmatically in a Microsoft Access update query?

I just tested this and it works in Access 2010.

Say you have a SELECT query with parameters:

PARAMETERS startID Long, endID Long;
SELECT Members.*
FROM Members
WHERE (((Members.memberID) Between [startID] And [endID]));

You run that query interactively and it prompts you for [startID] and [endID]. That works, so you save that query as [MemberSubset].

Now you create an UPDATE query based on that query:

UPDATE Members SET Members.age = [age]+1
WHERE (((Members.memberID) In (SELECT memberID FROM [MemberSubset])));

You run that query interactively and again you are prompted for [startID] and [endID] and it works well, so you save it as [MemberSubsetUpdate].

You can run [MemberSubsetUpdate] from VBA code by specifying [startID] and [endID] values as parameters to [MemberSubsetUpdate], even though they are actually parameters of [MemberSubset]. Those parameter values "trickle down" to where they are needed, and the query does work without human intervention:

Sub paramTest()
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("MemberSubsetUpdate")
    qdf!startID = 1  ' specify
    qdf!endID = 2    '     parameters
    qdf.Execute
    Set qdf = Nothing
End Sub

MS Access query not receiving parameter from VBA

When you have such problems with Execute, a specially with pivot queries with sub queries. Try to replace WHERE lti.LTI_ID=[Forms]![frm_LTI]![LTI_ID]; with this WHERE lti.LTI_ID=eval("[Forms]![frm_LTI]![LTI_ID]");. But sometimes type cast also needed(for dates as example): WHERE lti.LTI_ID=cDbl(eval("[Forms]![frm_LTI]![LTI_ID]"));

MS Access 2003 - VBA for Parameter box that open after code is run: auto ok?

The parameter box will appear if the query contains a field/param that is not in the table/not specified.

It would seem that the MobileNumber field (if what you state) might be spelled incorrectly, or is not part of the underlyinh table/query.

Verify that you table structure is correct in regards, to the query you supplied, or supply the table structure so that we can have a look.

Access 2003 VBA: query works when run directly, but run-time error 3061 when run from code?

That OpenRecordset() should be a simple basic operation; I can't understand why it's failing when DoCmd.OpenQuery "qryDesignerProjectPrioritySet" works. See what happens with a minimal procedure which does only enough to attempt OpenRecordset().

Insert the following code as a new standard module, and run Debug->Compile from the VB Editor's main menu. Assuming it compiles without error, test the sub with the frm_selectDesigner form open in form view. If it doesn't compile, you likely need to add a reference for DAO or ACEDAO.

Option Compare Database
Option Explicit

Public Sub test_OpenRecordset()
Dim dbs As DAO.Database
Dim rst_projects As DAO.Recordset

Set dbs = CurrentDb
Set rst_projects = dbs.OpenRecordset("qryDesignerProjectPrioritySet", dbOpenDynaset)
rst_projects.Close
Set rst_projects = Nothing
Set dbs = Nothing
End Sub

If it compiles and runs without error, compare that code with your failing code to see if you can spot differences such as the way the object variables are declared and assigned.

If that effort doesn't lead to a solution, or if test_OpenRecordset also throws the same error, all I can think to suggest is HOW TO decompile and recompile.

Passing parameters from one query to a view in MS-Access?

That CREATE VIEW statement creates the named query, paramTest, with this SQL:

SELECT *
FROM T_Employees AS t
WHERE (((t.LastName)=[LastName]));

When running that query, the db engine does not interpret [LastName] to be a pararmeter, so doesn't pop up the parameter dialog, because the name of the parameter is the same as the field name. Essentially, the db engine returns the rows where each LastName field is equal to itself ... all rows.

If you want to create a parameter query which works, give the parameter a name which doesn't match any field names in the data source. And, as Ron mentioned. CREATE VIEW won't allow you to create a parameter query. However you can create one with CREATE PROCEDURE.

CurrentProject.Connection.Execute "CREATE PROCEDURE paramTest " & _
    "(Which_LastName TEXT(255)) AS" & vbCrLf & _
    "SELECT * FROM T_Employees t WHERE t.LastName=[Which_LastName];"

How to enter a parameter in a Where clause?

I'm not all that familiar with Access/DAO, but you're concatenating the parameter value into your query. With ADO your command text would look something like this instead:

Const sql As String = "SELECT * FROM order_tbl WHERE invoice_no Is Null AND company_name = ? AND shiped = True"

The ? is a placeholder for a parameter value; instead of calling OpenRecordset(sql) directly, you need to make a Command and add a proper Parameter.

Seems DAO handles this a bit differently, see Parameterized queries in Access - surely one of the answers is applicable here. Because I don't like random ad-hoc queries hitting my databases I'd probably go with the QueryDefs approach in this answer:

Dim qdf As Querydef
Dim rst As Recordset

'then we'll open up the query:
Set qdf = CurrentDB.QueryDefs(qryname)

'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qryStartDate
qdf.Parameters(1) = qryEndDate

'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset

That would be:

Dim query As QueryDef
Set query = CurrentDB.QueryDefs("TheQuery")

query.Parameters(0) = Me.cmb_start_company.Column(1)

Dim result As Recordset
Set result = query.OpenRecordset

---

Related Topics