Galisteo8
asked on
Switching Access ADP file to different SQL database
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
--Galisteo8
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks for the help!
No problem!
Thanks for the points!
Chris
Thanks for the points!
Chris
ASKER
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"???
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"???
ASKER
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??????
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??????
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
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
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
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
ASKER
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??
In the old ADP this particular form kept producing the error "Not enough memory to complete the operation."
Any thoughts??
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
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
ASKER
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.