RE: Access 2000 ODBC table links

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