none
Bridging a complex access MDB database to a Dataset RRS feed

  • Question

  • My program is going to use a fairly complex database in access 2003 format.  What is the most streamlined way to copy ALL tables and associated restrictions, schema, and relations into memory?

    I am trying to simplfy my code so i only have to call a update command at certain intervals/events to repopulate information back to the hard disk representation of the data.

    the dataAdapter's Fill method only seems to work for a single table even though it fills a dataset.
    Saturday, October 20, 2007 3:09 AM

Answers

  • Hi,

     

    Just came across this post and thought I'd comment. This maybe extends Pauls comments and just offers an alternative approach you might think about.

     

    The alternative to loading all your data in would be to run SQL statements against the database. For example you might want to change 200 records but, depending on the change, that might be possible by executing one or two SQL statements against the database. If a combination of commands needed to be sent, perhaps representing a complete transaction, then you could encapsulate that in a method with the parameters being the data necessary to complete the update.

     

    I did this on a fairly complex database using traditional ADO and it solved all sorts of headaches. Obviously it depends on the project and what the goal is but, especially with an Access database (stored in file, rather than over the network) this would be fairly suitable solution. I wouldn't run 200 SQL commands though but if that can be reduced using WHERE statements then yeah it would work.

     

    Monday, October 22, 2007 8:27 PM
  • Well you can keep these updates in memory (in your DataSet) and write them all out at a later time, but how long do you want to wait to do this and does it make any difference that the file store would be out of sync during this time? Also, there is the potential risk that all of your changes would be lost should a fatal error occur at the application or system level.

     

    I would recommend experimenting a bit using a DataSet and see how it works for you. Remember that a DataSet is essentially disconnected from the data source until you need to retrieve or update data, so you already have a partial solution.

    Tuesday, October 23, 2007 1:09 PM

All replies

  • Why would you want to load all of the Access database objects into memory? Access implements local file based technology so performance isn't generally an issue.

     

    Monday, October 22, 2007 1:00 PM
  • The footprint of the objects will be small (or relativally so, round 39 megs at MAX) but have over 20 relationships and some complex validation rules.  I want to clone that to objects, which i have since found by using Strongly Typed datasets.  I also know how to fill them by use of queries and finally how to update them.  My big concern is how to set such a method or queue that has a set of updates to be sent to the db, which is only changed by the program, as so not to overwhelm the HDD and cause preformance issues.  The memory resident copy will be enough for opperation, the persistant one is for program termination and failure.
    Monday, October 22, 2007 2:50 PM
  • Well it sounds like you're attempting to re-invent the database technology and I guess I'm not sure why you want to do this. Essentially a DataSet is your in-memory queue. You can make changes to the data (in a DataTable or DataTables) and ultimately batch update to the file store.

     

    Is there some other need beyond a DataSet that you require?
    Monday, October 22, 2007 6:28 PM
  • There is no other need.  The real difficulty I am facing is to keep from overburdening the hard disk.  I need to either create or adapt a method that places updates in a queue of sorts and handles them on a "last priority" basis with the execption of some information such as error log entries and payments.

    As this is my first time using any ADO.NET structures am I making this much harder than it should be?  Is there really a worry if a system attempts to change 200 records on hard disk every second?  Will this cause noticable system slow down on an average computer to a point where optimization/batch processing is even an issue?
    Monday, October 22, 2007 8:07 PM
  • Hi,

     

    Just came across this post and thought I'd comment. This maybe extends Pauls comments and just offers an alternative approach you might think about.

     

    The alternative to loading all your data in would be to run SQL statements against the database. For example you might want to change 200 records but, depending on the change, that might be possible by executing one or two SQL statements against the database. If a combination of commands needed to be sent, perhaps representing a complete transaction, then you could encapsulate that in a method with the parameters being the data necessary to complete the update.

     

    I did this on a fairly complex database using traditional ADO and it solved all sorts of headaches. Obviously it depends on the project and what the goal is but, especially with an Access database (stored in file, rather than over the network) this would be fairly suitable solution. I wouldn't run 200 SQL commands though but if that can be reduced using WHERE statements then yeah it would work.

     

    Monday, October 22, 2007 8:27 PM
  • Well you can keep these updates in memory (in your DataSet) and write them all out at a later time, but how long do you want to wait to do this and does it make any difference that the file store would be out of sync during this time? Also, there is the potential risk that all of your changes would be lost should a fatal error occur at the application or system level.

     

    I would recommend experimenting a bit using a DataSet and see how it works for you. Remember that a DataSet is essentially disconnected from the data source until you need to retrieve or update data, so you already have a partial solution.

    Tuesday, October 23, 2007 1:09 PM
  • I guess that would be the best way to go about it.  Thats for your help you two.  I'm not sure which to mark as the answer....may i use both of your responses as answers or may I only use one?
    Tuesday, October 23, 2007 2:13 PM
  • Hi,

     

    If you found both answers helpful then it's ok to mark both of them, as long as you marked Pauls replies, and you have, simply because he gave the most help. Good luck with your project.

    Tuesday, October 23, 2007 3:54 PM