Friday, August 3, 2007

Программное взаимодействие с MS SQL Server из MS Access

Недавно для одной из рабочих задач встала необходимость создания клиентского приложения для работы с базой данных на платформе MS SQL Server 2000. По условиям ТЗ клиент должен был быть выполнен в MS Access.

Ниже я привожу код, который позволяет создать подключение к SQL Server без предварительного создания DSN и создать ссылки на серверные таблицы в Access.

' Функция проверки существования таблицы в Access
'
' Parameters
' tblName - название таблицы
'
' Return Value
' True - если таблица с заданным именем существует, False - в противном случае
Public Function isTableExist(tblName) As Boolean
On Error Resume Next

Dim td As TableDef

isTableExist = False

Set td = currentDB.TableDefs(tblName)
If Err.Number = 0 Then
If td.Connect <> "" Then
isTableExist = True
End If
Set td = Nothing
End If

Err.Clear
End Function

' Функция создания ссылки в Access на таблицу в базе данных MS SQL Server
'
' Parameters
' serverName - имя или IP сервера

' serverDBName - название серверной базы данных
' tblName - название таблицы
' makeHidden - сделать таблицу скрытой (Boolean)
Public Sub linkTable(serverName, serverDBName , tblName, makeHidden)
' Если в Access уже существует таблица с таким именем ее необходимо удалить
If (isTableExist(tblName) = True) Then
DoCmd.DeleteObject acTable, tblName
End If

' Создание ссылки на серверную таблицу в Access
DoCmd.TransferDatabase acLink, "ODBC", _
"ODBC;DRIVER=SQL Server;" & _
"DATABASE=" & serverDBName & ";" & _
"APP=Microsoft Office 2003;" & _
"SERVER=" & serverName & ";" & _
"Trusted_Connection=Yes;" & _
"", acTable, tblName, tblName, False, True

' Если makeHidden = True таблица будет скрытой
Application.SetHiddenAttribute acTable, tblName, makeHidden
End Sub

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

Если необходимо предоставить доступ к серверным таблицам только для чтения и в подключаемых серверных таблицах нет ключевых полей, например, при создании ссылок на серверные представления (View), удобно воспользоваться следующей функцией. При создании ссылок на серверные таблицы предложений о выборе ключевых полей появляться не будет.

' Функция создания ссылки в Access на таблицу в базе данных MS SQL Server
'
' Parameters
' serverName - имя или IP сервера

' serverDBName - название серверной базы данных
' tblName - название таблицы
' makeHidden - сделать таблицу скрытой (Boolean)
Public Sub linkView(serverName, serverDBName , tblName, makeHidden)
' Если в Access уже существует таблица с таким именем ее необходимо удалить
If (isTableExist(tblName) = True) Then
DoCmd.DeleteObject acTable, tblName
End If

' Создание подключения к серверу
Set NewTableDef = currentDB.CreateTableDef(tblName)
NewTableDef.Connect = "ODBC;DRIVER=SQL Server;" & _
"UID=;" & _
"DATABASE=" & serverDBName & ";" & _
"SERVER=" & serverName & ";" & _
"PWD="
NewTableDef.SourceTableName = tblName

' Создание ссылки на серверную таблицу в Access
currentDB.TableDefs.Append NewTableDef

' Если makeHidden = True таблица будет скрытой
Application.SetHiddenAttribute acTable, tblName, makeHidden

Set NewTableDef = Nothing
End Sub

И, наконец, следующая функция иллюстрирует создание подключения к MS SQL Server без необходимости предварительного ручного или программного создания DSN.

Public Sub ExecSomeStoredProcedure(serverName, serverDBName)
Dim cnn As ADODB.Connection

Set cnn= New ADODB.Connection
cnn.Provider = "MSDataShape"
cnn.Properties("Data Provider") = "SQLOLEDB.1"
cnn.Properties("Initial Catalog") = serverDBName
cnn.Properties("Persist Security Info") = False
cnn.Properties("Data Source") = serverName
cnn.Properties("Integrated Security") = "SSPI"
cnn.Open
cnn.CommandTimeout = 90000

cnn.Execute "some_stored_procedure"

cnn.Close
Set cnn = Nothing
End Sub

No comments: