Thread: 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
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@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Markus Wagner
Sent: Monday, August 27, 2001 12:08 AM
To: pgsql-odbc@postgresql.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
On Mon, 27 Aug 2001, Joseph wrote:
> 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
--------------^^^^^^^^^^^^^^^^
This is part new to me.. appending a table that you just got out of
currentdb. Markus, have you tried this? FYI, I've been talking with
Markus a lot about various things. What he describes seems to be specific
to Access 2000. I can relink tables fine also in ACC97. As he said, 2000
seems to store the username away somewhere in the mdb forever.. even if
you close the mdb and reopen it.
-Cedar