Re: ms access app ? - Mailing list pgsql-general

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@postgresql.org [mailto:pgsql-general-owner@postgresql.org]De la part de
Tom.Zschockelt@flender.com
Envoyé : mardi, 26. octobre 2004 12:20
À : pgsql-general@postgresql.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,
whichdisable the full-qualified name  
to be shown to access in the case where the tableowner is connected to postgresql ?

thanks in advance

tom