Solved

Switching Access ADP file to different SQL database

Posted on 2004-08-10
11
861 Views
Last Modified: 2008-03-10
I have an Access database project (.adp) file which I copied from my home computer to the server here at the office.  The Access forms and views were all designed to work with a SQL database at home that is the same as the one here on the server.... except that the SQL Server name is different, so the adp file says it is disconnected from its underlying SQL database.  Which, of course, it is.  Is there a way to get the Access adp to "switch" to the local SQL database here on the server?

--Galisteo8
0
Comment
Question by:Galisteo8
  • 6
  • 5
11 Comments
 
LVL 5

Accepted Solution

by:
Krys_Wilson earned 50 total points
ID: 11764324
Galisteo8,

If you go to File -> Connection -> Connection Tab

Item number 1 is where you can switch the server name.  Item number 2 is for username and password if different and Item number 3 is for Database name if different.

I use this all the time to switch between a dev server and a live server.

HTH

Chris
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 11764544
Thanks, Chris. I didn't see the Connection... option in my File pulldown, since it is rarely used. I didn't think to expand the list!  D'oh!  LOL

Say, for my reference, is there any easy to transport an entire SQL database in the same manner that I copied the ADP file?  I know I can export tables, but I have to do it one at a time, and then, of course, import them one at a time to their new home. And I don't know what to do with views and/or queries...? As I'm moving back and forth between the dev environment here and at home, this might facilitate things, especially if I wind up making additional changes to the SQL back-end.
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 11764841
Unfortunately I am not a big SQL guru but I think you might be able to do it with a backup of the database.

Backup the database at work, then import the backup into your dev server at home.  If this is a live SQL server at work, do not do the opposite though and import back into the live version.  That will kill all of your new data.

Other than that I am not sure what else would work.

Chris
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 8

Author Comment

by:Galisteo8
ID: 11765297
Thanks for the help!
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 11765514
No problem!

Thanks for the points!

Chris
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 11766431
Sure thing.

Hey -- by the way -- When I close and re-open the ADP file, I have to go through the Connection process again. Is there any way to make it "stick"???
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 11767290
Hmmm... Another important issue. I may have accepted the answer too quickly!  ;-)

Not only do I have to re-connect the ADP file every time I open it... It also seems that any forms I subsequently create in the ADP are just GONE when I re-open the ADP file and re-connect it to the SQL server once more. This is no good.  What gives??????
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 11769784
Sorry its taken me so long to answer!  I have never had that problem when using Access 2000 connecting to a SQL Server 2000 database.

I'm not at work now so I don't have an ADP to check but there might be an option in there to keep your server name in there.

Ill check in the morning!

Chris
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 11774217
Ok, after checking around in my ADP connection dialog box there doesn't appear to be anything that would clear your connection.  After I have changed the connection, it stays that way until I change it again.  I use a completely different SQL 2000 server for my development than I do for my live databases so its a very similar process.

The only thing I did find was in the ALL tab of the connection dialog box.  There is an option for Persist Security Info that is set to true on my app.

Under my Connection tab:

In box 1: I have my server

In box 2: I have use a specific username and password checked.  Then I have my username and password filled out and the option for Allow saving password checked.

In box 3: I have Select the database on the server checked and my default database is filled out.

As for the forms, they have always persisted no matter what database I am connected to.  In an ADP project, all tables, queries, stored procedures, and database diagrams are stored on the SQL server.  The forms, reports, macros and modules are stored on the ADP so it doesn't make sense that you would lose the forms.  Maybe that ADP is corrupt.  Try making a new ADP file and connecting to the database you can't stay connected to.  Then, create a couple of quick forms and close and reopen several times to see if the forms dissappear again.

If they don't, import all of your old forms, macros, reports and modules to the new ADP and begin working from there.

HTH

Chris
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 11774912
Great idea!  Okay... I have now created a new ADP which seems to work okay (so far), so I've imported all the forms and reports into it from my other ADP.  'Cept for one form, everything seems to be working fine.  Is it possible for a single *form* within an ADP to be corrupted???  Basically, it doesn't open (in Design or Form view).

In the old ADP this particular form kept producing the error "Not enough memory to complete the operation."

Any thoughts??

0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 11780184
Yes, it is very possible for one form to be corrupt.  To be on the safe side, I would create another ADP if you imported the bad form into the the ADP thats working.

There are several different options to try and get lost data/objects back from access corruption.  I know EE has several bery good discussions on the subject.  Try compacting and repairing and see if that helps.

If you have a backup where that form does work, you can import the form from there.

If the compacting/repair doesn't work and if you don't have a backup Ill search around and see if I can find you some good discussions on corruption.

Chris
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now