Vba sql: Выполнение SQL запросов к текущей базе данных в среде VBA (Access)

Компания Сплайн. VBA — SQL Server

В этой статье мы решили поделиться своим опытом программного взаимодействия с GoogleCalendar: ввод событий, синхронизация, сверка состояний. Наша компания активно использует GoogleCalendar, так как проведение различного рода семинаров для наших клиентов является одним из видов деятельности. Мы ведем порядка 6 календарей по территориям, где проводятся занятия и 12 календарей для лекторов, которые являются дублирующими. Можно отметить стабильный уровень работы GoogleCalendar, поэтому данное мероприятие можно считать целесообразным. Выбор VBA для такой работы является своеобразной гарантией простоты освоения процесса. Данная статья не является подробной инструкцией, так как технический материал (собственно, основной код) недостаточно представлен, но его можно расширить, если он будет востребован. Перед тем, как начать работать в этом направлении, необходимо иметь хотя бы общие представления о том, что такое REST API, запросы к серверу (они же запросы HTTP), код JSON и настроиться, что ничего сложного здесь нет. И так, поехали.
Всё, что вы будете делать с календарем (заполнять его событиями, удалять их, обновлять) делается объектами, которые являются рабочими лошадками, и которыми вы будете управлять запросами HTTP к гугловскому REST API по определенным адресам, используя предоставленные вам многочисленные идентификаторы. В нашем случае достаточно только одного объекта-коллекции — Events с его свойствами и методами, но, тем, не менее, придется сделать всё по полной программе.

1. Необходимо создать свой аккаунт в Google. Скорее всего, он уже у вас есть, поэтому войдите в него.

2. Теперь надо создать проект API. Программно обращаясь к нему, вы будете производить все действия с календарем. Вот ссылка, где вы сможете это сделать. https://console.developers.google.com/flows/enableapi?apiid=calendar
Если войти по ссылке без предватительного входа в свой аккаунт, то все равно придется в него входить позже.

3. Теперь надо создать учетные данные. При создании, укажите, что доступ к API будет выполняться из приложения (не из браузера). После создания учетных данных, лучше скачать строку JSON с ними к себе на РС и занести в таблицу SQL или Access, назовем её таблица идентификаторов:

4. Теперь необходимо создать календарь или календари, объявить их общедоступными, правильно настроить (отображение времени, цвет и прочее). Не нужно пользоваться уже созданным, при создании аккаунта календарем, работа с ним имеет неприятные нюансы, создавайте свои. Идентификатор каждого календаря надо занести в таблицу — таблицу календарей. Не стоит идентификаторы и календари держать в одной таблице. Если идентификаторы вы будете просто считывать в форму, то по календарям, если их несколько, надо будет делать цикл. Если у вас один календарь, то скоро появится ещё и ещё. Пример нашей таблицы календарей:

Если вы всё сделали правильно и у вас имеются 2 таблицы с необходимыми идентификаторами (см. как у нас), то можно приступать к самой интересной части задачи. Важный момент: если вы работаете через прокси-сервер, необходимо будет указывать его параметр во всех HTTP-запросах. Если его параметр обновляется через системный реестр, то занесите в таблицу идентификаторов путь, по которому вы будете его считывать. Можно еще брать из браузера. Проконсультируйтесь со своим сисадмином по этому вопросу, так как он принципиальный. И еще, не пытайтесь скопировать наши личные идентификаторы, они вам не помогут. А вот пути, скорее всего, будут те же.

5. Теперь самый ответственный момент — аутентификация.
5.1. Получение кода первичной аутентификации (код аутентификации). Не путать с токеном, о нём позже. Этот код нужен для последующего получения токенов. Данная процедура делается очень редко, но с неё всё начинается.
Эту процедуру можно сделать как угодно, как вам удобно. Например, вручную запустить Explorer по определенному адресу с нужными идентификаторами, получить код аутентификации, затем получать все остальное.  
У нас эта процедура сделана в составе кода. Сначала создается объект Explorer. Прикрепляются к нему необходимые идентификаторы (см. таблицу) и всё это запускаетсяпо пути:
‘Адрес для аутентификации       
    strUrlAuth = strUrlAuth & «?response_type=code»
    strUrlAuth = strUrlAuth & «&client_id=» & strClientID
    strUrlAuth = strUrlAuth & «&redirect_uri=» & strRedirectUri
    strUrlAuth = strUrlAuth & «&scope=» & strScope

Set IE = New InternetExplorer
    IE. Navigate strUrlAuth
    IE.visible = True
    Do While IE.Busy Or IE.ReadyState <> 4 ‘READYSTATE_COMPLETE
        DoEvents
    Loop

который приведет к своему аккаунту. Далее в программе создаем ожидание, например, 1 минуту, чтобы успеть войти в свой аккаунт. Входим в аккаунт, далее на страницу кода, видим код в поле. Там будет рекомендация скопировать его через буфер обмена, но в нашем случае это не актуально, потому что получаем ответ в виде строки JSON, где он есть. Далее программно  работаем с текстом, который в JSON, используя массив и функции Split, Mid, Left, InStr, вычленяем из текста длинный такой код и присваиваем его строковой переменной. Текст, который в JSON, представляет собой обычный текст, основным достоинством которого является, то, что в конце каждой строки есть символ перехода на другую строку — vblf или Chr(10). Поэтому удобно Split-ом его построчно резать и засовывать в массив.
5.2. По коду аутентификации получаем ответ JSON, который содержит токен, токен-рефреш, время жизни токена, тип токена, прочее,
    Dim objXMLRequest As MSXML2. ServerXMLHTTP60   ‘именно этот класс, другие могут глючить

    With objXMLRequest
        
            strRequest = «code=» & strAuthCode
            strRequest = strRequest & «&client_id=» & strClientID
            strRequest = strRequest & «&client_secret=» & strClientSecret
            strRequest = strRequest & «&redirect_uri=» & strRedirectUri
            strRequest = strRequest & «&grant_type=authorization_code»
              
                   ‘Прокси (есть или нет)                      
             If Len(Nz(WshShell.RegRead(strProxy), «»)) > 0 Then .SetProxy 2, WshShell.RegRead(strProxy)     
            
            .Open «POST», strUrlToken, False
            .SetRequestHeader «Content-Type», «application/x-www-form-urlencoded»
            .Send strRequest

            If .Status <> 200 Then
                MsgBox «Ошибка получения токена!» & vbCrLf & vbCrLf & . Status & «: » & .responseText
            End If
            
            ‘Разбираем строку ответа
            arrResponce = Split(.responseText, vbLf)

далее, так же разбираем ответ из массива вышеуказанными функциями и заносим всё в таблицу токенов.

Токен выдается только на 1 час, дальнейшее его получение осуществляется по токену-рефрешу, который всегда актуален. Итак, аутентификация пройдена, можно заполнять календарь, что делается POST-запросами к серверу. Особое значение имеют идентификаторы событий, которые должны быть уникальными и могут представлять собой: буквы a-z нижний регистр и все цифры, от 5 до 1024 символов. У нас только цифры, длиной от 5 до 7 символов.
Представленные выше и ниже фрагменты кода, естественно с условностями. Необходимо присоединить библиотеки, объявить переменные, объекты, массивы, а по завершению всего подчистить за собой, сделать обработку ошибок, создать функции, причем универсальные, например, проверки токена на свежесть, получения нового токена по токену-рефрешу, функцию для ввода событий (POST-запрос), функцию синхронизации (обновления) событий (PUT-запрос, она же отменяет событие, удаляет и прочее, адресуясь непосредственно к событию, а не только к календарю) и много еще чего. Естественно, что вы будете выбирать данные из своей базы данных, поэтому придется делать запросы в составе хранимой процедуры, открывать там курсоры, организовывать циклы по ним и формировать, таким образом, строку JSON. Но это все уже обычная, понятная работа, а здесь только основные моменты, касающиеся именно календаря.
Опять же, всё это вопрос творчества программиста. Можно делать такие запросы, как угодно. Например, можно сделать по событиям (ID события). Для события формируется на сервере строка JSON, затем идет на клиент, затем в Google. Анализируется ответ, если все в порядке, то следующая итерация. Если сбой, то — в таблицу ошибок и т.д.
У нас сделано по-другому. На сервере формируется сразу массив информации по дата-время начала, дата-время конца события и ID календаря. После завершения процесса, весь массив Select-ом отправляется на клиент, в рекордсет, делается цикл по нему и POST-запросом по событиям вводится в Google. Параллельно с этим все эти события вводятся в таблицу событий. Вводить одни и те же события в календарь нельзя, будет критический сбой, поэтому такая таблица нужна. В ней же будут отражаться все дальнейшие действия с календарем, и она же очень помогает для анализа информации, которая в календарях, у нас в форме по ней сделаны различные фильтры, сортировки и там же функционал управления календарями.
Здесь есть один нюанс. А если какое-либо событие сбойнуло? Ничего страшного, нужно прогнать весь цикл, пусть введется то, что введется. А далее делается сверка состояний таблицы событий и GoogleCalendar. Это очень простая процедура, которая выводит все несоответствия. Сбои будут только на первом этапе. Основная причина сбоев — это какой-нибудь мерзкий символ, который впоследствии выявляется и Replace-ом убивается. Проникает он в базу через копирование с помощью буфера обмена. В принципе, можно чистить текст перед выгрузкой в процедуру, можно это делать перед вводом его в базу, а можно не париться, и так много всяких наворотов. Очень важно: перед отправкой массива данных на клиент (в рекордсет), надо, еще в процедуре SQL все символы конца строки, табуляции и возврата каретки заменить Replace-ом на пустую строку,
     select @CalendarID as CalendarID,
     StudyID,
     REPLACE(REPLACE(REPLACE(JsonSrting,CHAR(9),»),CHAR(10),»),CHAR(13),») as JsonSrting

а на клиенте, например, в Access-е, перед отправкой в Google, заменить Replace-ом двойные кавычки на одинарные:
    . Send Replace(rst!JsonSrting, «»»», «‘»)
Именно в такой последовательности.
Что касается кода по формированию строки JSON, он прост, не смотря на то, что сама хранимая процедура громоздкая и код формируется в нескольких циклах по курсорам. Вот некоторые свойства, которые вы должны передавать для заполнения календаря и, главное, как. Для примера пришлось сократить почти всё, чтобы не перегружать вас.
                    Set @JsonSrting =               
                    ‘{
                    «end»: {«dateTime»:»‘ + @StudyFinish + ‘»}
                    ,»start»: {«dateTime»:»‘ + @StudyStart + ‘»}
                    ,»id»:»‘ + CAST(@StudyID as nvarchar(50)) + ‘»
                    ,»visibility»:»‘ + @Visibility + ‘»
                    ,»transparency»:»transparent»
                    ,»guestsCanSeeOtherGuests»:»False»
                    ,»location»:»‘ + @AddressView + ‘»                    
                    ,»summary»:»‘ + @StudyOfficialType    +  ‘ ‘ + @StudyTheme + ‘ (‘ + @InstructorJobTitle + ‘ ‘ + @Instructor + ‘)»        
                    ,»description»:»‘ + @StudyTheme +
                    ‘»}’  
                    
Так же в форме по управлению календарями желательно сделать 2 невидимых списка (ListBox), к которым в качестве источника присоединить идентификаторы и календари. Удобно потом считывать из них идентификаторы и делать циклы по календарям.
Что касается POST-запроса по заполнению календаря, то вот заключительный момент:
    ‘Если рекордсет не пустой, то делаем по нему цикл и посылаем запросы в Google
    With objXMLRequest
         If rst.RecordCount > 0 Then
                  rst.MoveFirst
                  Do Until rst.EOF                      
                       
                             ‘Прокси (есть или нет)
                        If Len(Nz(WshShell.RegRead(strProxy), «»)) > 0 Then .SetProxy 2, WshShell.RegRead(strProxy)
                        .Open «POST», strURL, False
                        .SetRequestHeader «Content-Type», «application/json»
                        .SetRequestHeader «Authorization», «Bearer » & strToken
                        .SetRequestHeader «Accept-Charset», «UTF-8»
                        
                        .Send Replace(rst!JsonSrting, «»»», «‘»)
                                                
                        ‘204 — статус при удалении, в остальных случаях — 200
                        If . Status <> 200 And .Status <> 204 Then MsgBox «Ошибка ввода события в календарь.” & vbCrLf &
                                                                        vbCrLf & .Status & «: » & .responseText, vbCritical, «Сбой в
                                                                         календаре ID: » & rst!CalendarID & «, событие ID: » & rst!StudyID
                        End If
                
                        rst.MoveNext
                   Loop
          End If
    End With

Ну вот, в общем, и всё. В связи с довольно объемным функционалом, который есть в реальности, а здесь представлены только основные моменты, осталось впечатление некой незаконченности, много чего еще надо бы сказать, но, чтобы сформировать мнение о предстоящей работе и начать, достаточно. В заключение благодарим нашего программиста Алексея Брыкалина за исследовательскую деятельность в этом вопросе.

Желаем всем вам успехов!

Excel VBA.

SQL-запросы в подключениях

UPDATE 21.10.15 Добавил «обратный» макрос — VBA в SQL и макрос для доступа к строке запроса SQL

Некоторое время назад я прошел несколько курсов по SQL. И мне было очень интересно — какую часть из мощного инструмента под названием T-SQL можно применять без использования SQL-Server (не дают мне сервачек под мои нужды, хнык-хнык).

Итак… Начнем с простого — подключение через Query Table в VBA. Можно записать через макрорекордер — для этого нужно создать подключение через Microsoft Query.

Выбираем Excel Files, указываем путь к файлу (пытаясь при этом не ругать разработчиков за интерфейс из 90х годов), фильтруем как-угодно поля. Нам сейчас это не важно — главное получить код, который дальше можно будет корректировать.

Должно получится что-то вроде этого:

[code language=»vb»]
Sub Макрос1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
«ODBC;DSN=Excel Files;DBQ=D:\Dropbox\Excel\тест excel_SQL-2015. xlsx;DefaultDir=D:\Dropbox\Excel;DriverId=1046;MaxBufferSize=2048;Page» _
), Array(«Timeout=5;»)), Destination:=Range(«$A$1»)).QueryTable
.CommandType = 0
.CommandText = Array( _
«SELECT Продажи.F2, Продажи.F3» & Chr(13) & «FROM `D:\Dropbox\Excel\тест excel_SQL-2015.xlsx`.Продажи Продажи» _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = «Таблица_Запрос_из_Excel_Files»
.Refresh BackgroundQuery:=False
End With
End Sub
[/code]

Строчка .CommandText = «SELECT…» — отвечает за SQL запрос. Если хотя бы немного почитать поисковую выдачу google по запросу QueryTable можно упростить код до следующего:

[code language=»vb»]
Sub CopyFromRecordset_To_Range()

DBPath = «C:\InputData. xlsx»
sconnect = «Provider=MSDASQL.1;DSN=Excel Files;DBQ=» & DBPath & «;HDR=Yes’;»
Conn.Open sconnect
sSQLSting = «SELECT * FROM [Sheet1$]»

rs.Open sSQLSting, Conn
Set QT1 = ActiveSheet.QueryTables.Add(rs, Range(«A1»))
QT1.Refresh

rs.Close
Conn.Close

End Sub
[/code]

Теперь начинаем копаться глубже — какого уровня запросы можно строить из VBA. Самые-самые базовые, основные конструкции — все работает, все ок.

Заполнение нового столбца одинаковым значением

[code language=»sql»]
SELECT ‘YTikhonov’, *
FROM [Sheet1$]
[/code]

Переименование столбцов

[code language=»sql»]
SELECT [Advertiser] AS ‘Рекламодатель’, [Quantity] AS ‘Количество’
FROM [Sheet1$]
[/code]

Фильтрация записей

[code language=»sql»]
SELECT *
FROM [Sheet1$]
WHERE [Year] = 2014
[/code]

Сортировка

[code language=»sql»]
SELECT *
FROM [Sheet1$]
ORDER BY [Advertiser] DESC
[/code]

Агрегация записей

[code language=»sql»]
SELECT [Advertiser], Sum([Cost])
FROM [Sheet1$]
GROUP BY [Advertiser]
[/code]

Работа с датой

Дату можно впрямую через конструкцию

[code language=»sql»]
[SomeDateField] = {ts ‘2015-01-01 00:00:00’}
[/code]

Но я люблю отталкиваться от текущей даты. За пару текущая дата-время отвечает функция SYSDATETIME() и она может вернуть в том числе текущий день. Для этого нужна еще одна функция  — CONVERT(type,value)

[code language=»sql»]
SELECT CONVERT(date,SYSDATETIME())
[/code]

С функцией DATEFROMPARTS строка запроса в Excel почему-то не дружит, поэтому придется использовать костыли функцию DATEADD:

[code language=»sql»]
DATEADD(minute, 59, DATEADD(hour, 23, DATEADD(month, MONTH(SYSDATETIME())+1, DATEADD(year, YEAR(SYSDATETIME()) — 1900, 0))))-1
[/code]

Эта строчка в любой день октября 2015 вернет значение — 30.11.15 23:59

А теперь — немного best practice!

Объединение + Агрегация + Join + Подзапросы. И самое интересное — подключение к нескольким источникам:

[code language=»sql»]
SELECT [Year], O.Numbers, SCost, SVolume, SQuantity FROM
(
SELECT [Year], Month, SUM([Cost RUB]) AS SCost, SUM(Volume) AS SVolume, SUM(Quantity) AS SQuantity FROM
(
SELECT Advertiser, 2013 as [Year], Month, [Cost RUB], Quantity, Volume
FROM [N:\GK\Radio\Маркетинг\Служебный\2013. xlsb].[Мониторинг$]
UNION
SELECT Advertiser, 2014 as [Year], Month, [Cost RUB], Quantity, Volume
FROM [N:\GK\Radio\Маркетинг\Служебный\2014.xlsb].[Мониторинг$]
UNION
SELECT Advertiser, 2015 as [Year], Month, [Cost RUB], Quantity, Volume
FROM [N:\GK\Radio\Маркетинг\Служебный\2015.xlsb].[Мониторинг$]
)
WHERE [Advertiser] = ‘METRO GROUP’
GROUP BY [Year], Month
) as T INNER JOIN [C:\test\Month.xlsb].[Test$] AS O
ON T.[Month] = O.[Month]
[/code]

Одна проблема — если осуществлять такого вида запрос для соединения нескольких Excel-файлов, он будет выполняться достаточно медленно. У меня вышло порядка 2 минут. Но не стоит думать что это бесполезно — если подобные запросы выполнять при подключении к SQL-серверу, то время обработки будет 1-2 секунды (само собой, все зависит от сложности запроса, базы, и прочие прочие факторы).

Бонусы

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

[code language=»vb»]
‘работа с буфером обмена http://excelvba.ru/code/clipboard
Private Function ClipboardText() ‘ чтение из буфера обмена
With GetObject(«New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}»)
.GetFromClipboard
ClipboardText = .GetText
End With
End Function

Private Sub SetClipboardText(ByVal txt$) ‘ запись в буфер обмена
With GetObject(«New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}»)
.SetText txt$
.PutInClipboard
End With
End Sub

Public Sub SQL_String_To_VBA()

Dim sInput As String, sOut As String
Dim ArrInput, i As Integer

Dim cIdent As Integer: cIdent = 1 ‘Count of tabs
Dim sVar As String: sVar = «strSQL» ‘Name of variable

sInput = ClipboardText()

ArrInput = Split(sInput, Chr(13))

For i = LBound(ArrInput) To UBound(ArrInput)
sOut = sOut & sVar & » = » & sVar & » & » & Chr(34)
sOut = sOut & String(cIdent, Chr(9))
sOut = sOut & Replace(ArrInput(i), Chr(10), «»)
sOut = sOut & Chr(34) & «& chr(10)» & Chr(10)
Next i

SetClipboardText (sOut)

End Sub

Public Sub VBA_String_To_SQL()

Dim sInput As String, sOut As String
Dim ArrInput, i As Integer, sTemp

sInput = ClipboardText()

ArrInput = Split(sInput, Chr(10))

For i = LBound(ArrInput) To UBound(ArrInput)
sTemp = Replace(ArrInput(i), «& chr(10)», «»)
If Right(sTemp, 1) = » » Then sTemp = Left(sTemp, Len(sTemp) — 1)
If Right(sTemp, 1) = Chr(34) Then sTemp = Left(sTemp, Len(sTemp) — 1)

If Len(sTemp) > 0 Then
sTemp = Right(sTemp, Len(sTemp) — InStr(1, sTemp, Chr(34)))
sOut = sOut & Chr(10) & sTemp
End If
Next i

SetClipboardText (sOut)

End Sub
[/code]

Сами запросы просто и удобно создавать, например, используя Notepad++. Создали многострочный запрос SQL, копируете его в буфер обмена, запускаете макрос и вуаля — в буфере обмена строчки кода, готовые для вставки в ваши макросы. При желании вы можете настроить название переменной и количество табуляций.

И еще один небольшой бонус. Если у вас есть отчет по менеджерам/руководителям, построенный на запросах, то вам наверняка потребуется получать доступ к строке запроса через VBA. Сделать это можно через замечательную команду .CommandText — работает на чтение и запись. Мне для формирования отчета на 25 человек очень пригодился.

[code language=»vb»]
Public Sub ReplaceCommandText()

Dim con As WorkbookConnection
Dim sTemp As String

For Each con In ActiveWorkbook.Connections
sTemp = con.ODBCConnection.CommandText
con.ODBCConnection.CommandText = sTemp
con.Refresh
Next con

End Sub
[/code]

PS Ссылка с ответом на вопрос — как вставить данные из Excel в SQL
https://www.simple-talk.com/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/

 

Приятного использования!

Оператор Excel VBA SQL с содержит

спросил

Изменено
1 год, 1 месяц назад

Просмотрено
570 раз

У меня есть таблица в Excel, которую я хочу использовать для получения некоторых показателей через SQL.

Это первая часть моего кода, которая отлично работает:

 Опция Явный
Подпрограмма MySQL()
    Dim cn как объект, rs как объект, вывод как строка, sql как строка
    Установите cn = CreateObject("ADODB.Connection")
    С Сп
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Источник данных=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Расширенные свойства=""Excel 12.0 Xml;HDR=YES"";"
        .Открытым
    Конец с
 

Теперь я могу получить количество записей с определенным условием, например:

 sql = "ВЫБРАТЬ COUNT(ID) FROM [Data$] WHERE [Type] = ""myType"" и [Status] = ""myStatus"""
Установите rs = cn.Execute(sql)
MsgBox (rs(0))
 

Теперь я хотел бы использовать условие СОДЕРЖИТ , но это не работает:

 sql = "ВЫБЕРИТЕ СЧЕТ(ID) ИЗ [Данные$] ГДЕ СОДЕРЖИТ([Тип], ""T"")"
Установите rs = cn.Execute(sql)
MsgBox (rs(0))
 
  • sql
  • excel
  • vba
  • содержит

4

Содержит — это нестандартная функция SQL, доступная в SQL Server (см. https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view= sql-сервер-ver15). Он используется для выполнения нечетких поисков всех видов.

Содержит , недоступен при запросе других источников данных (Excel, Access, Oracle…).

Если вы просто ищете подстроку, вы можете использовать как -оператор и использовать % в качестве подстановочного знака, например

 sql = "ВЫБЕРИТЕ COUNT(ID) FROM [Data$] WHERE [Type] как '%T%'"
 

'%T%' найдет все, что содержит букву T
'T%' найдет все, что начинается с буквы T
'%T' найдет все, что заканчивается на букву T
(это , конечно, не ограничиваясь одним символом, вы можете использовать '%overflow%' , если хотите).

Обратите внимание, что разные системы баз данных имеют разные правила чувствительности к регистру. Запросы Excel нечувствительны к регистру, поэтому , как %t% , и , как %T% , возвращают один и тот же результат. Это отличается от других баз данных.

Между прочим, вы должны взять за привычку использовать одинарные кавычки для постоянного текста в операторе SQL. Двойные кавычки работают в Excel, но не являются стандартом SQL и не работают в большинстве баз данных.

1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Вложенный запрос SQL Select в Excel с помощью VBA

Я пытаюсь выполнить SQL-запрос на нескольких листах Excel.

У меня такая настройка (каждая таблица представляет собой отдельный рабочий лист):

Три рабочих листа — один с условием, второй с исходными данными и третий с выводом.

В выходных данных мне нужны все записи из источника, где значение в ColumnA присутствует в условии рядом с положительным числом.

Я пытался сделать что-то вроде этого:

 Выберите *
Из [Источник$]
Где [Столбец А] В (
    Выберите [Столбец1]
    От [Условие$]
    Где [Столбец2] > 0
)
 

Возникла первая проблема из-за пустых значений в таблице условий. Он не может обрабатывать сравнение чисел, если в этом столбце есть пустые значения — Несоответствие типа данных в выражении критериев . На данный момент я справился с этим, выполнив эту уродливую вещь:
Где Column2 <> '' And Column2 <> '0'

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

Я все еще получаю сообщение об ошибке Несоответствие типов в выражении критериев. (несколько другая ошибка, чем выше).

Так что я могу нормально работать с внутренним выражением. Я также могу запускать внешнее выражение с жестко закодированными значениями (например, Where ColumnA In "'value1','value2',..." ). Но когда я их вставляю, я получаю сообщение об ошибке.
Насколько я знаю SQL, я думаю, что этот запрос должен работать, но по какой-то причине внутренний запрос в excel возвращает данные, несовместимые с в операторе .

Это мой полный код (благодаря этому ответу SO):

 Option Explicit
Частная константа adCmdText As Long = 1
Частная константа adStateOpen As Long = 1
Общедоступный вспомогательный дисплей ()
Dim dbField как вариант
Dim fieldCounter As Long
Dim dbConnection как объект
Dim dbRecordset как объект
Dim dbCommand как объект
Dim OutputSheet As Excel.Worksheet
Установите dbConnection = CreateObject("ADODB.Connection")
Установить dbRecordset = CreateObject("ADODB.Recordset")
Установите dbCommand = CreateObject("ADODB. Command")
Установите OutputSheet = ThisWorkbook.Worksheets("Вывод")
'Выполните быструю проверку, чтобы определить правильную строку подключения
'если один из них не работает, посмотрите здесь --> https://www.connectionstrings.com/excel/
Если Left$(ThisWorkbook.FullName, 4) = "xlsm" Тогда
dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Источник данных=" & _
ThisWorkbook.FullName & ";Расширенные свойства='Макрос Excel 12.0;HDR=YES';"
Еще
dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Источник данных=" & _
ThisWorkbook.FullName & ";Расширенные свойства='Excel 12.0;HDR=YES';"
Конец, если
'Открыть соединение и запросить
dbConnection.Открыть
С dbCommand
.ActiveConnection = подключение к базе данных
.CommandType = адкмдтекст
.CommandText = "Выберите * из [Source$], где [ColumnA] In (выберите [Column1] из [Condition$], где [Column1] > 0)"
Установите dbRecordset = .