Unable to connect to ADP database

[Guest]

I have an ADP project that I work with inside Access. It worked fine until I
installed Visual Studio 2005 and SQL Server 2005. Now, Access cannot connect
to the database in the ADP file.

I read elsewhere that the ADP file works only with SQL Server 2000, but I
cannot find a way to reinstall SQL Server 2000. I can view the code modules
and forms in the ADP file but I cannot work with the tables or queries.

How can I regain access to the database?

Thanks.

Scott

 

[Larry Linson]

ADPs have been "deprecated" in Access 2007. Perhaps it was because of SQL
Server changes from 2000 to 2005. ADPs did not exactly "take the Access
world by storm", so it makes sense not to invest heavily in changes to make
them work with a new version of SQL Server.

I don't have an answer to your question, but perhaps someone else does.

I am hesitant to suggest that you can regenerate your application as an MDB,
with Jet, and use ODBC to link the Tables, and then it will not only work
with the current SQL Server, but will probably be easier to migrate to newer
versions of Office.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thank you for your response. I still have the issue, however, that the
project was created using new data, not an existing MDB. So, I do not know
how to extract the MDB that is contained in the ADP.

Scott

 

[Norman Yuan]

Access project (*.adp) should be still able to connect to SQL Server2005/SQL
Server2005 Express, although you cannot create/modify server objects via
ADP, like tables, views, sps... At least my adp file can connect to SQL
Server2005/Express. AFAIK, your adp app most like still works with SQL
Server2005, as long as you do not need to create/modify server objects
within the adp app.

What exactly happens when you say "cannot connect"? Get some error message
at all? Is the SQL Server 2005 login security being set correctly?

As for Access2007's support for ADP, I simply cannot find any reliable
information to clear things up, even its public beta2 has been out for
sometime now. It seems no insider (including those from Access team in MS)
is willing or allowed(?) to say a simple word: "supported" or "dropped".

 

[Douglas J. Steele]

Larry: I believe it's DAPs that have been deprecated, not ADPs.

 

[Guest]

When I open the ADP file, the title bar of the project windows says
Service Music (disconnected): Project (Access 2002 - 2003 file format)

When I execute the File>Connection menu option, the name of the database
(Service MusicSQL) is in the database drop down. If I click Test Connection,
I get the following message:
Test connection failed because of an error in initializing provider.
Cannot open database "Service MusicSQL" requested by the login. The login
failed.

If I expand the database dropdown, the only databases listed are
master
model
msdb
tempdb

Scott

 

[Norman Yuan]

1. Which version of Access do you use? I use Access 2003.
2. Can you coonect to SQL Server2005 with SQL Server2005 Management
Studio/Express, or Enterprise Manager?
3. Do you use Windows Integrated Security or SQL Server security to log in?
Have you tried both (if mixed login mode is enabled)?
4. Are the ADP project and SQL Server2005 on the same machine?
5. Does the adp still can connect to a SQL Server2000/MSDE (if you still
have one on the network somewhere)?

As I mentioned previously, my ADP projects have no problem to connect to
either SQL Server2000/MSDE or SQL Server2005/Express. So, the most possible
cause of your problem, I bet, is login/security setting if the adp and SQL
Server2005 on the same machine, or the SQL Server2005 remote connection
access is not enabled if they are on different machine (default installation
setting, but it seems unlikely, since you can see Master db in the database
dropdown list).

 

[Guest]

1. I use Access 2003.
2. I can connect to SQL Server Management Studio, although when it opens I
get the following message: Cannot create a stable subkey under a volatile
parent. There is a Continue button, and I click it. The same databases appear
in Object Explorer as were in the previously-mentioned drop down.
3. I use Windows Integrated Security. (I have never had to log in to the
application or database before.)
4. The ADP project and SQL Server 2005 are on the same machine.
5. SQL Server 2000/MSDE is no longer on my machine. I had the impression
that, when I installed SQL Server 2005, I should (or could) remove all
previous versions of SQL Server. I reinstalled Access 2003, hoping that that
would restore SQL Server 2000/MSDE, but it did not. I have not been able to
find a way to reinstall SQL Server 2000/MSDE. (It sounds like reinstalling
SQL Server 2000/MSDE is the solution to my problem.)

 

[aaron.kempf]

either that; or just finding the MDF and LDF files and re-attaching
them to SQL 2005.

I use ADP against SQL 2005 all the time... i love the performance

 

[Norman Yuan]

Arron has a good point.

I assumed that you installed SQL Server2005 correctly and attached all your
own database to it. It seems you did not attach your database (named as
"Service MusicSQL", right) to SQL Server2005. That explains why you can see
Master database in dropdown list, but not your database.

So, as Arron suggested, you need to find the database files (*.mdf/*.ldf)
and attach to SQL Server 2005 instance.

BTW, installing SQL Server2005 does not requires you to remove SQL
Server2000/MSDE. They can stay on the same computer very well. Also,
reinstall Access does not reinstall SQL Server2000/MSDE. Even you
reinstalled SQL Server2000/MSDE, you still need to attach back your database
before you can use it. The database is yours, not part of SQL
Server2000/MSDE/SQL Server2005

 

[Guest]

I did a search on my computer and there is no "Service MusicSQL.mdf" file.
Did it somehow get deleted when I uninstalled SQL Server 2000? I looked in
the Norton Protected Recycle Bin and the file wasn't there either.

 

[Norman Yuan]

The data base file name may not be the same as the database name. If you did
not attach any database file to SQL Server 2005 after installing the SQL
Server 2005, then it is for sure that is the cause of your problem.

I do not remember that uninstalling SQL Server would delete all database
files. However, whenever I have to uninstall SQL Server, I alway make sure I
have database backed up/detached first. So I really do not rember if there
is an option to delete all data file when uninstall SQL Server.

Anyway, search your computer for *.mdf/*.ldf file (or *-log.ldf) files,
which exist in pair. If you do find some of these files, try to copy/move to
other folder. If you cannot copy/move, the files are currently attached to
SQL Server. If you can, the files are detached from SQL Server, and could be
the ones you are looking for.

 

[Guest]

I found the file. This time, when I searched, I told Explorer to include
system and hidden files. The file was in C:\Program Files\Microsoft SQL
Server\MSSQL\Data. That directory was not visible when I was searching
before. I'm not sure why, I'm just glad that I found the file. I connected to
it and everything is fine.

Thanks for your help (all of you) and your patience.

 

[aaron.kempf]

yes; i am eternally grateful that uninstalling sql doesn't kill those
files.

i learned that the hard way; i told some network admin 'i need 3 more
days to use this server' and he went ahead and

a) uninstalled my Sql
b) installed Sybase.

I was convinced my goose had been cooked... but i found the file and i
was SOOOOOOOOOOOOOOOOOOOOOOOOOO happy

 

[aaron.kempf]

Arron has a good point.

I assumed that you installed SQL Server2005 correctly and attached all your
own database to it. It seems you did not attach your database (named as
"Service MusicSQL", right) to SQL Server2005. That explains why you can see
Master database in dropdown list, but not your database.

So, as Arron suggested, you need to find the database files (*.mdf/*.ldf)
and attach to SQL Server 2005 instance.

BTW, installing SQL Server2005 does not requires you to remove SQL
Server2000/MSDE. They can stay on the same computer very well. Also,
reinstall Access does not reinstall SQL Server2000/MSDE. Even you
reinstalled SQL Server2000/MSDE, you still need to attach back your database
before you can use it. The database is yours, not part of SQL
Server2000/MSDE/SQL Server2005