Generating a SQL query string in VBA. How to get data from SQL query in Microsoft Access VBA? Source data for examples

Access saved a query that was developed using the myQuery query builder. The database is connected to the system via an ODBC connection. All macros are included.

Excel Has establishes an ADODB connection to connect to the database via

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "MyDatabase.accdb" End With

Usually you just write your SQL, which works great, and then just do something like

Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...

But I want to access the query that I have stored in the access database. So how do I call a saved query on the database I just connected.

I already tried it

con.Execute("EXEC myQuery"), but that told me that myQuery could not be found.
rs.Open "myQuery" but it is invalid and requires SELECT/etc statements from it

5 responses

I think you can think of this as a stored procedure.

If we start right before Dim sqlQuery As String

Dim cmd as new ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()

Then pick up your work with the recordset after that.

You were almost there

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "z:\docs\MyDatabase.accdb" End With con.Execute "MyQuery"

Just leave Exec.

You can also add parameters, this is a bit outdated but should help: Update 2 fields in an Access database with Excel data and maybe a macro

I was able to run an update query that was already saved in Access using:

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

This was giving me errors until I replaced spaces in the query name with underscores in both the Access database and the execute statement.

It's kind of a hack job, but you can request a request. That is, replace the SQL line with the following:

SqlQuery = "SELECT * FROM QueryName;"

Before starting, you need to make sure that the Access database has been saved, i.e. press Ctrl + S (it is not enough for the query to be executed in Access).

It's been a long time since this topic was created. If I understand correctly, I might be able to add something useful. I've given a name to what the OP is describing: this is the process of using SQL from a query stored in ACCDB to be run in VBA via DAO or ADOBD. I called it "Object Property Provider", even with the acronym OPP in my notes and for the object name prefix/suffix.

The idea is that an existing object in ACCDB (usually a query) exposes a property (usually SQL) that needs to be used in VBA. I threw together a function just to suck the SQL out of the queries for this; See below. Warning: Sorry, but this is all in DAO, I don't use ADODB much. I hope you still find the ideas useful.

I even went so far as to develop a method for using/inserting replaceable parameters in SQL that comes from these OPP queries. Then I use VBA.Replace() to do the replacement before using the SQL in VBA.

The DAO object path to the SQL query in ACCDB is as follows:

MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL

I use replaceable parameters by evaluating what needs to be replaced and choosing an unusual name for the parameter that may not exist in the actual database. For the most part, the only replacements I've made are field or table names or WHERE and HAVING clause expressions. So I call them things like "(ReplaceMe00000001)" and then use the Replace() function to do the job...

SqlText = VBA.Replace(sqlText, "(ReplaceMe00000001)", "SomeActualParameter")

And then use sqlText in VBA. Here's a working example:

Public Function MySqlThing() Dim sqlText as String Dim myParamater as String Dim myExpression as String "Set everything up. sqlText = getSqlTextFromQuery("myQuery") myParameter = "(ReplaceMe00000001)" myExpression = "SomeDateOrSomething12/31/2017" "Do the replacement . sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Then use the SQL. db.Execute sqlText, dbFailOnError End Function Function getSqlTextFromQuery(ByVal oppName As String) As String Dim app As Access.Application Dim db As DAO.Database Dim qdefs As DAO.QueryDefs Dim qdef As DAO.QueryDef Dim sqlText As String Set app = Access.Application Set db = app.CurrentDb Set qdefs = db.QueryDefs Set qdef = qdefs(oppName) oppGetSqlText = qdef.SQL End Function

Using a macro OpenRequest In Access databases, you can open select and cross queries in Datasheet view, Design view, or Preview view. This action triggers a change request. You can also select the data entry mode for the request.

Note: This macro is only available in an Access database environment (MDB or ACCDB). If you are using the Access Project Environment (ADP), see Macros OpenView, OpenSavedProcedure And OpenFunction. Macro command OpenRequest not available in Access web apps.

Settings

Macro command OpenRequest has the following arguments:

Macro Argument	Description
Request name	The name of the request to open. Select a name from the drop-down list. This is a required argument. When you run a macro library in a database that contains a macro OpenRequest, Access first looks for a query with that name in the library database, and then in current base data.
	The view in which the request will be opened. Select in the field View meaning Table, Constructor, Preview, Pivot table or Pivot Chart. Default is Table. Note: The PivotTable and PivotChart views are not available in Access versions starting with Access 2013.
Data mode	Data entry mode for the request. This option only applies to queries opened in Datasheet view. Select Add(users will be able to add new entries, but not change existing ones), Change(users will be able to change existing entries as well as add new ones) or Only for reading(users will only be able to view entries). The default value is Change.

Macro Argument

Description

Request name

The name of the request to open. Select a name from the drop-down list. This is a required argument.

When you run a macro library in a database that contains a macro OpenRequest, Access first looks for a query with that name in the library database, and then in current base data.

The view in which the request will be opened. Select in the field View meaning Table, Constructor, Preview, Pivot table or Pivot Chart. Default is Table.

Note: The PivotTable and PivotChart views are not available in Access versions starting with Access 2013.

Data mode

Data entry mode for the request. This option only applies to queries opened in Datasheet view. Select Add(users will be able to add new entries, but not change existing ones), Change(users will be able to change existing entries as well as add new ones) or Only for reading(users will only be able to view entries). The default value is Change.

Notes

If for argument View given value Table, Access displays the result set if you use a select query, cross query, join query, or server query, property ReturnsRecords which matters Yes. If it is a change request, a data definition request, or a server request, for a property ReturnsRecords whose value is given No, the request is being executed.

Macro command OpenRequest similar double click query in the navigation pane or by right-clicking it in the navigation pane and selecting view. When using a macro, you can select additional options.

Adviсe

You can drag a query from the navigation pane to the Macro Designer window. This will automatically create a macro command OpenRequest, which opens the query in Datasheet view.

If you switch to the Designer while the query is open, the argument value Data mode is deleted. This setting will have no effect even if the user returns to Datasheet view.

If you don't want to display the system messages that typically appear when executing change requests (the ones that tell you that it is a change request and the number of records it affects), you can turn them off using a macro SetWarning.

To run a macro OpenRequest in a Visual Basic for Applications (VBA) module, use the method OpenRequest object DoCmd.

This lesson is dedicated to SQL queries to the database on VBA Access. We will look at how INSERT, UPDATE, DELETE queries are made to the database in VBA, and we will also learn how to get a specific value from a SELECT query.

Those who program in VBA Access and while working with the base SQL data servers are very often faced with such a simple and necessary task as sending an SQL query to a database, be it INSERT, UPDATE or a simple SQL SELECT query. And since we are novice programmers, we should also be able to do this, so today we will do just that.

We have already touched on the topic of obtaining data from a SQL server, where we wrote code in VBA to obtain this data, for example, in the article about Uploading data into a text file from MSSql 2008, or we also touched on it a little in the material Uploading data from Access to a Word and Excel template. but one way or another, we looked at this superficially, and today I propose to talk about this in a little more detail.

Note! All the examples below are considered using the Access 2003 ADP project and the MSSql 2008 database. If you don’t know what an ADP project is, then we looked at this in the material How to create and configure an Access ADP project

Source data for examples

Let's say we have a table test_table, which will contain the numbers and names of the months of the year (queries are executed using management studio)

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

As I already said, we will use an ADP project configured to work with MS SQL 2008, in which I created a test form and added a start button with a signature "Run", which we will need to test our code, i.e. We will write all the code in the event handler " Button press».

Queries to the database INSERT, UPDATE, DELETE in VBA

In order not to delay too long, let's get started right away, let's say we need to add a row to our test table ( code commented)/

Private Sub start_Click() "Declare a variable to store the query string Dim sql_query As String "Write the query we need into it sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Execute it DoCmd. RunSQL sql_query End Sub

In this case, the query is executed using the current database connection parameters. We can check whether the data has been added or not.

As you can see, the data has been inserted.

In order to delete one line we write the following code.

Private Sub start_Click() "Declare a variable to store the query string Dim sql_query As String "Write a delete query into it sql_query = "DELETE test_table WHERE id = 6" "Run it DoCmd.RunSQL sql_query End Sub

If we check, we will see that required line left.

To update the data, write the update request to the sql_query variable, I hope the meaning is clear.

SELECT query to a database in VBA

Here things are a little more interesting than with other SQL constructs.

First, let's say we need to get all the data from the table, and, for example, we will process it and display it in a message, and you, of course, can use it for other purposes, for this we write the following code

Private Sub start_Click() "Declare variables "For a set of records from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for displaying summary data in a message Dim str As String "Create a new object for records set RS = New ADODB .Recordset "Query line sql_query = "SELECT id, name_mon FROM test_table" "Run the query using the current project connection settings RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Loop through the records While Not (RS.EOF) "Fill the variable to display the message str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "go to the next record RS.MoveNext Wend "Output the message msgbox str End Sub

Here we are already using VBA Access loops to iterate through all the values in our recordset.

But quite often it is necessary to obtain not all values from a set of records, but just one, for example, the name of the month by its code. And to do this, it’s kind of expensive to use a loop, so we can simply write a query that will return just one value and access it, for example, we’ll get the name of the month using code 5

Private Sub start_Click() "Declare variables" For a set of records from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String to display the final value Dim str As String "Create a new object for records set RS = New ADODB.Recordset "Query line sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Run the query using the current project connection settings RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Get our value str = RS.Fields(0) msgbox str End Sub

For universality, here we have already addressed not by the cell name, but by its index, i.e. 0, and this is the very first value in Recordset, in the end we got the value "May".

As you can see, everything is quite simple. If you often need to get a specific value from the database ( as in the last example), then I recommend outputting all the code into a separate function (How to write a function in VBA Access 2003) with one input parameter, for example, the month code ( if we consider our example) and simply, where it is necessary to display this value, call the function we need with the required parameter and that’s it, by doing this we will significantly reduce the VBA code and improve the perception of our program.

That's all for today. Good luck!

Access saved a query developed using a query builder called "myQuery". The database is connected to the system via an ODBC connection. Macros are all included.

Excel has an ADODB connection to connect to the database via

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "MyDatabase.accdb" End With

Usually you go ahead and just write your SQL, which is completely fine, and then just do something like

Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...

But I want to access the query which I have stored in the access database. So how can I call a saved query on the database I just connected.

Already tried it

con.Execute("EXEC myQuery") but that one told me that it couldn't be find myQuery.
rs.Open "myQuery", con but this one is invalid and wants SELECT /etc statements from it

vba excel-vba ms-access-2007 adodb excel

5 Replies

I think you can think of it as a stored procedure.

If we start right before Dim sqlQuery As String

Dim cmd as new ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()

Then take your recordset job after that.

You were almost there:

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "z:\docs\MyDatabase.accdb" End With con.Execute "MyQuery"

Just leave Exec aside.

You can also add parameters, this is a bit outdated but should help:

It's kind of a hack, but you can request it. That is, replace the sql line with the following:

SqlQuery = "SELECT * FROM QueryName;"

Before running this program, you must ensure that the Access database has been saved ie. press Ctrl+S (it is not enough for the query to be run in Access).

I was able to run an update query that was already saved in Access using:

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

This was giving me errors until I replaced spaces in the query name with underscores in both the Access database and the execute statement.

It's been a long time since this thread was created. If I understand everything correctly, I can add something useful. I have given a name to what the OP is describing, which is the process of using SQL from a query stored in ACCDB to be run in VBA via DAO or ADOBD. I gave it the name "object property provider", even with the acronym OPP in my notes, and a prefix/suffix for the object name.

The idea is that an existing object in ACCDB (usually a query) exposes a property (usually SQL) that needs to be used in VBA. I put together a function just to suck the SQL out of the queries for this; see below. Warning: Sorry, but this is all in DAO, I don't use ADODB much. I hope you still find these ideas useful.

The DAO object path to the SQL query in ACCDB is as follows:

MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL

I use replaceable parameters by evaluating what needs to be replaced and choosing an unusual name for the parameter that may not exist in the real database. For the most part, the only replacements I've made are to field or table names or WHERE and HAVING clause expressions. So I call them things like "(ReplaceMe00000001)" and then use the Replace() function to do the job...

SqlText = VBA.Replace(sqlText, "(ReplaceMe00000001)", "SomeActualParameter") ...

and then use sqlText in VBA. Here's a working example:

Run a query in Access MakeTable from Excel

I have Excel file which I need to automate. When a user opens an Excel report, they will be prompted to refresh the data. If they say yes then I need to run the query...

Execute VBA function via view query in MS Access 2013 from JS ActiveX ADO

How to execute a VBA macro via view query in MS Access 2013 from JS ActiveX ADO? The VBA function is designed to get the currently logged in user using: Public Declare...

Execute saved query containing "function" in access db from excel

I'm trying to run a query stored in an access database from excel vba. The query works fine if I open and run it in the access database, but throws an error when running it from a module...

MS Access - Execute saved query by name in VBA

How to execute a saved query in MS Access 2007 in VBA? I don't want to copy and paste SQL into VBA. I'd rather just execute the request name. This won't work... VBA can't find the query....

How to perform a request in ms-access in VBA code?

How can I run a query to return records in an ms-access database using VBA code?

Run an access request from excel and pass it paramerts

How to run a query in MS access db from code Excel VBA or macro. The MS-Access request accepts some parameters that need to be passed from Excel. Thank you

Managing an Excel workbook from Access 2010 VBA

I have a situation very similar to the following post: Requesting access to excel 2010 to create a graph via vba In my case, I'm exporting a table, but I want to do a lot more to the file...

Execute SQL Server Pass-Through Query From Access VBA

I have an UPDATE pass through query saved in Access 2007. When I double click on the pass through query it executes successfully. How can I get this query to be executed from VBA? I would...

Importing a huge data set into Access from Excel via VBA

I have a huge data set that I need to import from Excel into Access (~800k rows). However I can ignore the lines with a certain value columns that make up like 90%...

Any MDX query within Excel vba?

is there a way to perform an MDX query within Excel VBA? I thought this could be done via ADO, just like with SQL (yes, I know SQL is different from MDX - an issue that many times...