RE: Access 2000 ODBC table links

You could relink the tables on the fly with vb code.
Then you can specify any user you want.
You could make your own user login screen etc.

The following works for me in Access 97
You can make new recordsets using ODBCDirect and when you create the
workspace you can use any user name you like.

'**********
Function LinkODBCTable(strTable As String, strSourceTable As String)

If g_Debug Then: On Error GoTo 0: Else: On Error GoTo EH

'Make new linked table
'Sample statemenet -> LinkODBCTable "table_name_to_show_in_access",
"table_name_in_postgres"

Dim tdfLinked As TableDef
Dim rstLinked As Recordset
Dim intTemp As Integer
Dim ConnectInfo As String

' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
Set tdfLinked = currentdb.CreateTableDef(strTable)

'
On Error Resume Next
currentdb.TableDefs.Delete strTable
If g_Debug Then: On Error GoTo 0: Else: On Error GoTo EH

ConnectInfo = "ODBC;DRIVER={PostgreSQL};" _
& "DATABASE=hsdata;SERVER=clplinux;" _
& "PORT=5432;" _
& "UID=" & "YourUserNameHere"
& ";PWD=;READONLY=0;PROTOCOL=6.4;" _
& "FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;" _
& "ROWVERSIONING=1;SHOWSYSTEMTABLES=0;" _
& "CONNSETTINGS="

tdfLinked.Connect = ConnectInfo
tdfLinked.SourceTableName = strSourceTable
currentdb.TableDefs.Append tdfLinked

Set rstLinked = currentdb.OpenRecordset(strTable)

Debug.Print "Data from linked table:"

' Display the first three records of the linked table.
intTemp = 1
With rstLinked
Do While Not .EOF And intTemp <= 3
Debug.Print , .Fields(0), .Fields(1)
intTemp = intTemp + 1
.MoveNext
Loop
If Not .EOF Then Debug.Print , "[additional records]"
.Close
End With

Exit Function
EH:
g_Error

End Function

respectfully,
Joseph
=============

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org]On Behalf Of Markus Wagner
Sent: Monday, August 27, 2001 12:08 AM
To: pgsql-odbc(at)postgresql(dot)org
Subject: [ODBC] Access 2000 ODBC table links

Hi,

there is a problem using Access 2000 as a frontend for pg data sources.
I make a new system DSN in the control panel and I leave blank the fields
for username and password. Then, I use this DSN to link some tables in an
Access MDB. So whenever I start this MDB and open some table, Access will
ask me for username and password. So far, so good, but: When I login in
this way for the first time, the username I give will be stored
somewhere, and you can never give another one. When I login for the second
time, after closing Access, I can change the username and password, but
Access
will send the username from the first login, with the password of the
current
login, and Postgres logs something like "password authentication failed for
user x".

So how can we use one frontend for different users?

Thanks,

Markus

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster