Solved

Keeping my databases up to date

Posted on 2011-03-07
6
274 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
6 Comments
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 250 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 250 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

943 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

4 Experts available now in Live!

Get 1:1 Help Now