Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Unanswered: Transferring data from one database to another.

    I've got a database that I have been tasked with converting from 97 to 2000 in addition to making several major modifications. I want to take the database and split it into a front-end which will be located on each user's workstation and a back-end located on a central server. User's will 'check out' the data on the back-end, which will append the data to the user's front-end application and then 'check-in' the data once they are finished.

    The problem I'm looking at now is the best way of transferring data from the back-end database to the front-end. In addition to this, I also want to make sure that it is scalable to work in a SQL Server (another future project).

    Does anybody have any suggestions on the best course of action to take with this? I've been thinking about it for several days now but just can't decide on the course to take.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why the check in /check out process?

    Conventionally if you split you application into 2 the back end (the data store) resides on a server, the front end (contains the user interface [forms, reports, common modules etc... and optionally some local tables contianing configuration data or large static tables which never change, or rarely, change]) resides preferably on easch users local disk. Using this approach you don't have to push data up and down the network cable, for which your network trolls we be grudging gratefull. if you are seriously considering moving the app to a server then take the opportunity right now to write it as if it is using a server. In fact unless the time constraints are so severe I'd reccomend you go the server route right now (MSDE is available for nothing).

    buy a decent book which points out the design methodology to maximise the benefits of using a server that way round you oinly have to pay the pain of rewriting the app once. besides which its a good habit to get into when designing access apps.

    you will have some problems converting from 97 to 2000, especailly as some of the object properties have changed, new ones created and old ones dropped. Do a search on MSDN to find out what the pitfalls are on doing the conversion.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2005
    Posts
    5
    Thanks for the quick response.

    The reason for the check-in/check-out process is mostly due to the amount of data (the old database grows by 2000 - 3500 items a day) coupled with 15-25+ users in the system at the same time. The old database (the 97 one) runs into all kinds of lock violations due to 15 people running a query against the same table at the same time. I want to distribute x amount of records (say 100) to a user upon them signing into the system, then as they complete so much, say 20 items, it sends the updated portion back the original table.

    As for me building it with a SQL Server, it is mainly just a plan for the distant future. Due to certain restrictions placed against me in my current job, I'm only allowed to use Access, nothing else. I just want have everything set up so the conversion to a server is less of a hassle.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access 2000/ JET 4 uses a far better locking mechanism than its predecessors - you can use row level locking, rather than page or table locks. This should work well for you in most situations. However if you plan to go to a server based mechanism in the foreseeable future then you may need to switch to another mechanism.

    It might be better to use another means of handling what would be lock errors - that is implement your own mechanism to handle when errors may occur. Usually this is done using a timestamp on each update, checking the timestamp hasn't changed in between read and before committing new changes.

    If row level locking is applicable to your application then it may well be an approriate half way house, especailly if your intention to switch to a server is tentative.
    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
  •