Solved

Switching Access ADP file to different SQL database

Posted on 2004-08-10
11
845 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Author Comment

by:Galisteo8
Comment Utility
Thanks for the help!
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
No problem!

Thanks for the points!

Chris
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 8

Author Comment

by:Galisteo8
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 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

7 Experts available now in Live!

Get 1:1 Help Now