469,963 Members | 1,225 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

Creating ODBC table links

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
Nov 13 '05 #1
4 13141
-----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 TransferDatabase 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 TransferDatabase 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQaFTfoechKqOuFEgEQKubwCfZgMiBhgSzCwqZ86plPS5gw aewAoAn3yu
uxwo3tBk2d1gVQaRjXRhAdHv
=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 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

Nov 13 '05 #2
Hi,
Many, many thanks for the tip. The TransferDatabase 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 TransferDatabase 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 TransferDatabase 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.

Nov 13 '05 #3
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQaKg7oechKqOuFEgEQJTAgCeMcb8oXQQPv109aoxY9GrSn WjSoIAoOvF
NuqNNjTKfTTfRyVuLMdUNy+9
=nz3F
-----END PGP SIGNATURE-----

Eric E wrote:
Hi,
Many, many thanks for the tip. The TransferDatabase 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 TransferDatabase 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 TransferDatabase 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.

Nov 13 '05 #4
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.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by colin.anderson | last post: by
7 posts views Thread by Patrick Olurotimi Ige | last post: by
5 posts views Thread by Randy Harris | last post: by
5 posts views Thread by aaron.m.johnson | last post: by
1 post views Thread by Scott | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.