Re: ms access app ?

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 [mailto:pgsql-general-owner(at)postgresql(dot)org]De la part de Tom(dot)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-qualified name
to be shown to access in the case where the tableowner is connected to postgresql ?

thanks in advance

tom