Re: Programmatic Access97 - > postgres login

Fred Parkinson wrote:

> Can anybody point me to how to programatically log in to our Postgres
> database? Using Access'97 built-in capabilities I have successfully
> linked to and used the Postgres tables for a while now. Naturally I am
> asked for the username and password when the app starts up, and this has
> never been a problem. Now I want to run an automatic process each night
> and want to do so without a person present to type in the username and
> password.

You should:

1) Turn ODBC tracing on

2) Examine the log for the connection string

3) Execute the connection-string-adjusted version of the following VBA
as appropriate. This function takes a userid, password, and database
name from a Login dialog (form) and constructs the Connect string from
that. If you want to embed the userid/password then you would just use
a constant connection string, but you get the idea:

----

Sub PreConnect(strUserName As String, strPassword As String,
strDatabase As String)

Dim wrkRemote As Workspace, dbsRemote As Database
Dim strConnect As String

strConnect = "ODBC;DSN=PostgreSQL;DATABASE=" + strDatabase +
";SERVER=pgserver;PORT=5432;UID=" + strUserName + ";PWD=" +
strPassword +
";READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=1;CONNSETTINGS="

Set wrkRemote = DBEngine.Workspaces(0)
Set dbsRemote = wrkRemote.OpenDatabase("", False, False, strConnect)
dbsRemote.Close ' Close database but keep connection.

End Sub

----

Note: This is from an older interface whose ODBC parameters might have
changed with newer ODBC versions, so just yank the connection string
from the ODBC trace.

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com