Using a macro Open Request In Access databases, you can open select and crosstab queries in Grid view, Design View, or Preview mode. This action triggers a change request. You can also select the data entry mode for the query.
Note: This macro is only available in the Access database environment (MDB or ACCDB). If you are using the Access Project Environment (ADP) see macros OpenView, OpenSaved Procedure and Open Function... Macro Open Request not available in Access web apps.
Customization
Macro Open Request has the following arguments:
Macro argument | Description |
|---|---|
|
Request name |
The name of the request to open. Select a name from the dropdown list. This is a required argument. When executing a macro containing a macro in the library database Open Request, Access first searches the library database for a query with this name and then searches the current database. |
|
The view in which the request will open. Select in the box View value Table, Constructor, Preview, Pivot table or Pivot chart... The default is Table. Note: PivotTable and PivotChart views are not available in versions of Access starting in Access 2013. |
|
|
Data mode |
Data entry mode for the request. This parameter only applies to queries opened in table mode. Please select Add to (users will be able to add new entries, but not modify existing ones), Edit (users will be able to modify existing records as well as add new ones) or Only for reading (users will only be able to view the entries). The default is Edit. |
Notes
If for argument View set value Table, Access displays the result set if you are using a select query, cross query, union 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 which is set to No, the request is being executed.
Macro Open Request is the same as double-clicking a query in the Navigation Pane or right-clicking it in the Navigation Pane and selecting a view. When using a macro, you can select additional options.
Advice
You can drag a query from the Navigation Pane to the Macro Designer window. This will automatically create a macro Open Requestwhich opens the query in table view.
If you switch to Constructor when a request is open, the argument value Data mode removed. This parameter will have no effect even if the user returns to table mode.
If you do not want to display the system messages that usually appear when making change requests (they say that this is a change request and indicate the number of records that it affects), you can disable them using the macro SetWarning.
To execute a macro Open Request in a Visual Basic for Applications (VBA) module, use the method Open Request object DoCmd.
Access saved a query that was developed using the myQuery Query Builder. The database is connected to the system via an ODBC connection. Macros are all included.
Excel Has establishes ADODB connection to connect to database via
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con \u003d New ADODB.Connection With con .Provider \u003d "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 \u003d "SELECT * FROM myTable" Set rs \u003d New ADODB.Recordset rs.Open sqlQuery, con, ...
But I want to access the request that I have stored in the access database. So how do I call the saved query on the database I just connected.
Tried already
- con.Execute ("EXEC myQuery"), but he told me he couldn't find myQuery.
- rs.Open "myQuery" but it is invalid and requires SELECT statements from it / etc
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 \u003d adCmdStoredProc cmd.CommandText \u003d "myQuery" cmd.ActiveConnection \u003d con Set rs \u003d cmd.Execute ()
Then take your work with a recordset after that.
You were almost there
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con \u003d New ADODB.Connection With con .Provider \u003d "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 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 the spaces in the query name with an underscore in both the Access database and the execute statement.
It's kind of a hacky job, but you can request a request. That is, replace the SQL line with the following:
SqlQuery \u003d "SELECT * FROM QueryName;"
Before starting, you need to make sure that the Access database has been saved, i.e. press Ctrl + S (not enough for the query to run in Access).
Long since the creation of this topic. If I understand correctly, I might add something useful. I gave a name to what the OP describes: it is 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 prefix / suffix of the object name.
The idea is that an existing object in ACCDB (usually a query) provides 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 really use ADODB. I hope you still find the ideas useful.
I even went as far as to devise a method of using / inserting replaceable parameters in SQL that comes from these OPP queries. Then I use VBA.Replace () to replace before using SQL in VBA.
The DAO path to the SQL query in ACCDB looks like this:
MySqlStatement \u003d 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 like "(ReplaceMe00000001)" and then use the Replace () function to get the job done ...
SqlText \u003d 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 \u003d getSqlTextFromQuery (" myQuery ") myParameter \u003d" (ReplaceMe00000001) "myExpression \u003d" SomeDateOrSomething12 " ... sqlText \u003d 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.App Dlication Dim db As String As DAO.QueryDefs Dim qdef As DAO.QueryDef Dim sqlText As String Set app \u003d Access.Application Set db \u003d app.CurrentDb Set qdefs \u003d db.QueryDefs Set qdef \u003d qdefs (oppName) oppGetSqlText \u003d qdef.SQL End Function
Access saved a query developed 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 \u003d New ADODB.Connection With con .Provider \u003d "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 you just do something like
Dim sqlQuery As String sqlQuery \u003d "SELECT * FROM myTable" Set rs \u003d New ADODB.Recordset rs.Open sqlQuery, con, ...
But I want to access the request that I have stored in the access database. So how can I call the saved query on the database I just connected.
Already tried
- con.Execute ("EXEC myQuery") , but he told me it couldn't be find myQuery.
- 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 \u003d adCmdStoredProc cmd.CommandText \u003d "myQuery" cmd.ActiveConnection \u003d con Set rs \u003d cmd.Execute ()
Then take your recordset work after that.
1
You were almost there:
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con \u003d New ADODB.Connection With con .Provider \u003d "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 little outdated but should help:
0
It's kind of a hack, but you can request a request. That is, replace the sql line with the following:
SqlQuery \u003d "SELECT * FROM QueryName;"
Before running this program, you need to make sure that the Access database has been saved ie. press Ctrl + S (not enough to run the query in Access).
0
I was able to run an update request 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 the spaces in the query name with underscores in both the Access database and the execute statement.
0
It has 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 OPP acronym in my notes, and for the object name prefix / suffix.
The idea is that an existing object in ACCDB (usually a query) provides 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 even went as far as to come up with a method of using / inserting replaceable parameters in SQL that comes from these OPP queries. Then I use VBA.Replace () to do the replacement before using SQL in VBA.
The DAO path to the SQL query in ACCDB looks like this:
MySqlStatement \u003d Access.Application.CurrentDb.QueryDefs ("myQueryName"). SQL
I use replaceable parameters, 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 \u003d 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 \u003d getSqlTextFromQuery (" myQuery ") myParameter \u003d" (ReplaceMe00000001) "myExpression \u003d" SomeDateOrSomething12 " ... sqlText \u003d 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.App Dlication Dim db As String As DAO.QueryDefs Dim qdef As DAO.QueryDef Dim sqlText As String Set app \u003d Access.Application Set db \u003d app.CurrentDb Set qdefs \u003d db.QueryDefs Set qdef \u003d qdefs (oppName) oppGetSqlText \u003d qdef.SQL End Function
Execute query in Access MakeTable from Excel
I have an Excel file that I need to automate. When a user opens an Excel report, he will be prompted to refresh the data. If they say yes, then I need to run a query ...
Execute VBA function via view query in MS Access 2013 from JS ActiveX ADO
How to execute VBA macro through view query in MS Access 2013 from JS ActiveX ADO? The VBA function is for getting the current user logged in with: Public Declare ...
Execute saved query containing "function" in access db from excel
I am trying to run a query stored in an access database from excel vba. The request works fine if I open and run it in the access database, but throw an error when I run it from a module ...
MS Access - execute saved query by name in VBA
How do I execute a stored query in MS Access 2007 in VBA? I don't want to copy and paste SQL in VBA. I rather just follow the name of the request. It won't work ... VBA can't find the query ....
How do I execute a query in ms-access in VBA code?
How can I execute a query to return records in ms-access database using VBA code?
Run access request from excel and pass paramerts to it
How to execute query in MS access db from Excel VBA code or macro. MS-Access query takes some parameters that need to be passed from Excel. thanks
Manage Excel Workbook from Access 2010 VBA
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 a file ...
Execute SQL Server End-to-End 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 succeeds. How can I get this request to execute from VBA? I would...
Importing huge dataset into Access from Excel via VBA
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 amount to as much as 90% ...
Any MDX query within Excel vba?
is there a way to execute an MDX query within Excel VBA? I thought it could be done via ADO, just like it is with SQL (yes, I know SQL is different from MDX - a problem that many times ...
Hey, I just learned something about how to put my SQL statements in VBA (or at least write them out), but I don't know how to get the returned data?
I have several forms (form chart) based on queries that I run fairly regular parameters against, just changing the timeframe (like top 10 monthly sales of some sort of thing). Then I have procedures that automatically pass the chart object into a PowerPoint presentation. So I have all these queries pre-built (for example 63) and a form diagram to fit (well yeah .... 63 ... I know this is bad) and then all these things are set up to open / close "An event leading to the next one (its like my best attempt at being a hack .... or domino; whichever you prefer).
So I was trying to find out how to use SQL statements in VBA, so in the end I can do it all there (I might need to save all these form diagrams, but I don’t know, because I am obviously missing understanding).
So aside from the question that I asked at the top, who can give advice? thanks
6 answers
10
This is a little outdated, so you might want to grab a book on this topic. But, here's a ton of access to resources and a bit of tutorials and examples as well. But, basically ...
Dim dbs As Database Dim rs As Recordset Dim strSQL As String Set dbs \u003d CurrentDb strSQL \u003d "your query here Set rs \u003d dbs.OpenRecordset (strSQL) If Not (rs.EOF And rs.BOF) Then rs.MoveFirst" get results using rs.Fields () Else "Use results
Per comment: Take a look at the post class. It contains a collection called fields, which are the columns that are returned from your query. Not knowing your circuit, it's hard to tell, but something like ...
Rs.MoveFirst Do While Not rs.EOF "do something like rs (" SomeFieldName ") rs.MoveNext Loop
As I said, it is best to grab a book on this topic, they have tons of examples.
Use a parameterized QueryDef and call it from VBA.
Query is easier to design ... easy testable..and easily accessible via VBA or form.
Dim qd as querydef set qd \u003d currentdb.querydefs! Myquerydef qd.parameters! Parm1 \u003d val1
or qd.execute
Dim rs as recordset set rs \u003d qd.openrecordset ()
Here is a function that you might consider refactoring to take in line and you will be able to reuse it anywhere in your code.
So constant or construct a string for your SQL statement, and pop in your sanitize, NON SQL INJECTED string as argument :)
StrSQL \u003d "SELECT * FROM Customer WHERE ID \u003d" & EnsureParamIsNotSQLInjection (customerID)
Then call the function / sub where you need to get the data / recordset / execute the statement. By creating multiple data access functions / sub where you can simply run an UPDATE statement or retrieve a single value, or retrieve full-blown records.
The key point here is to have these features all living in one place and use them everywhere. Here's an example in VBScript.
Sub DoStuff (strSQL) Set adoCon \u003d Server.CreateObject ("ADODB.Connection") strConnString \u003d "Provider \u003d Microsoft.Jet.OLEDB.4.0; Data Source \u003d" & Server.MapPath ("db \\ Database.mdb") "strConnString \u003d "DRIVER \u003d (Microsoft Access Driver (* .mdb)); DBQ \u003d "& Server.MapPath (" db \\ Database.mdb ") adoCon.Open strConnString Set rsMain \u003d Server.CreateObject (" ADODB.Recordset ") rsMain.Open strSQL, adoCon Do While NOT rsMain.EOF customerName \u003d rsMain (" CustomerName ")" silly example RsMain.MoveNext Loop rsMain.Close Set adoCon \u003d Nothing End Sub
Another way to do this, which no one seems to have mentioned, is to link your graph to one saved QueryDef and then rewrite QueryDef at runtime. Now, I do not recommend changing the stored QueryDefs for most contexts, as it causes frontal bloat and usually is not even necessary (in most contexts where you use the saved QueryDefs can be filtered in one way or another, in the context in which they are are used, for example, as one of the RecordSource forms, you just pass one argument to DoCmd.OpenForm).
The graphs are different because the SQL driving graphs cannot be changed at runtime.
Some of them suggested options, but opening a form with a graph on it that uses a SQL string with parameters is going to pop up the default options dialogs. One way to avoid this is to use a dialog form to collect criteria and then set links to controls in the dialog as parameters, etc .:
PARAMETERS !! Long;
If you are using form references, it is important that you do so, because from Access 2002 onwards, the Jet Expression Service does not always correctly handle them when the controls are Null. Defining them as parameters straightens out this problem (which was not present before Access XP).
One of the situations in which you must rewrite the 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.
This lesson focuses on 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 also learn how to get a specific value from a SELECT query.
Those who program on VBA Access 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 must also be able to do this, so today we will do just that.
We have already touched on the topic of receiving data from a SQL server, where we wrote code just in VBA to obtain this data, for example, in the article about Unloading data to a text file from MSSql 2008 or also slightly touched upon in the material Unloading 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 considered using the Access 2003 ADP project and the MSSql 2008 database. If you do not know what an ADP project is in general, then we considered this in the material How to create and configure an Access ADP project
Initial data for examples
Let's say we have a test_table that will contain the numbers and names of the months in the 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 signature "Run"which we need to test our code, i.e. we will write all the code in the event handler " Button press».
VBA INSERT, UPDATE, DELETE queries
In order not to delay for a long time, let's start right away, let's say we need to add a row to our test table ( the 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 sql_query \u003d "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 if the data has been added or not.
As you can see, the data has been inserted.
In order to delete one line, 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 to it sql_query \u003d "DELETE test_table WHERE id \u003d 6" "Execute it DoCmd.RunSQL sql_query End Sub
If we check, we will see that the required line has been deleted.
To update the data, write the update query to the sql_query variable, I hope the meaning is clear.
SELECT query against VBA base
Things are a little more interesting here than with the rest of the 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 the summary data in the message Dim str As String "Create a new object for records set RS \u003d New ADODB .Recordset "Query string sql_query \u003d" SELECT id, name_mon FROM test_table "" Execute 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 in the variable to display the message str \u003d str & RS.Fields ("id") & "-" & RS.Fields ("name_mon") & vbnewline "go to the next record RS.MoveNext Wend" Display the message msgbox str End Sub
Here we are already using VBA Access loops to iterate over all the values \u200b\u200bin our recordset.
But, quite often it is necessary to get not all values \u200b\u200bfrom a record set, 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 refer to it, for example, we will get the name of the month by 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 total value Dim str As String "Create a new object for records set RS \u003d New ADODB.Recordset "Query string sql_query \u003d" SELECT name_mon FROM test_table WHERE id \u003d 5 "" We execute the query using the current project connection settings RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Get our value str \u003d 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, 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 base ( as in the last example), then I recommend that you output 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 output this value, call the function we need with the required parameter and that's it, this will significantly reduce the VBA code and improve the perception of our program.
That's all for today. Good luck!
