Problem opening Access database (.adp) using MSDE

Hi,

I've created an access database using the .adp and using the microsoft SQL server service manager (MSDE that came with office corporate). Also, I was using data access pages as my user interface.

Everything was working fine until I move the .adp files into a computer in a network setting then I'm getting error whether I was accessing it throu the data access pages or the .adp.

Errors that I'm getting are:

1. Data provider could not be initialized.

2. [DBNETLB][ConnectionOpen(Connect()).] SQL Server does not exist or access
    denied.


Since I put the .adp in a networked computer, I thougth it would be the settings in the network. When I got home I've installed the servicepack 2 for MSDE and hoping that it would still work but unfortunately it did exactly the thing as in the office.


Well, I figure that everytime I change from one computer to another with there own MSDE installed have there own SQL Server and SQL server database name. So, those .adp database that I've created is expecting different SQL server name and SQL server database but since I've change the SQL Server Service Manager version or changed from one computer to another it found a different one. Therefore my .adp database wouldn't open.

So what can I do to fix this problem? I really need this database to work. Please help :-(




KosherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dfarthingCommented:
change the adp's connection "file - connection" to the sql server name on the pc you are installing on. If you need to connect in set the new connection to currentproject.connection and then this will dynamicly be set to the adps connection
0
KosherAuthor Commented:
Hi,

dfarthing, I've tried your idea but it's still giving me errors.

"If you need to connect in set the new connection to currentproject.connection and then this will dynamicly be set to the adps connection " 

I'm not quite sure on how you do this.
0
KosherAuthor Commented:
Also, I've noticed that I can't view any of my tables. After moved my .mdp files into another server(computer). Why is this happening?

Anyone? please
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dfarthingCommented:
OK try this

open your adp on the office machine click file then connection. Now use the dialog to select the server and database for your adp. what you have set there is the currentproject.connection now all you table will be connected via that connection. when you connect to currentproject.connection in code you will be connection to the same server and database.

I assume you have moved you have moved you database to the networked computers msde instance.

Let me know how you get on.
0
KosherAuthor Commented:
Hi again,

I've tried that one but the error that came up is when I tried to test the connection is:

Test connection failed because of an error in initializing provider. 0x80040e4d

And if I just press ok the error that i get is:

Microsoft access could not log on to the server. Verify that the log on information is correct.

Thanks for the time

0
dfarthingCommented:
ok erm, then problem is probably not with access then, create a new text document, rename it so it has an extension of .udl rather then .txt double  click it. Now select ole db provider for sql server then select your msde server, tap in your user name and password for the server now select the database. if you get as far as selecting the database its a good sign if so click test connection.

This will tell you if the machine itself is able to connect to the server, if so then i was wrong and the problem is with access, otherwise it is a problem with the client setup.
0
KosherAuthor Commented:
I really appreciate your time.

Anyways, I've tried what you suggested.
 1) I've dbl click on the .udl
 2) Choose the ole db provider for sql server
 3) I click on the server name
 4) For the password and login name I've used the Windows NT security
 5) The only database on the server is the master, model, msdb, and tempdb. If I put the name of my database it gives me error such as:

"Test Connection failed because of an error initializing provider can't open database requested in login 'SupportSQL' login fails.
 
0
dfarthingCommented:
and there in lies your problem your database is not attached to the msde. you do realise that that when you copy an adp the database on the sql server isnot moved?

you need to detach the database from your machine, copy the mdf and ldf files to the networked machine then attach them to the msde server.

then your database will be in the list and your adp will be able to connect
0
dfarthingCommented:
and there in lies your problem your database is not attached to the msde. you do realise that that when you copy an adp the database on the sql server isnot moved?

you need to detach the database from your machine, copy the mdf and ldf files to the networked machine then attach them to the msde server.

then your database will be in the list and your adp will be able to connect
0
dfarthingCommented:
or it could be that the networked machine has no log on for that database or though i would think you would still see it in the list.

If you have already attached your database then try logging on as "sa"
0
KosherAuthor Commented:
Hi,

I've search for .mdf and .ldf files and I can't find them anywhere in my computer.

Would it make any difference if I've changed the msde (came with the office) to msde servicepack 2? Because that's what I've done. In the process of changing I've uninstalled the msde 1 before I've installed the msde service pack 2.


0
dfarthingCommented:
Hello,

You mdf and ldf files are in something like c:\program files\microsoft sql server\data

and will be named yourdatabase.mdf and yourdatabase.ldf on the machine you created the database.

You need to detach the database before you copy them to insure they do not become currupt. It really doesn't matter which version you have you will still have these files. you do however need to be sure the version of msde you are going to attach this database to is the same as the msde version you are copying from.

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KosherAuthor Commented:
Thanks you for your patience, I certaintly learn something.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.