Solved

Switching Access ADP file to different SQL database

Posted on 2004-08-10
11
881 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
[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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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