?
Solved

Problem opening Access database (.adp) using MSDE

Posted on 2003-03-11
13
Medium Priority
?
910 Views
Last Modified: 2008-01-09
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 :-(




0
Comment
Question by:Kosher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 3

Expert Comment

by:dfarthing
ID: 8110503
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
 

Author Comment

by:Kosher
ID: 8124696
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
 

Author Comment

by:Kosher
ID: 8124747
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 3

Expert Comment

by:dfarthing
ID: 8126347
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
 

Author Comment

by:Kosher
ID: 8133295
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
 
LVL 3

Expert Comment

by:dfarthing
ID: 8134876
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
 

Author Comment

by:Kosher
ID: 8135171
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
 
LVL 3

Expert Comment

by:dfarthing
ID: 8135220
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
 
LVL 3

Expert Comment

by:dfarthing
ID: 8135256
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
 
LVL 3

Expert Comment

by:dfarthing
ID: 8135763
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
 

Author Comment

by:Kosher
ID: 8144608
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
 
LVL 3

Accepted Solution

by:
dfarthing earned 1160 total points
ID: 8146977
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
 

Author Comment

by:Kosher
ID: 8150594
Thanks you for your patience, I certaintly learn something.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question