Solved

Access Import or redo?

Posted on 2014-09-25
5
349 Views
Last Modified: 2014-09-26
Helloo

There is a production DB that is used by user. I made a copy of the DB and made changes to a table (added few columns) and then changed the form for that table. I don’t want to redo the changes I did within the production database. Is there an easy way to import those changes into the production DB– like import the whole table and the whole form – if that’s possible? Secondly doing it, the form will still work? Its reference for that table will not break if tis pushed to a different DB?
What are some best practices you guys conduct to do such change between test db and production db?

Thank you
0
Comment
Question by:Rayne
  • 2
  • 2
5 Comments
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40345198
It is time to split the database.  The FE (front end) contains forms/reports/macros/modules/queries and the BE (back end) contains ONLY tables.  That way, you can make whatever changes you want to the FE and simply replace the old one without disturbing the data.

When you have to change a table, that becomes more difficult.  If it is just adding columns, you need to have the users exit the application. You then back up the BE and open it exclusively.  Make the necessary changes and save.  The user can now go back to work.  Meanwhile, you take a COPY of the BE to use with your COPY of the FE and then make the FE changes.  Once everything is tested, you replace the user's FE with your changed version.

You can use the wizard to split the database.  Start with the user's copy because that has the current data.  Then make the BE changes AGAIN to the split database.  Once you have a database with data, schema changes become more difficult to manage.  For the simple change you describe, this method will work.  Then take your modified copy and split it in your own local folder.  Discard the BE.  Copy the users BE to your test folder and your FE will now link to that BE assuming you used the same names.  Test your changes, then replace the user's FE with your now tested FE.

Of course, backup, backup, backup along the way.  This whole process is fraught with danger at this point and you really don't want to mess up and loose the production data.

If your changes are major or you have to support multiple copies of the BE as I do with software I sell, the procedure becomes quite different and must be automated so requires code to apply changes.  But, we won't go there at this point.
0
 

Author Comment

by:Rayne
ID: 40346171
Hello,
Thank you for the suggestion. Sure. My question was if I only updated one table and the form that sits on that table – I was wondering how easy it is to just import both the new form into the production table – assuming I will make change to the production table (adding new columns) and then will the form I created in the test DB work once I import it into the production DB? Or do I have to re-create the same form in the production db too?
That was my question..
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 400 total points
ID: 40346192
You should be able to import the form from one database to another.  If there are differences in the table to which the form is bound, you can make the needed changes on the imported form, but that would take much less time than recreating the form from scratch.

Bear in mind that if you have an existing form with the same name, the imported form will have a 1 at the end of its name.
0
 

Author Closing Comment

by:Rayne
ID: 40346302
thank you
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40346545
Yes, as Helen indicated, with changes to only one form and adding a couple of columns to a table, you can squeak by just importing changed form into the production FE and modifying the table schema in the FE.

However, you did ask for best practices and that is far from best practices.  

Best practice is to ALWAYS split the database once you are ready to deploy it for the first time.  During initial testing, keeping the tables with the rest of the objects is fine.  But, the FIRST thing to do once the app is ready to be distributed is to split it.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

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