Hi all,
I'm having quite a bit of trouble with code to create linked tables in
Access 2K. I create a DAO tabledef using CreateTableDef against a DAO
database object, then set its connection string appropriately. The
trouble is when I go to append the tableDef to the collection I get
error 3264:
No field defined --cannot append TableDef or Index
Any suggestions?
Many thanks,
Eric
Code follows:
Public Function LinkAllTables()
' Link all tables
Dim rstTableNames As New ADODB.Recordset
Dim sGetTableNamesSQL As String, sConnStr As String
' SQL to read the list of tablenames
sGetTableNamesSQL = "SELECT DISTINCT table_name, table_schema FROM
information_schema.table_privileges " _
& " WHERE table_schema <> 'information_schema' and table_schema
<>'pg_catalog' " _
& " ORDER BY table_name; "
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).Databases(0)
With rstTableNames
Set .ActiveConnection = GetPGConnStr
.LockType = adLockReadOnly
.CursorLocation = adUseServer
.Source = sGetTableNamesSQL
.Open
While Not .EOF
Set tdf = db.CreateTableDef(.Fields("table_name"), , _
.Fields("table_schema") & "." & .Fields("table_name"))
tdf.Connect = GetPGConnStr
'tdf.Attributes = dbAttachedODBC
db.TableDefs.Append tdf
.MoveNext
Wend
End With
End Function
Public Function GetPGConnStr() As String
GetPGConnStr =
"ODBC;DSN=lims;DRIVER=PostgreSQL;SERVER=localhost; PORT=5432;"
End Function
