Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Import or redo?

Posted on 2014-09-25
5
Medium Priority
?
375 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 40

Assisted Solution

by:PatHartman
PatHartman earned 400 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 1600 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 40

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

886 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