Vba sql queries. Shaping a SQL Query String in VBA

Access saved a query designed with a query builder called "myQuery". The database connects to the system through 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 perfectly 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 the stored query on the database I just connected.

Already tried

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

5 Replies

6

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.

1

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 Exek aside.

You can also add options, this is a bit old but should help:

0

This is kind of a hack, but you can request a request. That is, replace the sql line with the following:

SqlQuery = "SELECT * FROM QueryName;"

Before running this program, you need to make sure that the base Access data was saved ie. press Ctrl+S (it's not enough to run the query in Access).

0

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

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

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

0

It's been a long time since this thread was created. If I understand everything correctly, I can add something useful. I gave a name to what the OP describes, which is the process of using SQL from a query stored in ACCDB to run in VBA via DAO or ADOBD. I gave it the name "object property provider", even with the abbreviation OPP in my notes, and 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.

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

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

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

I use replaceable parameters by evaluating what should be replaced and choosing an unusual name for the paramater that cannot exist in the actual database. For the most part, the only substitutions 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 is 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

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

How to perform VBA macro via view query in MS Access 2013 from JS ActiveX ADO? The VBA function is to get the current user logged in with: Public Declare...

I am 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 throw an error when I run it from a module...

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

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

How to execute query in MS access db from code Excel VBA or macro. The MS-Access request takes some parameters that need to be passed from Excel. Thanks

I have a situation very similar to the following post: Requesting access to excel 2010 to generate a graph via vba In my case, I am exporting a table, but I want to do much more for the file...

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

I have a huge dataset that I need to import from Excel to Access (~800k rows). However, I can ignore rows with a specific column value that add up to like 90%...

is there a way to execute an MDX query within Excel VBA? I thought it could be done via ADO , just like in the case of SQL (yes, I know SQL is different from MDX - a problem that many times...

Access saved a query that was designed with the myQuery Query Builder. The database is connected to the system via an ODBC connection. Macros are all 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 you 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 a query that I have stored in the access database. So how do I call the stored query on the database I just connected.

Tried already

1. con.Execute("EXEC myQuery"), but he told me that he couldn't find myQuery.
2. rs.Open "myQuery" but it's 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 old but should help: update 2 fields in 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.

This is 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's not enough for the query to be executed in Access).

It's been a long time since this thread was created. If I understand correctly, I could add something useful. I've given a name to what the OP describes: it's the process of using SQL from a query stored in ACCDB to run in VBA via DAO or ADOBD. I named 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 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 the ideas useful.

I've even gone so far as to develop a method for using/inserting substituted parameters in SQL that comes from these OPP queries. I then use VBA.Replace() to replace before using SQL in VBA.

The path of the DAO object 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 might not exist in the actual database. For the most part, the only substitutions I've made are field or table names or WHERE and HAVING clause expressions. So I call them 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 is 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

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

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, Open a Stored Procedure and OpenFunction. macro OpenRequest not available in Access web apps.

Setting

macro OpenRequest has the following arguments:

Notes

If for the argument View set value Table, Access displays the result set when a select query, cross query, union query, or server query is used, property ReturnsRecords which matters Yes. If it is a change request, a data definition request, or a request to the server, the property ReturnsRecords which is set to Not, the request is executed.

macro OpenRequest is the same as double-clicking a query in the navigation pane or clicking it right click mouse in the navigation area and view selection. 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. OpenRequest, which opens the query in datasheet view.

If you switch to the constructor when the query is open, the value of the argument Data mode is removed. 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 you run change requests (they say it's a change request and the number of records it affects), you can turn them off with a macro SetWarning.

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

Access allows you to programmatically create SQL query strings, as well as strings that serve as the values ​​of properties of forms, reports, etc., function parameters, etc., which must also comply with the SQL syntax. At the same time, it must be remembered that the constants included in such a line must also be formatted according to SQL rules, namely:

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

Those who program VBA Access while working with the base SQL data servers, very often face such a simple and necessary task as sending an SQL query to the 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 an article about Uploading data to a text file from MSSql 2008 or also touched a little in the material Uploading data from Access to a Word and Excel template. but one way or another there we considered it superficially, and today I propose to talk about it in a little more detail.

Note! All examples below are discussed using an Access 2003 ADP project and an MSSql 2008 database.

Initial data for examples

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

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

As I 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 caption "Run", which we will need to test our code, i.e. we will write all the code in the event handler " Button press».

Database queries INSERT, UPDATE, DELETE in VBA

In order not to drag it out for a long time, let's get started, let's say we need to add a row to our test table ( code is 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 with DoCmd. RunSQL sql_query End Sub

In this case, the query is executed using the current database connection settings. We can check if 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" "Execute it DoCmd.RunSQL sql_query End Sub

If we check, we will see that the desired line has been deleted.

To update the data, we write an update query to the sql_query variable, I hope the meaning is clear.

SELECT query to 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() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for outputting total data in the message Dim str As String "Creating a new object for records set RS = New ADODB .Recordset "Query string sql_query = "SELECT id, name_mon FROM test_table" "Execute query using current project connection settings to display a message str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "go to next record RS.MoveNext Wend "Output message msgbox str End Sub

Here we are already using VBA Access Loops to loop through all the values ​​in our recordset.

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

Private Sub start_Click() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for displaying the final value Dim str As String "Creating a new object for records set RS = New ADODB.Recordset "Query string sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Execute query using 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 name of the cell, but by its index, i.e. 0, which 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 that you output all the code in a separate function (How to write a function in VBA Access 2003) with one input parameter, for example, the month code ( considering our example) and simply, where it is necessary to display this value, call the function we need with the necessary parameter, and that's it, we will significantly reduce the VBA code and improve the perception of our program.

That's all for today. Good luck!