Author: Mark A. Taff (mark@libertycreek.net)
Release Date: 12 February 2002
Description: Example based Mini-Howto on using Microsoft Access VBA with PostgreSQL
Here is some VBA code I have written as it can be hard to find
answers related to the PostgreSQL ODBC driver. Specifically, how to programmatically
link and unlink PostgreSQL relations in a MS Access database. This is tested with
Access 2000 on win2k with PostgreSQL 7.1.3 on Red Hat 7.2.
The tricky thing here is the special way to specify the Connection parameters so
Access will accept them in the context of an Access table definition object (as
opposed to an ADODB connection object). The code is heavily commented to explain
it, and consists of two subroutines, one to link a new relation and another to
unlink it.
I am making it available for public knowledge WITHOUT ANY WARRANTY, but I sure
hope it makes someone else's life a bit easier.
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