Всем привет.
Поскольку недавно мне пришлось разбираться с этими проблемами, и, как я понял, это частые вопросы, решил поделиться приобретенным опытом, и написать здесь несколько ответов.
Прошу критиковать и корректировать ошибки.
Итак, вы, по привычке, пишете в запросе CAST/CONVERT/CASE, и оно не работает. И не будет. :( В провайдерах (драйверах), которые обрабатывают запросы к файлам Access, Excel такой функции нет. Поэтому работать из Access с SQL Server (когда вы делаете связанную таблицу) и из SQL Server с Access/Excel неудобно. Исключения - запрос из кода VBA и в Excel - подключение к данным SQL Server'а (БЕЗ!! использования Microsoft Query).
1. Конверсия типов. Для конверсии типов в Access используются функции Cint, CLng, CSng, CDbl, CStr, CBool, CDate и другие аналогичные. Есть проблема - они не воспринимают NULL, и возвращают ошибку.
2. Использование алиасов в Access требует обязательного указания ключевого слова AS
3. CASE в Access тоже нет, но есть функция, позволяющая частично заменить его: IIf(условие, что_если_истина, что_если_ложь). При помощи неё также можно обойти ограничение, указанное в пункте 1: iif(поле IS NULL, NULL, CInt(поле)) AS Alias
4. Если вы из SQL Server работаете с файлом Access/Excel через драйвер ADO, то все эти ограничения присутствуют в полной мере. Более того, Excel, например, определяет тип данных в поле по первым 8 строкам, и если значения там пусты, то тип данных устанавливается nchar или ntext. В этом случае ваш запрос может вызывать ошибки при конверсии типов. Особенно, когда у вас файл Excel сохранен в одном locale, а на SQL Server - другой.
Но и здесь есть решение.
SELECT field1, field2
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0; Database='+@ExcelFileName+'; HDR=No'
, 'SELECT iif(F1 IS NULL, NULL, CSng(F1)) AS field1, F2 AS field2 FROM [Sheet1$B4:C]') As t
В этом примере куча решений :)
4.1. идёт выборка, ограниченная в левом углу ячейкой B4, а в правом - последней заполненной ячейкой столбца C.
4.2. игнорируются имена колонок, и колонки автоматически именуются по-порядку: F1, F2
4.3. конверсия типа происходит на стороне драйвера ADO, поэтому используется locale целевого файла (@ExcelFileName).
4.4. во внешний запрос (SQL Server'у) передаются уже колонки с новыми именами, и во внешнем запросе с этими данными можно делать уже что угодно, используя синтаксис SQL Server'а.
5. Если вам нужно передать параметр из ячейки Excel в запрос к SQL Server, тот тут 2 пути: простой и сложный. :)
5.1. Простой: использовать Microsoft Query, в запросе в качестве параметров указать знак ?.
SELECT field1 FROM table AS t WHERE field2 = ? AND field3 = ? AND NOT field4 = ?
.
Выполнить запрос, при запросе параметров указать любые значения, подходящие к типу поля.
Затем, в свойствах запроса станет доступной кнопка "Параметры", нажав на которую, вы сможете установить, из какой ячейки брать значение параметра (и поставить очень нужную галочку "при изменении значения заново выполнять запрос"). Перечень параметров в порядке появления их в запросе.
Минусы этого способа: функционал Microsoft Query остался на уровне 1997, наверное, года. Никаких функций типа CASE там не будет. Можно попробовать iif, я не пробовал.
5.2. Сложный: написать макрос на VBA, который сделает всё это. Этот вариант полнофункциональный, т.к. в нем можно будет использовать все возможности SQL Server, но главный, кмк, для него минус - он потребует сохранения файла в формате xlsm. Если по каким-то причинам у вас это недопустимо (или нежелательно), прийдётся изворачиваться.
Ну, как-то так.
Для поиска:
Как сделать конверсию типов в Access? Cast и Convert в Access
Case в Access, Excel
Подключиться к Access, Excel из SQL Server
параметр SQL parameter из ячейки Excel
http://www.sql.ru/forum/1218305/faq-ispolzovanie-cast-convert-case-v-ms-access-parametry-sql-v-excel-i-dr
