Sql vba что это

Sql в программах на vba

VBA, как и макросы, предназначены для автоматизации выполнения повторяющихся операций над объектами БД Access.

В Access существуют следующие способы запуска программ VBA:

— включение программы в процедуру обработки события;

— вызов функции в выражении;

— вызов процедуры Sub в другой процедуре или в окне отладки;

— выполнение макрокоманды ЗапускПрограммы (RunCode) в макросе.

Функции применяются в выражениях, определяющих вычисляемые поля в формах, отчетах или запросах. Выражения используются для указания условий в запросах и фильтрах, а также в макросах, в инструкциях и методах VBA, а также в инструкциях SQL. В процедуру Sub можно включать общедоступные VBA – подпрограммы, вызываемые из других процедур.

Рассмотрим выполнение запроса к базе данных с помощью инструкций SQL в программе на Visual Basic для приложений.

В запросе производится отбор в базе данных записей, удовлетворяющих определенным условиям (запрос на выборку), либо выдается инструкция на выполнение указанных действий с записями, удовлетворяющими определенным условиям (запрос на изменение).

Существуют следующие способы выполнения запросов:

— вызов метода Execute (для выполнения запросов SQL на изменение);

— создание и выполнение специального объекта QueryDef;

— использование инструкции SQL в качестве аргумента метода OpenRecordset;

— выполнение метода OpenRecordset для существующего объекта QueryDef;

— вызов методов RunSQL и OpenQuery.

Метод Execute используется, если требуется выполнить такое изменение в БД, при котором не возвращаются записи. Например, операции вставки или удаления записей.

Объект QueryDef представляет собой сохраненное определение запроса в базе данных. Его можно рассматривать как откомпилированную инструкцию SQL.

Метод OpenRecordset используется, чтобы открыть объект типа Recordset для выполнения последующих операций над ним.

Метод RunSQL выполняет макрокоманду ЗапускЗапроса SQL в программе VBA

Метод OpenQuery выполняет макрокоманду ОткрытьЗапрос (OpenQuery) в программе VBA. С его помощью можно открыть запрос в режиме таблицы, Конструктора или просмотра. При этом устанавливается один из следующих режимов работы с данными: добавление, изменение или только чтение.

Выбор варианта выполнения запроса определяется программистом с учетом особенностей решаемой задачи.

Краткое описание языка sql в ms Access

Операторы (инструкции) языка SQL в СУБД MS Access используются при разработке форм, отчетов, а также написания макрокоманд и программ.

К числу основных операторов языка SQL, реализованного в Access, относятся следующие: ALTER TABLE, CREATE INDEX, CREATE TABLE, DELETE, DROP, INSERT INTO, SELECT, TRANSFORM и UPDATE.

Приведем описание инструкций, которое составлено по сведениям справочной системы MS Access

Назначение: изменение структуры таблицы, созданной с помощью инструкции CREATE TABLE

Удалить поле «Оклад» из таблицы «Сотрудники», которая создана ранее с помощью инструкции CREATE TABLE.

ALTER TABLE Сотрудники DROP COLUMN Оклад;

Назначение: создание нового индекса для существующей таблицы.

Создать в таблице «клиенты» индекс по полю «КодКлиента» в этом поле исключить повторяющиеся и пустые значения.

CREATE UNIQUE INDEX ИндексКлиента ON Клиенты (КодКлиента) WITH DISALLOW NULL;

Назначение: создание новой таблицы.

Создать новую таблицу «Таблица1» с двумя текстовыми полями и одним целочисленным полем. Поле «Страховка» сделать ключевым.

CREATE TABLE Таблица1 (Имя TEXT, Фамилия TEXT, Страховка INTEGER CONSTRAINT Индекс1 PRIMARY KEY);

Назначение: создание запроса на удаление записей из одной или нескольких таблиц, перечисленных в предложении FROM, которые удовлетворяют предложению WHERE.

Удалить записи о всех сотрудниках, которые занимают должность «Стажер» и имеют запись в таблице «Оплата». Между таблицами «Сотрудники» и «Оплата» установлена связь 1:1

DELETE Сотрудники.*FROM Сотрудники INNER JOIN Оплата ON Сотрудники.КодСотрудника=Оплата.КодСотрудника WHERE Сотрудники.Должность=’Стажер’;

Назначение: удаление таблицы из базы данных или индекса из таблицы.

Удалить «Индекс1» из таблицы «Стажеры».

DROP INDEX Индекс1 ON Стажеры;

Назначение: добавить запись или записи в таблицу. Эта инструкция образует запрос на добавление.

Отобрать все записи из таблицы «Стажеры» для стажеров, принятых на работу более 30 дней назад, и добавить их в таблицу «Сотрудники».

INSERT INTO сотрудники SELECT Стажеры.*FROM Стажеры WHERE ДатаНайма < Now () – 30;

Назначение: представить данные из базы данных в виде набора записей.

Отобрать все поля из таблицы «Сотрудники».

SELECT Сотрудники.*FROM Сотрудники;

Подсчитать число записей, которые содержат непустое значение в поле «Индекс», и присвоить заголовок «Итого» полю, в которое возвращается результат.

SELECT Count(Индекс) AS Итого FROM Клиенты;

Вывести число сотрудников и их среднюю и максимальную зарплату.

SELECT Count(*) AS ЧислоСотрудников, Avg (Оклад) AS Средний оклад, Max (Оклад) AS Максимальный оклад FROM Сотрудники;

Назначение: подготовка запроса на создание таблицы. Запрос на создание таблицы можно использовать для архивации записей, создания резервных копий таблицы, копий для экспорта в другую базу данных или основы отчета, отображающего данные за конкретный период времени.

Создать таблицу с именем «Стажеры» и скопировать в нее записи о всех сотрудниках, имеющих должность «Стажер»

SELECT Сотрудники.Имя, Фамилия INTO Стажеры FROM Сотрудники WHERE Должность=’Стажер’;

Создать таблицу, содержащую сведения о всех стажерах и их зарплате. Между таблицами «Сотрудники» и «Оплата» установлена связь 1:1. новая таблица должна содержать все данные из таблицы «Сотрудники», а также данные поля «Оклад» из таблицы «Оплата».

SELECT Сотрудники.*, Оклад INTO Стажеры FROM Сотрудники INNER JOIN Оплата ON Сотрудники.КодСотрудника=Оплата.КодСотрудника WHERE Должность = ‘Стажер’;

Назначение: создание перекрестного запроса (запрос, возвращающий данные в виде электронной таблицы, используя указанные поля как заголовки строк и столбцов, и способный возвращать итоговые данные). Перекрестный запрос позволяет просматривать данные в более компактной форме, чем при работе с запросом на выборку.

Создать перекрестный запрос, показывающий распределение продаж по месяцам указанного пользователем года. Месяцы должны определять заголовки столбцов слева направо, а марка товаров – заголовки строк сверху вниз.

PARAMETERS [Год продажи ?] LONG;

Sum (Заказано.Количество * (Заказано.Цена – (Заказано.Скидка / 100) * Заказано.Цена)) AS Продажи

FROM Заказы INNER JOIN

(Товары INNER JOIN Заказано ON Товары.КодТовара = Заказано.КодТовара) ON Заказы.КодЗаказа = Заказано.КодЗаказа

WHERE DatePart(“yyyy”, ДатаРазмещения) = [ Год продажи?]

PIVOT DatePart(“m”, ДатаРазмещения);

В этом примере перед инструкцией TRANSFORM стоит оператор PARAMETERS, который запрашивает у пользователя значение переменной «Год продажи?». Это позволяет построить запрос с параметром.

10. Инструкция UPDATE

Назначение: создание запроса на обновление записей, который изменяет значение полей указанной таблицы на основе заданного условия отбора.

Увеличить на 10 процентов цену на все товары поставщика, имеющего код 8, поставки которых еще не прекращены.

UPDATE Товары SET Цена = Цена * 1.1 WHERE КодПоставщика = 8

AND ПоставкиПрекращены = No;

Не следует использовать зарезервированные слова PRIMARY KEY при создании индекса в таблице, в которой определен ключ;

Нельзя добавить или удалить одновременно несколько полей или индексов;

Инструкцию CREATE INDEX можно использовать для добавления к таблице простого или составного индекса, а инструкции ALTER TABLE и DROP – для удаления индекса, созданного с помощью ALTER TABLE или CREATE INDEX;

При добавлении индекса указываются все необходимые сведения об индексе, а при его удалении – достаточно указать его имя;

Зарезервированное слово UNIQUE используется для обеспечения уникальности значений в поле;

Зарезервированные слова PRIMARY KEY используются для создания ключа таблицы, состоящего из одного или нескольких полей. Все значения в ключевом поле таблицы должны быть уникальными и не Null. В таблице может быть только один ключ;

Зарезервированные слова FOREIGN KEY используются для создания внешнего ключа.

SQL запрос из Excel VBA

SQL расшифровывается как Structured Query Language (структурированный язык запросов) и является языком, который используется для получения информации из баз данных (таких как Access , SQL Server from Microsoft , Oracle , Sybase , SAP и других). Вы также можете получать данные из интернета, текстовых файлов или других Excel или CSV файлов.

Итак, нам нужно соединение с базой данных (переменная varConn в макросе ниже) и SQL запрос (переменная varSQL ), чтобы автоматизировать получение данных из базы для отчета. В примере ниже есть SQL запрос , который получает данные с малой базы данных в Access.

Нажмите скачать базу данных Access . Для корректного соединения база данных должна быть в папке «Мои документы«. Файл Access будет выглядеть:

Давайте напишем свой макрос, который будет осуществлять SQL запрос .

Меню Сервис — Макрос — Редактор Visual Basic , вставьте новый модуль (меню Insert — Module ) и скопируйте туда текст макроса:

Нажимаем сохранить и возвращаемся к Excel . Выбираем в меню Вид — Макросы (Alt + F8) название нашего макроса » SQLQuery_1 «.

How to run a SQL Query with VBA on Excel Spreadsheets Data

Learn how to easily run a plain SQL query with Visual Basic for Applications on your Excel Spreadsheet.

In the last days, I received an unusual request from a friend that is working on something curious because of an assignment of the University. For this assignment, it’s necessary to find the answer or data as response of a query. Instead of a database, we are going to query plain data from an excel spreadsheet (yeah, just as it sounds). For example, for this article, we are going to use the following Sheet in Excel Plus 2016:

The goal of this task is to write raw SQL Queries against the available data in the spreadsheet to find the answer of the following questions:

• Which users live in Boston.
• Which users are boys and live in Boston.
• Which users were born in 2012.
• Which users were born in 2010 and were ranked in place #1.

Of course, finding such information as a regular user is quite easy and simple using filters and so, however the assignment requires to do the queries using SQL and Visual Basic for the job. In this article, I will explain you from scratch how to use Microsoft Visual Basic for Applications to develop your own macros and run some SQL queries against plain data in your excel spreadsheets.

1. Launch Microsoft Visual Basic For Applications

In order to launch the window of Visual Basic to run some code on your spreadsheets, you will need to enable the Developer tab on the excel Ribbon. You can do this easily opening the Excel options (File > Options) and searching for the Customize Ribbon tab, in this Tab you need to check the Developer checkbox to enable it in your regular interface:

Click on Ok and now you should be able to find the Developer tab on your excel ribbon. In this tab, launch the Visual Basic window:

In this new interface you will be able to run your VB code.

2. Building connection

In the Visual Basic window, open the code window of your sheet and let’s type some code! According to your needs you may create a custom macro and assign them to the action of buttons or other kind of stuff. In this example, we are going to work with plain code and will run them independently to test them. You need to understand how to connect to the workbook data source that will be handled with the following code:

The connection properties are described as follows:

• Provider: we will use the Microsoft Access Database Engine 2010 (Microsoft.ACE.OLEDB.12.0)
• ConnectionString: we will use the current excel file as the database.
• HDR=Yes; : indicates that the first row contains the column names, not data. HDR=No; indicates the opposite.

You will use this connection to run the SQL.

3. Printing whole table data

The following example, will use the mentioned logic to connect to the current spreadsheet and will query the range A1:E6 (selecting the whole table in the example excel) and will print every row in the immediate window:

Note that we are using HDR so the query will use the first row of data as the column headers, so the result will be the following one:

4. Query by columns

Now that you are able to connect to the worksheet, you may now customize the SQL to fit your needs. It is necessary to explain you the most basic thing you need to know about querying some data in your excel file. The range needs to specify the Sheet Name and the regular excel range (e.g. A1:Z1) and the whole data should be selected, not individual columns. You may filter by individual columns using regular SQL statements as WHERE, AND, OR, etc.

Depending if you use HDR (first row contains the column names), the query syntax will change:

HDR=YES

If you have HDR enabled (in the extended properties of the connection), you may query through the column name, considering that you selected the appropriate range:

HDR=NO

If you don’t use HDR, the nomenclature of the columns will follow the F1, F2, F3, . FN pattern:

The following query would work perfectly if you don’t have HDR enabled (note that the range changes):

In both cases, the output will be the same in the immediate window:

5. Answering questions

The SQL that should solve the initial questions will be the following ones (with HDR disabled):

3 Ways to Perform an Excel SQL Query

Howdee! Excel is a great tool for performing data analysis. However, sometimes getting the data we need into Excel can be cumbersome and take a lot of time when going through other systems. You’re also at the mercy of how a disparate system exports data, and may need an additional step between exporting and getting the data into the format you need. If you have access to the database where the data is housed, you can circumvent these steps and create your own custom Excel SQL query.

To follow along with my below demos, you’ll need to have an instance of SQL server installed on your desktop. If you don’t, you can download the trial version, developer version, or free express version here. I’ll be working with the free developer version in this article. I’m also using a sample database that you can download here. The easiest way to install this is using SQL Server Management Studio (SSMS). That download is available here. Once you open SSMS, it should automatically detect your local server instance. You must ensure your SQL Server User is running as the “Local Client” and then you can create a blank database, and restore that database from the backup file. If you have issues accomplishing this, let me know in the comments and I’ll elaborate on how this is done.

If you are familiar enough with SQL and have access to your own data, you can skip these steps and use your data. Otherwise, I recommend downloading these tools before getting started. If you’re new to SQL, I highly recommend the SQL Essential Training courses on Lynda.com. Now, on to why you’re all here…

Excel SQL Query Using Get Data

This option is the most straight forward approach to creating an Excel SQL query. However, it is important to note that this approach is only available in Excel 2013 and later and will not currently work on Mac OSX. To get started, select “Get Data” à “From Database” à “From SQL Server Database” as shown in the screen grab. At this point it will pop-up a prompt to enter your server name and the target database you’re wanting to query (you can get this information from SSMS). You can enter this information and then select “OK”. This will allow you to browse available tables from that database to import. You can remove columns and filter tables before importing. If you do not know how to write SQL queries yet, this is one approach you can take.

However, if you select the “Advanced” dropdown arrow, you can create your own custom Excel SQL query. I usually create my query in SSMS or Visual Studio and then just paste the final query in this window. That is because there is no intellisense in this window and it can be difficult to spot errors in your query. Once you select OK, it will ask you to confirm credentials and you may get an error about encryption. This is common when connecting to databases in this manner and nothing to worry about. The next screen will provide an example of your data and you can select “Load” to import it.

This will create a table on a new tab and you’ll also notice a new pane on the right titled “Connections & Queries”. It will display the name of your query (defaults to “Query1”, “Query2”, etc.) and you can rename the query by right-clicking and selecting “Rename”. You can also edit the query from this location as well. It will open up an interface with a sample of your data and you can add/remove columns, filter your data, or edit your source query from here.

Now that you’ve set up this Excel SQL query, you can simply refresh the data set with fresh data anytime by clicking “Refresh All” on the “Data” ribbon. A quick side note here. If you pivot this data, “Refresh All” will refresh pivot tables first and then the query. To update your pivot table, you’ll need to refresh all twice or update your pivot table manually. To me, one of the downsides of this approach is the results are always returned in a table. I personally do not like working with tables in Excel. That’s where using VBA for your SQL query can come in handy.

Excel SQL Query Using VBA

Using VBA to create your Excel SQL query is not as straight forward as the previous approach, but can still be an extremely useful method depending on your situation. I particularly like that the data is not returned to a table unless you designate it to be so. This technique will work on older versions of Microsoft Excel but will not work on Mac OSX versions of Excel since it uses and ADO connection.

To get started, open up the VBA editor by pressing alt+F11. Before beginning to write your code, you’ll need to ensure that the “Microsoft ActiveX Data Objects 2.0 Library” is referenced from the VBA Project. To do this, click on “Tools” in the ribbon menu at the top of the VBA editor. In the popup, ensure the library is checked as shown below. This allows the project to use the ADO connectors to create the connection to your database. Next, let’s dimension a few variables.

The Conn variable is will be used to represent the connection between our VBA project and the SQL database. The receset variable will represent a new record set through which we will give the command to perform our Excel SQL query using the connection we’ve established. Finally, the sqlQry variable will represent a string variable that is our SQL query command, and the sConnect variable will be a string representing the connection string the database requires. Let’s look at how to use these variables to perform a SQL query.

While this may look complex, each step is relatively simple. Firstly, we set our sqlQry variable equal to a string that represents the syntax of our SQL query. We then create a connection string we can use in our next command to connect to the database. So, “Conn.Open” is the command to open the connection and “sConnect” is the string it uses to do so. “Trusted_Connection=yes” means that the connection will attempt to be established using your Microsoft credentials for the account you’re logged in as.

Now that the connection is open, we can open a new record set and pass it the sql command using the sqlQry variable, and tell it which connection to use by passing it the Conn variable. We can then use the VBA command “CopyFromRecordset” to paste the recordset anywhere in our workbook. It’s important to close both the record set and connection at this point. You also want to set your recset variable equal to nothing so it does not eat up valuable resources.

One of the downsides to using this method is that you must explicitly tell Excel some things that the previous approach did automatically. For example, this SQL query will not return any column headers. Therefore, you must explicitly tell Excel what to label your columns. Secondly, the data is not automatically cleared and the new query imported. You must also explicitly tell Excel to do this as well. Here is the final code with those commands added.

My preference for using this approach is when I want the user to be able to pass parameters to my Excel SQL query. For example, I might have a dropdown of customer names the user could select. By using this tactic, I can easily add a dropdown of customer names the user can select, and pass that value to my SQL query in a where clause.

As you can see, both the built in Excel SQL query and the VBA method have pros and cons. I employ both in my everyday work depending on what situation I find myself in.

Excel SQL Query Using Microsoft Query

This option is likely the most complex option, but it has the added advantage of being compatible with some versions of Mac OSX. I won’t pretend to be an expert at creating Mac OSX compatible tools for Excel, but I have successfully used this implementation to create an embedded Excel SQL query for Macs in the past.

I also like this method because you can create popup style parameters. For example, you can prompt the user to input date range parameters at the time the SQL query is ran. Like the first example, running this query is as easy as clicking “Refresh All” on the Data ribbon. Let’s dive in to the details.

To get started here, click “Get Data” on the Data ribbon. In the menu that dropdowns select “From Other Sources” and, finally “From Microsoft Query”.

This will open a wizard for you to choose your data source. Double click “<New Data Source>” and you’ll be prompted to enter some information about your data source. Option 1 can be anything you wish that describes your data source. Option 2 should be “SQL Server”. Click “Connect” and it will pop up a third window where you can enter information about the server and login information. Be sure you select the “Options>>” dropdown so you can select the database you’re wanting to connect to.

You’ll now have a new data source in the original window. Double click the data source to bring up a table import wizard. If you want to import an entire table, you can do so here and even filter and sort the data using the import wizard. However, if you want to use your own custom query as we have been, just select any field and go through the wizard and import the data. When you come to screen that asks you if you want to return the data to Excel or edit in a query, return the data to Excel. It will then prompt you to select where you want the data returned in your workbook.

The query will return the data in a table format. To change it to your own custom SQL query, let’s follow these steps:

• Click anywhere in the data table.
• On the Excel Data Ribbon, in the “Queries & Connections” group, properties will no longer be grayed out like it normally is. Click this.
• In the popup – you’ll see another properties icon. Click this.
• In this popup, select the “Definition” tab and paste your SQL query in the “Command Text” input box.

Now you’ve built an Excel SQL Query that can be refreshed anytime the workbook is refreshed. In my screengrab, the “Parameters” button is greyed out. If you want to add parameters to your query, you do so by adding “?” in your command text. That looks like this.

This creates a parameter the end user can interact with. You can have the user be prompted to enter an input when the workbook is refreshed, select a default value, or have it linked to a cell in the workbook. Even though this option is cumbersome to set up, I really enjoy using it. It allows me a lot of flexibility to have the user interact with the data. As I touched on in the beginning, I’ve had success using this option on Microsoft Office for Mac OSX. I don’t want to say this will work 100% of the time on a Mac because I’ve also had it fail. If anyone has any input on this, I’d love to hear from you.

Let me know your thoughts on these approaches in the comments! What other ways do you creatively get data into Excel from SQL data sources?