Skip site navigation (1) Skip section navigation (2)

Re: ms access app ?

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <Tom(dot)Zschockelt(at)flender(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: ms access app ?
Date: 2004-10-31 09:06:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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"), _
            db.TableDefs.Append tbl
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
        End If


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

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 ?


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 


pgsql-general by date

Next:From: Ken TozierDate: 2004-10-31 10:24:34
Subject: Re: Superuser log-in through a web interface?
Previous:From: Claudio DuffiniDate: 2004-10-31 08:10:29
Subject: Re: Upgrading from beta3 to beta4

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group