Solved

Access Import or redo?

Posted on 2014-09-25
5
328 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 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thank you
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now