I’m a college institutional researcher and have this problem with Access ODBC links to PeopleSoft Finance, and now our Student/HR 8.9 “upgrade” – both on SQL Server. I’ve read that the problem is that Access has a limit of 32,768 objects (tables, views, etc.). PeopleSoft's table count, unfortunately, exceeds that limit.
I found the potential solutions below in Microsoft.com/Office/Community Access discussions. My DBA had already proposed #1 (the last sentence portion), but as a researcher I need to see all tables, sample/test, & select those I need for a specific research project, so I’m hoping #2 or #3 might work. I’ve had similar problems using Access Project (.adp), and pass-through queries don't provide the functionality I need. So please share if you find these/others to work.
POST #1: get external data odbc limitation? “ . . . . I'd start by replacing the employee who designed a relational database with 50,000 tables with a trained DBA. . . . . 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.”
POST #2: Problem with link dialog list >= 35000 entries. “ . . . . I found a workaround for my Oracle / ODBC Problem by altering the following value in the registry: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\ODBC] "AttachableObjects"="'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'" In my case, excluding 'SYNONYM' from the attachable objects reduced the list in the link dialog to < 32768.”
POST #3: Set up an ODBC connection Programmatically? “ . . . . It's also possible, though, to go DSN-less. Doug Steele has an example at
http://www.accessmvp.com/djsteele/DSNLessLinks.html . . . . . Did you look at Carl's site -
http://www.carlprothman.net/Default.aspx?tabid=90 - to get the correct DSNless connection string for Oracle?”
