Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Migrating data from a database structure to another

    Hi! I'm sure this question has already been asked somewhere, but I can't find it... I just produced a database with microsoft access, but I suppose it is possible I will have the need to change something in the structure or in the queries. What can I do to migrate data from the old version to the new when this will happen?
    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you deploy your application deploy it as two elements
    a front end contains the user interface (forms, reports, queries and so on
    a back end which stores the data, the tables

    when you make changes make changes to a copy of the front end, and you should be connecting to a copy of the back end so anything you do whilst developing and testing will not affect the live data

    when you are satisfied your new application front end is ready to roll then copy the development front end to the live front end, make certain the data is pointing to the correct (live data)

    and always take copious backups of what you are working on, and always retain a copy of the old version just in case your testing isnt' as comprehensive as you 'know' it to be

    if you are up to it there is some good stuff in the code bank whihc details how you can smooth out the deployment of front end's.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Posts
    16
    Unfortunately I have all in a single Microsoft Access file, I only have tables and queries. I access using a VB application. Can I move the queries to another Microsoft Access file?

    Or, would it be a good solution to build a query which exports all the data to a temporary recordset, and an import query which inserts those records into the tables in the new db?

    Thanks for your help!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't think it would help to move the queries to another MSAccess file. It would probably mean additional processing since you would then be connecting to 2 separate mdb files (one with the tables and the other with the queries.) So basically your backend is MSAccess tables/queries and your frontend is VB?

    Figuratively, this is similar to an MSAccess frontend and a SQL Server backend.

    Once the backend tables/queries are modified, the frontend is then modified to reflect the new changes. If testing goes ok on your development platform as healdem mentioned, then you roll those changes out to the network or desktop where users are running it (live or deployemnt platform).

    For example in your situation:

    1. You make your table/query design changes in the development version of the mdb file.
    2. You make your coding changes in your vb frontend (development).
    3. You test to make sure everything works ok.
    4. You add/modify the table/queries in the mdb file in the live environment.
    5. You create a new *.exe file from the VB design and copy the *.exe file to where the users open it.

    How you handling version of code changes are up to you. You can utilize source-safe (which I didn't like), or you can add a versioning type table into the mdb.

    Note also: this is the way I typically do it. Others may have a different method.
    Last edited by pkstormy; 11-18-09 at 20:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so in reality you have a JET file, not an Access file. Acces is a front end tool not a databaase,, although confusingly JET and Access share the same structure and file formats.

    So when you make changes you have a few options
    you could write a quick and dirty program which copies your SQL from the development db to the live DB. or you could make the changes to the live queries using DDL variant of SQL (eg ALTER TABLE, you will probably have to delete the old queries and replace with new) if you use the second appraoch you should have your quick and dirty program available form day 1 so that each and every chaneg you apply to the queries or the database are immediately enacted in the Q&D program so that you don't have to scratch your head trying to work out what you have changed

    or you could modify your production executable so that it checks to see if a query exists and if not creates the query, when you come to deploy a new version of the code delete all the queries and let the program self heal by writing its own queries.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Healdem,

    He mentioned he developed an MSAccess table with a vb front-end. When I had the same type of setup and wanted to make a table design change, I simply made it in the backend MSAccess table itself (both development and deployment) and then the neccessary changes in the vb frontend design. Why go through the hassle of writing an ALTER TABLE statement? I found trying to 'automate' or synchrenize the development/deployment environment usually leads to more work just maintaining the automation process itself, not to mention if you throw in a testing environment which I've seen at some companies. I use SQL Server and it's much easier to make the design change on SQL Server versus an ALTER TABLE statement. I would see other developers struggle to get their code working to do this and to try to keep everything automated (and spend hours on what took me 5 minutes to do) and I guess I'm wondering why. It isn't a proper audit trail if that is the intent.
    Last edited by pkstormy; 11-19-09 at 21:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    cos hes got to make the changes on his developemnt db, and the when it comes to deployment time to the live db. yes you can open up the db make the changes. for me it makes sense to build an update program so the changes are comprehensive to the live db. you know whats been done. I suppose you could write a prograsm which comapred the system objects between the development & live db's

    if the live db is outside of his direct control then sending an update program which applies all the changes using DDL then he doens't have to be on site, the customer doesn't have to pay costs to get a contractor on site.

    if the live db is under the control of the developer then fine. make the changes direct in the live db.

    for me the advantage of the Q&D program is that as each change to the db is identified its added to the Q&D program, and you know that when it comes to deployment its run that program and all changes are rippled through the live db. you have an audit trail (sort of) as to what changes have been made to the db.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •