Link Tables issue -- too many tables in SQL for Access?
I have a SQL Server 2005 DB that is over 90GB and now has over thirty-six thousand -- yes that is 36,000 -- tables. Its the back-end DB for our lan/server/network monitoring tool.
I have an Access XP (2002) front-end that was able to successfully link to the SQL DB before. We have recently added a large number of other monitoring probes so the number of tables has climbed significantly.
What is happening now is that when you go to link tables you go through the System DSN ODBC call and authenticate as any user id, even SA, and it only brings up the first table and none of the others (screenshot attached).
I suspect it is that the Link Table wizard can't handle over 32K tables and is choking on that (http://www.mssqlcity.com/Articles/Compare/sql_server_vs_access.htm).
Does anyone else have another idea?
I've run a checkdb repair_fast, checkcatalog, and did a sp_changedbowner 'sa' just for grins and all is coming back good. I also did a compact/repair on the Access DB and tried it with multiple Access DB all with the same results.
Link-Tables-Error.jpg
I have an Access XP (2002) front-end that was able to successfully link to the SQL DB before. We have recently added a large number of other monitoring probes so the number of tables has climbed significantly.
What is happening now is that when you go to link tables you go through the System DSN ODBC call and authenticate as any user id, even SA, and it only brings up the first table and none of the others (screenshot attached).
I suspect it is that the Link Table wizard can't handle over 32K tables and is choking on that (http://www.mssqlcity.com/Articles/Compare/sql_server_vs_access.htm).
Does anyone else have another idea?
I've run a checkdb repair_fast, checkcatalog, and did a sp_changedbowner 'sa' just for grins and all is coming back good. I also did a compact/repair on the Access DB and tried it with multiple Access DB all with the same results.
Link-Tables-Error.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
There is a front-end app that works with it already. It's the reporting that we're beating on.
We're extracting the data and dumping it out for analysis of uptime, reboots, data growth/disk utilization, connectivity etc.
We have other data sources for other things so its trying to consolidate it to a cohesive whole that we're having fun with.
I'm starting to think the method is to use the link table process that I have, and just have them link in from the Information_Schema.table view.
I was mostly wanting to confirm the limits is the problem.
We're extracting the data and dumping it out for analysis of uptime, reboots, data growth/disk utilization, connectivity etc.
We have other data sources for other things so its trying to consolidate it to a cohesive whole that we're having fun with.
I'm starting to think the method is to use the link table process that I have, and just have them link in from the Information_Schema.table view.
I was mostly wanting to confirm the limits is the problem.
ASKER
>> Do you need to link to all tables? Or just a subset.
Just a subset, but what is irritating me is the inability to select the table using the wizard.
Just a subset, but what is irritating me is the inability to select the table using the wizard.
Irritating, but not surprising :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very junior programmers who need a lot of hand-holding as it is. Trying to explain that to them is not the desired thing.