Solved

Switching Access ADP file to different SQL database

Posted on 2004-08-10
11
877 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

749 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