[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Keeping my databases up to date

Posted on 2011-03-07
6
Medium Priority
?
288 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 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

834 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