Hello,

Yes, you can do that, programmatically:


Here is some DAO code for your Access project:

Dim strTblName As String
Dim strConn As String
Dim db As DAO.DataBase
Dim rs As DAO.Recordset
Dim login As DAO.Recordset
Dim tbl As DAO.TableDef
Dim strDSN As String

Set db = CurrentDb
Set login = db.OpenRecordset("select * from tblLogin")
Set rs = db.OpenRecordset("select * from tblODBCDataSources")

While Not rs.EOF

strTblName = rs("LocalTableName")

strConn = "ODBC;"
strConn = strConn & "DSN=your_global_dsn_name;"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & login("DataBase") & ";"
strConn = strConn & "UID=" & login("UID") & ";"
strConn = strConn & "PWD=" & login("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName")

If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext

Wend


Where:
------

1) tblLogin is a local table with the definition:

UID Text
PWD Text
Database Text
Server Text

2) tblODBCDataSources is a local table with the definition

ODBCTablName Text
LocalTableName Text

3)
Function DoesTblExist(strTblName As String) As Boolean

On Error Resume Next

Dim db As DAO.DataBase
Dim tbl As DAO.TableDef

Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)

If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If

DoesTblExist = True

End Function

4) your_global_dsn_name refers to your DNS project name



With the table tblODBCDataSources, you can choose the local table name.

I hope this helps.

Philippe Lang



-----Message d'origine-----
De : pgsql-general-owner (AT) postgresql (DOT) org [mailtogsql-general-owner (AT) postgresql (DOT) org]De la part de Tom.Zschockelt (AT) flender (DOT) com
Envoyé : mardi, 26. octobre 2004 12:20
À : pgsql-general (AT) postgresql (DOT) org
Objet : [GENERAL] ms access app ?



Hi,

we have a large ms-access application (as .mde file) which is connected to an oracle database.
Now we want to migrate to postgresql. Database migration has been done successfully but
when starting the access-application we get the following error:

"Cannot find table ... on database"

I manually connected to postgres via access and find out that postgresql provides every table with the
full qualifier, which means, that while access is looking for a table (e.g.mytable) postgresql provides only a table ( myschema.mytable ).

Is there a setting which prevent ms-access from for an unqualified name or the other way around, is there an setting, which disable the full-qualifiedname
to be shown to access in the case where the tableowner is connected to postgresql ?

thanks in advance

tom

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend