Хотя мы можем создавать связанные таблицы в Access, которые извлекают информацию из базы данных SQL Server, бывают случаи, когда вы хотите проверить значение или выполнить поиск значения из базовой базы данных SQL Server непосредственно из запроса Access.
Используя VBA, мы можем сделать это!
Мы рассмотрим пример, который позволяет нам искать общий доход клиента от запроса на доступ.
VBA для запроса базы данных SQL Server
Следующую функцию VBA можно использовать из проекта Access VBA. Однако сначала вам нужно будет добавить библиотеку ADODB в проект.
Открытая функция LookupAWCustomerRevenue (intID As Long) в качестве валюты Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnString As String If intID = 0 Тогда LookupAWCustomerRevenue = 0 Else strConnString = "Provider = SQLOLEDB; Источник данных = W10NBJJ; JP0 _ & "Initial Catalog = AdventureWorks2014; Интегрированная безопасность = SSPI;" Установите conn = Новый ADODB.Connection conn.Open strConnString. Установите rs = conn.Execute ("ВЫБЕРИТЕ СУММУ (TotalDue) КАК CustRev FROM Sales.SalesOrderHeader WHERE CustomerID =" & intID) Если не IsNumeric (rs.Fields ("CustRev"). Value) Then LookupAWCustomerRevenue = 0 Else LookupAWCustomerRevenue = rs.Fields ("CustRev"). Значение rs.Close End If End End End Функция
В этом примере мы используем соединение ADO для подключения к экземпляру SQL Server (DBSRV \ SQL2014):
strConnString = "Поставщик = SQLOLEDB; Источник данных = DBSRV \ SQL2014;" _ & "Initial Catalog = AdventureWorks2014; Интегрированная безопасность = SSPI;"
- Параметр Provider указывает, что будет установлено соединение OLDEB, а параметр Data Source указывает на экземпляр SQL Server.
- Параметр Initial Catalog определяет базу данных для запроса (AdventureWorks2014).
- Параметр встроенной безопасности указывает, что проверка подлинности Windows будет использоваться для проверки подлинности с использованием SQL Server.
Объект RecordSet (rs) используется для создания набора записей из оператора SELECT:
Установите rs = conn.Execute ("ВЫБЕРИТЕ СУММУ (TotalDue) КАК CustRev ОТ Sales.SalesOrderHeader WHERE CustomerID =" & intID)
Оператор SELECT состоит из литеральной строки и значения переменной intID, которое передается в функцию при ее вызове.
Оператор If в начале проверяет значение intID, равное 0. Целочисленные переменные по умолчанию равны нулю, если не инициализированы (другими словами, если при вызове функции значение не указано). Если в функцию не передается значение, в качестве значения выручки возвращается значение 0.
Если intID = 0, тогда LookupAWCustomerRevenue = 0
Второй оператор If проверяет не числовое значение, возвращаемое оператором SELECT. Если идентификатор клиента, переданный в функцию, действителен, но он не разместил ни одного заказа, выражение SUM (TotalDue) вернет значение NULL . Если это произойдет, функция будет возвращать значение 0.
Если не IsNumeric (rs.Fields ("CustRev"). Значение), тогда LookupAWCustomerRevenue = 0
Сценарий значения NULL можно увидеть на следующем снимке экрана.
Я поместил точку останова в строку кода VBA, содержащего оператор If, и открыл окно Locals, чтобы я мог видеть все значения переменных в этой точке выполнения.
Я протестировал функцию, выполнив ее из окна Immediate в редакторе VBA:
? LookupAWCustomerRevenue (1)
С установленной точкой останова выполнение кода автоматически останавливается на отмеченной строке и позволяет нам просматривать среду в этой точке выполнения.
Окно Locals в приведенном выше снимке экрана показывает переменную объекта Recordset rs и, в частности, значение для первого поля из rs, «CustRev». Мы можем видеть, что для него установлено значение Null . Это связано с тем, что Клиент со значением CustomerID 1 не размещал никаких заказов и, следовательно, не имеет итогового значения дохода.
Следующий снимок экрана показывает, что запрос возвращает NULL при запуске непосредственно на экземпляре SQL Server:
Предполагая, что в функцию передан действительный CustomerID, а оператор SELECT возвращает ненулевое значение, функция вернет общий доход от продаж для этого клиента в виде значения валюты.
LookupAWCustomerRevenue = rs.Fields ("CustRev"). Значение
Вызов функции VBA из запроса доступа
Вызов функции VBA из запроса доступа прост. При создании запроса на доступ вы можете создавать выражения из встроенной библиотеки функций доступа. Любые функции VBA, которые вы создали в проекте доступа, также доступны для этих выражений. Следующий снимок экрана показывает это:
В четвертом столбце показано выражение для извлечения общего дохода клиента - он вызывает функцию VBA под названием LookupAWCustomerRevenue .
В следующем примере показаны результаты этого запроса:
Резюме
В этой статье мы увидели немного кодирования VBA, некоторые методы обработки ошибок VBA и отладки, и мы увидели, как мы можем вызывать функции VBA из запроса Access. Не стесняйтесь, пишите нам на [email protected] если у вас есть какие-либо вопросы, касающиеся этой статьи. Также есть Excel версия этой статьи ,
Если вы хотите узнать больше о любой из техник, описанных в этой статье, или о программировании на VBA, почему бы не посмотреть на нашу Учебные курсы по Excel и Access VBA ,
Если вы хотите узнать больше о работе с базой данных SQL Server, взгляните на нашу Учебные курсы по SQL Server ,
Вы можете обнаружить, что эти курсы Поздняя доступность предлагать время от времени, предлагая экономию 30%.