?
Solved

Keeping my databases up to date

Posted on 2011-03-07
6
Medium Priority
?
282 Views
Last Modified: 2012-05-11
Hi All,

I have a small VB.net 2010 Winforms application that uses a small access MDB file to keep some settings for the application (in a single table). I have maintained a VB6 application with MDB data for many years so this was my quickest route to get this working but I am now keen to use up-to-date technologies.

Now that the first version is installed on a few sites, I need to expand the functionality and add more tables and probably start to normalise as I go. Ideally, I would like to create my new database structure in my development environment and as I roll out a new version to the clients, update their local databases too. I really want to avoid hand coding and maintaining scripts as I am positive this will introduce errors.

So, my question is: What is the preferred method for doing this with .net so that I can reliably update my clients database and maintain a version of the database that the client currently has in place. Without having to hand code all of the modifications each time.

As this is only on a small number of clients at present, if there is a more reliable or robust method available using a different database like SQLExpress, please let me know as I could entertain that approach. But initially it must remain as MDB.

Please be as detailed as possible as this is my first adventure into VB.net and will no doubt be lacking in what may be classed as general knowledge to you guys.

Thanks.



0
Comment
Question by:bandito22
[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
6 Comments
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 750 total points
ID: 35056610
I don't think this is a vb.net issue so much as a distributed database issue.

In my opionion,  if you have local independent databases for each client and you are going to make schema changes, then you will inevitably have to code some upgrade scripts.

If everyone was synchronising to a central database, you could use features of the RDBMS to help with this.   However,  personally I would expect to hand-code some upgrade script at some point particuarly if you are planning to normalise data and introduce new tables.   This implies a lot of data transformation in order to fit into the new schema.    I don't think an automated management tool would handle this reliably enough for my tastes.

Not a solution I'm afraid but my my opinion nonetheless.


0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 750 total points
ID: 35056777
I would have to agree with previous comment on this one. Scripting your schema changes is the simplest way to perform upgrades to your schema, unless you have the luxury of being able to drop and re-create the database.

Scripting all of your changes also gives you the added bonus of being able to easily re-create the schema for a specific version of your app at any point, and will also help if you choose to use automated testing.
0
 

Author Comment

by:bandito22
ID: 35057121
Appreciate your comments.

I am happy to hand code the data population as I realise this could not really be automated. And I like the point made about being able to create the correct MDB that corresponds to the version of the app.

My remaining concern is the creation of the scripts for creating the MDB as I am only familiar with MSAccess GUI for creating and manipulating tables. Is there something I can use that can be pointed to my new MDB structure and create the necessary script for re-creating that MDB file with all the necessary definitions, keys etc.


Thanks.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:VTKegan
ID: 35060705
Here is a link to a whole host of functions that can be used to create modify edit delete tables using DAO.

http://allenbrowne.com/func-DAO.html

0
 

Author Comment

by:bandito22
ID: 35062130
Appreciate the additional information.

My approach is evolving as I read the various replies.

I would prefer to keep using the Access GUI for doing all of my initial creation tasks as it is really quick.

Then, once I am happy with the new structure, I was hoping to run something that will create the necessary scripts (the part I don't want to try to do myself) to recreate the whole MDB from scratch.

0
 

Author Closing Comment

by:bandito22
ID: 35446931
Good Advice
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

765 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