MS Access is making things harder than they need to be
June 9, 2011 9:56 AM Subscribe
MS Access: I have responsibility for a large database in Access 2007. How can I create a dev copy to experiment with changing forms/layout/queries, etc. which shows record data, but does NOT link to and make changes to data in the tables in the "real" original database? I do not know SQL.
Our IT staff was laid off last month. I need to do this without SQL. I understand that I can just save a dev copy with another file name & muck around at will with the forms & layout, etc. without messing up the look of the "real" version ... but I want to also experiment with how the table data itself behaves in these forms by entering sample data and changing existing data in the records of the dev copy. So how I can create a copy of the entire DB including the tables and records, in which I can change any part of the dev copy's infrastructure or data itself, but will not change the records in my main DB? To explain what I mean: Assume the main db is titled "UserDatabase.mbd" located in a folder named Files01 on the server. I save a copy of the entire db titled "DatabaseDev060911.mdb" in a different folder named Files02 (by using file>"save as"). Now I can open "DatabaseDev060911" and goof around all I want with the forms and layout of the dev copy, and the real database "UserDatabase" in the folder Files01 is unchanged. However, right now if I change any of the *record data* or the tables in my dev copy, those records are getting changed in our main db. This is bad. How to not do this? Is there a way when copying a whole database to copy the tables & records to use as sample data so I can experiment with them, but not have that (dev) data linked to the original? This seems like it should be simple but I can't find anything in the help screens or gis on how to do this.
Our IT staff was laid off last month. I need to do this without SQL. I understand that I can just save a dev copy with another file name & muck around at will with the forms & layout, etc. without messing up the look of the "real" version ... but I want to also experiment with how the table data itself behaves in these forms by entering sample data and changing existing data in the records of the dev copy. So how I can create a copy of the entire DB including the tables and records, in which I can change any part of the dev copy's infrastructure or data itself, but will not change the records in my main DB? To explain what I mean: Assume the main db is titled "UserDatabase.mbd" located in a folder named Files01 on the server. I save a copy of the entire db titled "DatabaseDev060911.mdb" in a different folder named Files02 (by using file>"save as"). Now I can open "DatabaseDev060911" and goof around all I want with the forms and layout of the dev copy, and the real database "UserDatabase" in the folder Files01 is unchanged. However, right now if I change any of the *record data* or the tables in my dev copy, those records are getting changed in our main db. This is bad. How to not do this? Is there a way when copying a whole database to copy the tables & records to use as sample data so I can experiment with them, but not have that (dev) data linked to the original? This seems like it should be simple but I can't find anything in the help screens or gis on how to do this.
Best answer: Sounds like you have a front end & a back end. Hopefully the connection between the two is not byzantinely complex. The first thing I would try is this:
->Locate the two related databases (they will be separate access files) & copy them both to a folder on your computer. This is as simple as [right click on database] -> [select "copy" from the popup menu] -> [right click on the destination folder] -> [select "paste" from the popup menu]
The back end is the file that stores the data, the front end is the file that contains the user interfaces like forms & queries & reports. Collectively, when working together, they are your "database."
-> Open the front end (the one you use to input & view stuff).
If you are in Access 2007 there will be a nifty feature under the file menu called "link table manager" or something like that. Select all the displayed tables, then click the appropriate box to "refresh" the links. You can browse to help it find the new location for the back-end copy you made.
If you are in earlier versions of access, you may have to remove the old tables & re-link them all in one by one to the copied version of the back end. I like to retain the old links by re-naming them (so that I have something to go back to in case of errors), then linking in the tables again. To link a table, go to [File] -> [get external data] -> [link tables] & browse to get to the copy back end. From there you can select the table to link. Can't remember if it lets you do more than one at a time.
If you are wondering which tables are linked to the back end, and which actually live in the front end, open the front end in such a way as to be able to view the tables list. [If there is no way to see the tables list, close the front end, put your finger on the shift key, open the front end. Keep you finger on the shift key until the DB is as open as it is going to get. This should bypass any popups designed to hide your database window, where table & query lists are found.] A table that is linked in should have a little arrow to the left of the table icon right next to its name.
-> Relinking tables should work great, so long as no code is being used to connect tables. If there is code linking tables, you may need to do some digging through the Visual Basic code. As a non-visual basic coder myself, I can tell you it CAN be done, but requires patience & persistence to ID every point from which foreign tables are called by name.
If I've misunderstood about you having a two-file database, ignore all of the above.
posted by Ys at 10:45 AM on June 9, 2011
->Locate the two related databases (they will be separate access files) & copy them both to a folder on your computer. This is as simple as [right click on database] -> [select "copy" from the popup menu] -> [right click on the destination folder] -> [select "paste" from the popup menu]
The back end is the file that stores the data, the front end is the file that contains the user interfaces like forms & queries & reports. Collectively, when working together, they are your "database."
-> Open the front end (the one you use to input & view stuff).
If you are in Access 2007 there will be a nifty feature under the file menu called "link table manager" or something like that. Select all the displayed tables, then click the appropriate box to "refresh" the links. You can browse to help it find the new location for the back-end copy you made.
If you are in earlier versions of access, you may have to remove the old tables & re-link them all in one by one to the copied version of the back end. I like to retain the old links by re-naming them (so that I have something to go back to in case of errors), then linking in the tables again. To link a table, go to [File] -> [get external data] -> [link tables] & browse to get to the copy back end. From there you can select the table to link. Can't remember if it lets you do more than one at a time.
If you are wondering which tables are linked to the back end, and which actually live in the front end, open the front end in such a way as to be able to view the tables list. [If there is no way to see the tables list, close the front end, put your finger on the shift key, open the front end. Keep you finger on the shift key until the DB is as open as it is going to get. This should bypass any popups designed to hide your database window, where table & query lists are found.] A table that is linked in should have a little arrow to the left of the table icon right next to its name.
-> Relinking tables should work great, so long as no code is being used to connect tables. If there is code linking tables, you may need to do some digging through the Visual Basic code. As a non-visual basic coder myself, I can tell you it CAN be done, but requires patience & persistence to ID every point from which foreign tables are called by name.
If I've misunderstood about you having a two-file database, ignore all of the above.
posted by Ys at 10:45 AM on June 9, 2011
On preview, +1 to Ys.
posted by Doofus Magoo at 10:48 AM on June 9, 2011
posted by Doofus Magoo at 10:48 AM on June 9, 2011
Response by poster: Aha! That seemed to work! In the linked table manager, it didn't give an option to navigate where the new tables were until clicking "Always prompt for new location." Did that and hit ok... now seems to do what I want it to. Thanks Ys!
posted by cuddles.mcsnuggy at 11:51 AM on June 9, 2011
posted by cuddles.mcsnuggy at 11:51 AM on June 9, 2011
« Older Is this Planned Parenthood memo real? | How do I convince myself I don't need to convince... Newer »
This thread is closed to new comments.
posted by cuddles.mcsnuggy at 10:03 AM on June 9, 2011