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 : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] la part de [EMAIL PROTECTED]
Envoyé : mardi, 26. octobre 2004 12:20
À : [EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
