asked on

Cannot see my SQL in ODBC after detattach and reattachment

I have a SQL 2005 database that I detattached and moved the data and log to a different folder MSSQL/DATA was the original and move it to MSSAL/DATA/BQ

When I detached and did the move then reattached the database I could not find it in my odbc as I am using MS Access 2003 to access the database. I can see the database just fine in SQL 2005 but cannot seem to find it from my System DSN when I try to setup the ODBC. How do I fix this?

ASKER CERTIFIED SOLUTION

tbsgadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peggyweber

ASKER

Its not at the MS Access level where you do a relink to the tables. Its at the administration level using ODBC Data Source Adminsistrator tool where on the the System DSN where you setup an ODBC connection and you clicked 'Next" two times and it asks you to "Change the defalut database to:....this is where I cannot see my reattached database. Seems ODBC forgots to look to see what is attached in SQL Server and only remembers the past database and goodness helps you if you deattach but it sees the database going away but does not see it come back when you re-attach

peggyweber

ASKER

I found out the reason, however I will reward the points to the person that give me the right answer.

Hint: MS Access need ODBC to map to SQL Server Named Database, ODBC need to see the database. Think how the ODBC can see a database. Note: has nothing to do with MS Access and the ODBC is working fine.

peggyweber

ASKER

The Database stilled had the user defined but was lost was the user or user groups at the System level not having access to the database. When you detach a database and re-attach at least in my case the users associated with that database where no longer associated. As soon as I made the associations the ODBC with Access worked.