Обсуждение: 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
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