get external data odbc limitation?

[brinky]

I am using Access 2003.
I am using the "get external data" "link tables" option and then using a odbc
type connection. The selection box that comes up is limiting the number of
tables that I can choose. Our database has 50,000 tables in it and the
selection box is only showing the first 3200 or so alphabetically. Is there
a setting somewhere within Access that is limiting the number of tables that
I can see from the ODBC?

 

['69 Camaro]

Hi, Brinky.

The selection box that comes up is limiting the number of
tables that I can choose.

Actually, it's limiting the number of characters that can be displayed, not
the number of tables. Up to 64K of characters will appear in the list. The
longer the table names, the fewer the table names that will be displayed.

Our database has 50,000 tables in it and the
selection box is only showing the first 3200 or so alphabetically.

That number of tables far exceeds good relational database design and
management.

Is there
a setting somewhere within Access that is limiting the number of tables
that
I can see from the ODBC?

No. You only get 64K characters to display the table names. Use it wisely.

My advice is to connect to the database server as a user who has limited
permissions, not the database administrator, so that only the necessary
tables appear on the list. There's an additional work-around in Oracle, but
you haven't mentioned which database server you're using, so I won't mention
the additional Oracle solution, either.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[brinky via AccessMonster.com]

It's a Peoplesoft financials database on SQL server 2000. The user that uses
this option says at one time he was able to see all databases in the list and
then one day it started limiting him. We don't feel like we changed anything
on the sqlserver side that would have limited him, so I was assuming that
something changed within Access.
I can't say for sure whether the list did or did not ever show the full list
of tables, except to take his word for it.

Hi, Brinky.

The selection box that comes up is limiting the number of
tables that I can choose.

Actually, it's limiting the number of characters that can be displayed, not
the number of tables. Up to 64K of characters will appear in the list. The
longer the table names, the fewer the table names that will be displayed.

Our database has 50,000 tables in it and the
selection box is only showing the first 3200 or so alphabetically.

That number of tables far exceeds good relational database design and
management.

Is there
a setting somewhere within Access that is limiting the number of tables
that
I can see from the ODBC?

No. You only get 64K characters to display the table names. Use it wisely.

My advice is to connect to the database server as a user who has limited
permissions, not the database administrator, so that only the necessary
tables appear on the list. There's an additional work-around in Oracle, but
you haven't mentioned which database server you're using, so I won't mention
the additional Oracle solution, either.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I am using Access 2003.
I am using the "get external data" "link tables" option and then using a

[quoted text clipped - 7 lines]

that
I can see from the ODBC?

 

['69 Camaro]

Hi, Brinky.

The user that uses
this option says at one time he was able to see all databases in the list
and
then one day it started limiting him.

The wizard has never shown more than 64K characters in the list of ODBC
tables in any version of Access. Either the number of tables has increased
nearly 20-fold since that time, or the lengths of the table names have
increased nearly 20-fold, or he had permissions to see far fewer tables, or
he's misremembering which tool he was using to see all of the tables listed.

We don't feel like we changed anything
on the sqlserver side that would have limited him, so I was assuming that
something changed within Access.

Nope. Access hasn't changed the limit. Access only shows the first 64K of
characters of all of the tables and views the user is authorized to read in
that database.

I can't say for sure whether the list did or did not ever show the full
list
of tables, except to take his word for it.

Regardless, you need to fix the problem. I'd start by replacing the
employee who designed a relational database with 50,000 tables with a
trained DBA. Data is being recorded in table names, instead of in the
columns of the tables, where they belong.

In the short term, create a role that has permissions limited to only the
tables needed, create a new user and assign it to that role, then connect to
SQL Server with that User ID.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

It's a Peoplesoft financials database on SQL server 2000. The user that
uses
this option says at one time he was able to see all databases in the list
and
then one day it started limiting him. We don't feel like we changed
anything
on the sqlserver side that would have limited him, so I was assuming that
something changed within Access.
I can't say for sure whether the list did or did not ever show the full
list
of tables, except to take his word for it.

Hi, Brinky.

The selection box that comes up is limiting the number of
tables that I can choose.

Actually, it's limiting the number of characters that can be displayed,
not
the number of tables. Up to 64K of characters will appear in the list.
The
longer the table names, the fewer the table names that will be displayed.

Our database has 50,000 tables in it and the
selection box is only showing the first 3200 or so alphabetically.

That number of tables far exceeds good relational database design and
management.

Is there
a setting somewhere within Access that is limiting the number of tables
that
I can see from the ODBC?

No. You only get 64K characters to display the table names. Use it
wisely.

My advice is to connect to the database server as a user who has limited
permissions, not the database administrator, so that only the necessary
tables appear on the list. There's an additional work-around in Oracle,
but
you haven't mentioned which database server you're using, so I won't
mention
the additional Oracle solution, either.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I am using Access 2003.
I am using the "get external data" "link tables" option and then using a

[quoted text clipped - 7 lines]

that
I can see from the ODBC?

 

[college researcher]

Since the last post has anyone found another way to get around the Access
ODBC table linking limitation ’69 Camero/Gunny cites -- i.e., that an Access
Jet (.mdb) database will show only 64K of characters in returning a list
(table names in this case)?

I'm a college institutional researcher and also must contend with
PeopleSoft's 50,000+ Finance tables (on SQL Server), plus those in Student/HR
8.9 (residing by PeopleSoft design on a separate server). While my DBA (all
seem to be stretched thin these days) has provided the solution Gunny cites
(i.e., "create a role that has permissions limited to only the tables needed,
create a new user and assign it to that role"), for a researcher it's best
for a number of reasons to be able to see all tables. While only a fraction
of PeopleSoft's tables may be needed for research purposes, PeopleSoft
doesn't provide a roadmap, table descriptions, etc., and one must do their
own mapping and stay on top of tables being used and added. If one can't see
the table names and test/sample them as needed, it's extremely difficult to
give one's DBA “the†limited list of tables needed.

In terms of one alternative that has been suboptimal so far, I have been
able to link to all tables in PeopleSoft Student 8.9 via use of Access
Project (.adp), but haven't figured out a better way than export/import to
get records/tables needed into my Access Jet database. My Jet databases
incorporate tables from other data sources as well (via ODBC or import), and
it's relatively easy and fast to transform fields in tables from different
sources as needed (e.g., convert numbers stored as text, perform crosswalks,
etc.), and further link those tables to selected records/tables from
PeopleSoft (whether linked via ODBC or "downloaded" via an Access "make
table" query). Further, I use an add-in called "CSD Tools 2000" (copyright
2007, Conrad Systems Development) that assists greatly in mapping tasks --
i.e., it creates a list of an Access databases tables (including linked
PeopleSoft and other tables), their fields, properties, record count, etc.
This can then be imported as part of an overall "data dictionary" (which
PeopleSoft lacks). I haven’t gotten CDS Tools to work with Access Project
(or found a substitute).

In brief, there are other advantages (including compatibility with other
college office software in use) & time savings in using Access Jet databases
for college institutional research, so I/others would greatly appreciate any
suggestions or solutions (direction, tools, ad-ins, whatever) for getting
Access Jet ODBC to link or show all PeopleSoft tables on SQL Server. I'm at
the power user level, and would tap my DBA regarding technical suggestions or
material anyone may with to cite.

 

[Aaron Kempf]

im sorry

Peoplesoft has 50,000 tables?


ROFL

I'd reccomend using Access Data Projects; this ges around alot of the other
crap in MDB

File, New, Project (existing data)

 

[Aaron Kempf]

if you want to put information into 'another database' u should create a 2nd
SQL database not a MDB

Select * from database1.dbo.MyTable can refer to Select * from
database2.dbo.MyTable without any connection string crap

Congrats on moving to ADP!
ADP rocks!

 

[Aaron Kempf]

I'd reccomend

Select so.Name as TblName, sc.Name as ColName
From Sysobjects so Inner Join SysColumns so on so.id = sc.id
Where so.Xtype = 'u'

This will give you a list of ALL of the columns and tables in your database

yeah most of the people around here are stuck in the 90s so I wouldn't
expect Conrad to know SQL Server.. since he's a MDB cry baby

rther, I use an add-in called "CSD Tools 2000" (copyright
2007, Conrad Systems Development) that assists greatly in mapping tasks --
i.e., it creates a list of an Access databases tables (including linked
PeopleSoft and other tables), their fields, properties, record count, etc.
This can then be imported as part of an overall "data dictionary"