[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Keeping my databases up to date

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
bandito22
Asked:
bandito22
2 Solutions
 
Paul_Harris_FusionCommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
bandito22Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
VTKeganCommented:
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
 
bandito22Author Commented:
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
 
bandito22Author Commented:
Good Advice
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now