Run a query from Access via Excel query in VBA. How to get data from SQL query in Microsoft Access VBA? Sql query for contents of string access vba

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.

Tried already

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 answers

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

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 a SQL server database, very often they are faced with 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 the article about Uploading data to 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

Initial 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 performed 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 the desired line has been deleted.

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!

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 the current database.
	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 the current database.

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 is the same as double-clicking a query in the navigation pane or 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.

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 an Excel file that 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 Excel VBA code 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 rows with a specific column value that are 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...

Hey, I just learned something about how to put my SQL statements into VBA (or at least write them out), but I don't know how to get the data returned?

I have several forms (chart form) based on queries that I run fairly regular parameters against, just changing the time frame (like top 10 sales per month kind of thing). Then I have procedures that automatically pass the chart object into the PowerPoint presentation. So I have all these queries pre-built (like 63), and a form diagram to match (yeah.... 63... I know it's bad), and then all these things are created on "open/close" ” event leading to the next one (it's like my best attempt at being a hack.... or domino; whichever you prefer).

So I've been trying to learn how to use SQL statements in VBA so that eventually I can do it all there (I might need to save all those shape diagrams, but I don't know because I'm obviously missing understanding).

So aside from the question I asked at the top, who can give advice? Thank you

6 answers

This is a bit outdated, so you may want to grab a book on this topic. But, here's a ton of access to resources and some tutorials and examples as well. But basically...

Dim dbs As Database Dim rs As Recordset Dim strSQL As String Set dbs = CurrentDb strSQL = "your query here Set rs = dbs.OpenRecordset(strSQL) If Not (rs.EOF And rs.BOF) Then rs.MoveFirst "get results using rs.Fields() Else "Use results

Behind the comment: take a look at the Post class. It contains a collection called fields, which are the columns that are returned from your query. Without knowing your circuit, it's hard to say, but something like...

Rs.MoveFirst Do While Not rs.EOF "do something like rs("SomeFieldName") rs.MoveNext Loop

Like I said, your best bet is to grab a book on this topic, they have tons of examples.

Use a parameterized QueryDef and call it from VBA.
The query is easier to design...easily testable..and easily accessible using VBA or a form.

Dim qd as querydef set qd = currentdb.querydefs!myquerydef qd.parameters!parm1=val1

or qd.execute

Dim rs as recordset set rs = qd.openrecordset()

Here's a function that you might consider refactoring to take in line and you'll be able to reuse anywhere in your code.

So const or construct a string for your SQL statement, and pop in your sanitize, NON SQL INJECT the string as an argument :)

StrSQL = "SELECT * FROM Customer WHERE ID = " & EnsureParamIsNotSQLInjection(customerID)

Then call the /sub function from wherever you need to get the data/set of records/execute the instruction. By creating multiple data access functions/subs where you can simply run an UPDATE statement or retrieve a single value, or retrieve full-blown records.

The key here is to have these functions all live in one place and use them everywhere. Here is an example in VBScript.

Sub DoStuff(strSQL) Set adoCon = Server.CreateObject("ADODB.Connection") strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db\Database.mdb") "strConnString = "DRIVER=(Microsoft Access Driver (*.mdb)); DBQ=" & Server.MapPath("db\Database.mdb") adoCon.Open strConnString Set rsMain = Server.CreateObject("ADODB.Recordset") rsMain.Open strSQL, adoCon Do While NOT rsMain.EOF customerName = rsMain(" CustomerName") "silly example RsMain.MoveNext Loop rsMain.Close Set adoCon = Nothing End Sub

Another way to do this, which no one seems to have mentioned, is to associate your graph with a single saved QueryDef, and then at runtime, overwrite the QueryDef. Now, I don't recommend changing saved QueryDefs for most contexts as it causes frontal bloat and is usually not even necessary (in most contexts where you use saved QueryDefs can be filtered one way or another in the context in which they used, for example, as one of the RecordSource forms, you just pass one argument to DoCmd.OpenForm).

The graphs are different because SQL driving graphs cannot be changed at runtime.

Some of them suggested parameters, but opening a form with a graph on it that uses an SQL string with parameters is going to pop up default parameters dialogs. One way to avoid this is to use a dialog form to collect criteria, and then set references to the controls in the dialog view as parameters, etc.:

PARAMETERS!! Long;

If you use form links, it is important that you do so because as of Access 2002, the Jet Expression Service does not always handle them correctly when the controls are Null. Defining them as parameters rectifies this problem (which was not present before Access XP).

One situation in which you must rewrite QueryDef for a graph is if you want to allow the user to select N in a TOP N SQL statement. In other words, if you want to be able to select TOP 5 or TOP 10 or TOP 20, you will have to change the saved QueryDef since N cannot be parameterized.