Access 2010 permissions issue revisited

[vba317]

I have copied an Access 2003 working database from one server to another. On the new server I created 5 new system DSN's through the SQL server with the ODBC connection program. I than created the ODBC connection in the Access database. I see all 5 ODBC connections I created from within the database. When I go to the connection wizard and I click on machine data source I see all the databases I linked. The problem is whatever database I click on I can only see system tables and not the tables I need. I tried to build an Access 2010 database with the same results. Any help is appreciated.

Tom

 

[vba317]

Thsnk's for that update. I just moved the module from a class module to a module.
Now I am getting a compile error on my code Call FixConnections(AMSPBI,rptdata_ahs) the AMSPBI2 is being highlighted. That is the correct name of the server.
Tom

 

[dhookom]

I expect the function expects either memory variables or literal strings like:

Call FixConnections("AMSPBI","rptdata_ahs")

The web page

If you've added this code in a module, you can invoke it from the Debug Window (Ctrl-G) by typing:

FixConnections "MyServer", "MyDatabase"

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Duane,
Once again thanks for your patience! Going back and forth between sql and access I got the ' mixed up with quotes. I got the FixConnections code to run but now all my linked tables are deleted from the database. I tried to relink the tables using the normal process but I still can only see the system tables only.

 

[dhookom]

Are the system tables from the appropriate database or are they from the Master database?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

They are from the master table.

 

[dhookom]

It appears that either the default database has not been set for the login in SQL server or the ODBC/DSN doesn't note the default database.

Duane
Hook'D on Access
MS Access MVP