Solved

Access Import or redo?

Posted on 2014-09-25
5
353 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
[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
  • 2
  • 2
5 Comments
 
LVL 37

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 37

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

710 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