How can one set parameter values of an INSERT SQL query through VBA in Microsoft Access?

sql ms-access vba ms-access-2010
Romi24 picture Romi24 · Aug 4, 2012 · Viewed 25.7k times · Source

I am new to Access and I am coming from C#, SQL Server & .Net. There is a project that has come my way and I have to complete some parts.

The scenario may be described as:

  1. An Access form with a subform
  2. An Access query that is the data source of the aforementioned subform, with two parameters, displayed as: Parametername1 String(255),Parametername2 String(255).
  3. VBA code module(s)

My plan is to set the values of the above mentioned query parameters within a procedure in my VBA code module. I believe this should refresh my subform as the query is the datasource for the subform.

The problem is I don't know how to implement this plan.

I want to use a query because I don't want to mess up my VBA Code with inline SQL.

I am using Access 2010.

Answer

Ben picture Ben · Feb 18, 2013

I had exactly this question, I wanted to use the same 'stored' update query but execute it from two different forms so wanted to pass the parameter to the query at run-time. This is what I found (in another forum) that does exactly what I want:

With CurrentDb.QueryDefs("qry_YourQuery")
   .Parameters("yourParam") = yourVBAvar
   .Execute
End With

Related questions

How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
MS Access - execute a saved query by name in VBA

How do I execute a saved query in MS Access 2007 in VBA? I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query. This doesn't work ... VBA can't find the …

Read More
Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More