Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Keeping my databases up to date

Posted on 2011-03-07
6
Medium Priority
?
286 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

609 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