Creating ODBC table links

[Eric E]

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 sGetTableNamesS QL As String, sConnStr As String

' SQL to read the list of tablenames
sGetTableNamesS QL = "SELECT DISTINCT table_name, table_schema FROM
information_sch ema.table_privi leges " _
& " WHERE table_schema <> 'information_sc hema' and table_schema
<>'pg_catalog ' " _
& " ORDER BY table_name; "
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Set db = DBEngine.Worksp aces(0).Databas es(0)
With rstTableNames
Set .ActiveConnecti on = GetPGConnStr
.LockType = adLockReadOnly
.CursorLocation = adUseServer
.Source = sGetTableNamesS QL
.Open
While Not .EOF
Set tdf = db.CreateTableD ef(.Fields("tab le_name"), , _
.Fields("table_ schema") & "." & .Fields("table_ name"))

tdf.Connect = GetPGConnStr
'tdf.Attributes = dbAttachedODBC
db.TableDefs.Ap pend tdf
.MoveNext
Wend
End With
End Function

Public Function GetPGConnStr() As String
GetPGConnStr =
"ODBC;DSN=lims; DRIVER=PostgreS QL;SERVER=local host;PORT=5432; "
End Function

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The error you are getting is saying that the defined table needs the
columns (aka Fields ) defined that go w/ that table. IOW, you need the
full meta-definition of that table (columns, unique indexes, primary
keys) to "re-construct" it as an Access linked table.

To link ODBC tables it is easier to run the TransferDatabas e method of
the DoCmd object. Unfortunately, if the tables don't have unique
indexes/primary keys defined you will be prompted for those fields when
you run the TransferDatabas e method.

When that happens - what I've done is just Escape thru the pop-up prompt
& run a DDL statement (in an Access query) that adds the index/PK
constraints.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQaFTfoechKq OuFEgEQKubwCfZg MiBhgSzCwqZ86pl PS5gwaewAoAn3yu
uxwo3tBk2d1gVQa RjXRhAdHv
=hRBs
-----END PGP SIGNATURE-----
Eric E wrote:

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?

Code follows:
Public Function LinkAllTables()
' Link all tables

Dim rstTableNames As New ADODB.Recordset
Dim sGetTableNamesS QL As String, sConnStr As String

' SQL to read the list of tablenames
sGetTableNamesS QL = "SELECT DISTINCT table_name, table_schema FROM
information_sch ema.table_privi leges " _
& " WHERE table_schema <> 'information_sc hema' and table_schema
<>'pg_catalog ' " _
& " ORDER BY table_name; "
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Set db = DBEngine.Worksp aces(0).Databas es(0)
With rstTableNames
Set .ActiveConnecti on = GetPGConnStr
.LockType = adLockReadOnly
.CursorLocation = adUseServer
.Source = sGetTableNamesS QL
.Open
While Not .EOF
Set tdf = db.CreateTableD ef(.Fields("tab le_name"), , _
.Fields("table_ schema") & "." & .Fields("table_ name"))

tdf.Connect = GetPGConnStr
'tdf.Attributes = dbAttachedODBC
db.TableDefs.Ap pend tdf
.MoveNext
Wend
End With
End Function

Public Function GetPGConnStr() As String
GetPGConnStr =
"ODBC;DSN=lims; DRIVER=PostgreS QL;SERVER=local host;PORT=5432; "
End Function

[Eric E]

Hi,
Many, many thanks for the tip. The TransferDatabas e method works well,
and, as you predicted, linking to tables/view that don't expose a
primary key produces the same popup that occurs when using the table
linking wizard. Is there any way I can pass a SendKeys argument to
automatically hit ESC in these cases?

Thanks,

EE
MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The error you are getting is saying that the defined table needs the
columns (aka Fields ) defined that go w/ that table. IOW, you need the
full meta-definition of that table (columns, unique indexes, primary
keys) to "re-construct" it as an Access linked table.

To link ODBC tables it is easier to run the TransferDatabas e method of
the DoCmd object. Unfortunately, if the tables don't have unique
indexes/primary keys defined you will be prompted for those fields when
you run the TransferDatabas e method.

When that happens - what I've done is just Escape thru the pop-up prompt
& run a DDL statement (in an Access query) that adds the index/PK
constraints.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SendKyes -> Esc? Not that I know of. Since I consider linking ODBC
tables & views a developing/Administrator event I don't make it user
friendly. It's just a subroutine I run thru the debug window.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQaKg7oechKq OuFEgEQJTAgCeMc b8oXQQPv109aoxY 9GrSnWjSoIAoOvF
NuqNNjTKfTTfRyV uLMdUNy+9
=nz3F
-----END PGP SIGNATURE-----

Eric E wrote:

Hi,
Many, many thanks for the tip. The TransferDatabas e method works
well, and, as you predicted, linking to tables/view that don't expose a
primary key produces the same popup that occurs when using the table
linking wizard. Is there any way I can pass a SendKeys argument to
automatically hit ESC in these cases?

Thanks,

EE
MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The error you are getting is saying that the defined table needs the
columns (aka Fields ) defined that go w/ that table. IOW, you need the
full meta-definition of that table (columns, unique indexes, primary
keys) to "re-construct" it as an Access linked table.

To link ODBC tables it is easier to run the TransferDatabas e method of
the DoCmd object. Unfortunately, if the tables don't have unique
indexes/primary keys defined you will be prompted for those fields when
you run the TransferDatabas e method.

When that happens - what I've done is just Escape thru the pop-up prompt
& run a DDL statement (in an Access query) that adds the index/PK
constraints.

[Eric E]

Yeah, that's what I'm doing now. I was hoping to run a relink the first
time each user logged in, so that only the tables s/he has at least
SELECT access to would show up. I'll just have to work around it, I
guess. Anyway, thanks again for the helpful advice.

Cheers,

Eric

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SendKyes -> Esc? Not that I know of. Since I consider linking ODBC
tables & views a developing/Administrator event I don't make it user
friendly. It's just a subroutine I run thru the debug window.