ODBC TABLE SET

[Guest]

I am setting up an ODBC connection to an oracle db using linked tables. When
th e list of available tables comes back it is only a subset of the tables I
know are in the db. Any ideas?

 

[Pieter Wijnen]

Yepp, dependant on the version of Access the number of tables returned are
limited (don't remember the figures - but it's 255 for '97 i believe)
I Use a Passthrough query against the database :
SELECT TABLE_NAME FROM USER_TABLES '(or ALL_TABLES)
And save it As User Tables

I Then Use The
DoCmd.TransferDatabase method (or CreateTableDef) in a loop to link all
tables

incomplete code (away from access at the moment)

Dim Db As DAO.Database
Dim Rs AS DAO.Recordset

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("USER_TABLES",DbOpenSnapshot)
While Not Rs.EOF
DoCmd.TransferDatabase aclink,"ODBC;DSN=xxx;uid=yyy;pwd=zzz","ORAUSER." &
Rs.Fields("TABLE_NAME").Value ....
Rs.Movenext
Wend
Rs.Close : Set Rs = Nothing
Set Db = Nothing

HTH

Pieter

I am setting up an ODBC connection to an oracle db using linked tables.
When
th e list of available tables comes back it is only a subset of the tables
I
know are in the db. Any ideas?

--

 

[Pieter Wijnen]

Yepp, dependant on the version of Access the number of tables returned are
limited (don't remember the figures - but it's 255 for '97 i believe)
I Use a Passthrough query against the database :
SELECT TABLE_NAME FROM USER_TABLES '(or ALL_TABLES)
And save it As User Tables

I Then Use The
DoCmd.TransferDatabase method (or CreateTableDef) in a loop to link all
tables

incomplete code (away from access at the moment)

Dim Db As DAO.Database
Dim Rs AS DAO.Recordset

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("USER_TABLES",DbOpenSnapshot)
While Not Rs.EOF
DoCmd.TransferDatabase aclink,"ODBC;DSN=xxx;uid=yyy;pwd=zzz","ORAUSER." &
Rs.Fields("TABLE_NAME").Value ....
Rs.Movenext
Wend
Rs.Close : Set Rs = Nothing
Set Db = Nothing

HTH

Pieter