作者：Mark A. Taff ([email protected])
发布日期：2002 年 2 月 12 日
描述：使用 Microsoft Access VBA 与 PostgreSQL 交互的示例迷你指南
以下是我编写的一些 VBA 代码，因为很难找到与 PostgreSQL ODBC 驱动程序相关的答案。特别是，如何在 MS Access 数据库中以编程方式链接和取消链接 PostgreSQL 关系。这已在 win2k 上的 Access 2000 与 Red Hat 7.2 上的 PostgreSQL 7.1.3 上进行了测试。
这里棘手的地方是指定连接参数的特殊方式，以便 Access 在 Access 表定义对象（而不是 ADODB 连接对象）的上下文中接受它们。代码中有大量注释来解释它，并且包含两个子例程，一个用于链接新关系，另一个用于取消链接关系。
我将其公开发布，不提供任何保证，但我希望它能使其他人的生活更轻松。
Private Sub Link_ODBCTbl(serverConn As String, rstrTblSrc As String, _
rstrTblDest As String, db As Database)
LogEvent "Entering " & APP_NAME & ": Form_Login.Link_ODBCTbbl(" & _
rstrTblSrc & ")", etDebug
On Error GoTo Err_Handler
StartWork "Adding relation: " & rstrTblSrc
Dim tdf As TableDef
Dim connOptions As String
Dim myConn As String
Dim myLen As Integer
Dim bNoErr As Boolean
bNoErr = True
Set tdf = db.CreateTableDef(rstrTblDest)
' don't need next line, as only called if doesn't exist locally
'db.TableDefs.Delete rstrTblDest
' this is 1st error, as doesn't exist locally yet; maybe wrong key
' The length of the connection string allowed is limited such that you can't
' specify all of the PostgreSQL ODBC driver options as you normally would.
' For those that want to do it normally, you are limited to somewhere between
' 269 characters (works) and 274 (doesn't work). Using a dsn is not a workaround.
'
' ***WORKAROUND*** Tested Access 2000 on Win2k, PostgreSQL 7.1.3 on Red Hat 7.2
'
' The connection string begins as usual, for example:
'
' "ODBC;DRIVER={PostgreSQL};DATABASE=database_name_to_connect_to;" & _
' "SERVER=ip_address_to_connect_to;PORT=5432;Uid=username_to_connect_as;" & _
' "Pwd=password_of_user;" & _
'
' For all other parameters, you must code them in the same way Access stores them
' in the hidden MSysObjects table. Here is a cross-reference table:
'
' PG_ODBC_PARAMETER ACCESS_PARAMETER
' *********************************************
' READONLY A0
' PROTOCOL A1
' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1
' SHOWOIDCOLUMN A3
' ROWVERSIONING A4
' SHOWSYSTEMTABLES A5
' CONNSETTINGS A6
' FETCH A7
' SOCKET A8
' UNKNOWNSIZES A9 ' range [0-2]
' MAXVARCHARSIZE B0
' MAXLONGVARCHARSIZE B1
' DEBUG B2
' COMMLOG B3
' OPTIMIZER B4 ' note that 1 = _cancel_ generic optimizer...
' KSQO B5
' USEDECLAREFETCH B6
' TEXTASLONGVARCHAR B7
' UNKNOWNSASLONGVARCHAR B8
' BOOLSASCHAR B9
' PARSE C0
' CANCELASFREESTMT C1
' EXTRASYSTABLEPREFIXES C2
'
' So the parameter part of the connection string might look like: '
' "A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
' "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_"
'
' Concatenating those four strings together will give you a working connection
' string (but you may want to change the options specified).
'
' NOTES:
' `Disallow Premature` in driver dialog is not stored by Access.
' string must begin with `ODBC;` or you will get error
' `3170 Could not find installable ISAM`.
'Debug.Print svr.Conn
myConn = "ODBC;DRIVER={PostgreSQL};" & serverConn & _
"A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;" & _
"C0=0;C1=0;C2=dd_"
tdf.Connect = myConn
tdf.SourceTableName = rstrTblSrc
db.TableDefs.Append tdf
db.TableDefs.Refresh
' If we made it this far without errors, table was linked...
If bNoErr Then
LogEvent "Form_Login.Link_ODBCTbl: Linked new relation: " & _
rstrTblSrc, etDebug
End If
'Debug.Print "Linked new relation: " & rstrTblSrc ' Link new relation
Set tdf = Nothing
Exit Sub
Err_Handler:
bNoErr = False
Debug.Print Err.Number & " : " & Err.Description
If Err.Number <> 0 Then LogError Err.Number, Err.Description, APP_NAME & _
": Form_Login.Link_ODBCTbl"
Resume Next
End Sub
Private Sub UnLink_ODBCTbl(rstrTblName As String, db As Database)
LogEvent "Entering " & APP_NAME & ": Form_Login.UnLink_ODBCTbbl", etDebug
On Error GoTo Err_Handler
StartWork "Removing revoked relation: " & rstrTblName
' Delete the revoked relation...that'll teach 'em not to get on my bad side
' I only call this sub after verifying the relation exists locally, so I
' don't check if it exists here prior to trying to delete it, however if you
' aren't careful...
db.TableDefs.Delete rstrTblName
db.TableDefs.Refresh
Debug.Print "Removed revoked relation: " & rstrTblName
Exit Sub
Err_Handler:
Debug.Print Err.Number & " : " & Err.Description
If Err.Number <> 0 Then LogError Err.Number, Err.Description, APP_NAME & _
": Form_Login.UnLink_ODBCTbl"
Resume Next
End Sub