Sql query for the content of the access vba string. How to send a query to a database in VBA Access

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 drag out 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.

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 set of records, but only one, for example, the name of the month by its code. And for this, it is somehow expensive to use a loop, 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 for displaying 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!

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 to powerPoint presentations... 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 eventually I can do it all there (I might need to save all these form diagrams, but I don't know, because I obviously lack 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 the 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 saved 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 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.

Access allows you to programmatically create strings of SQL queries, as well as strings that serve as values \u200b\u200bfor properties of forms, reports, etc., function parameters, etc., which must also match sQL syntax... It should be remembered that constants included in such a string must also be formatted according to the rules of SQL, namely:

Using a macro Open Request in bases access data you can open select and cross queries in Grid View, Design View, or Preview View. 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:

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 Not, 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.

Tips

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 executing change requests (they say that this is a change request and indicate the number of records 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.