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@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