Update MS Access Data Structure
-
I developed an application that uses an MS Access database to store test results, various parameters and other information needed for performing some the tests in question. All of that information needs to be preserved when updating (or upgrading the application). However, the nature of the upgrade may involve some changes to the underlying database structure (an additional data table, new field names, etc), that also needs to get updated. So, I do want to deploy a default database with each application installment, but want to preserve the data from the database utilized by the previously installed application. At present, I preserve this data by performing a few actions after installation that save the deployed database to a different location and point the application to that location. When I provide an upgrade, the default database is targeted for the application startup path and the first login using the upgraded something will tell the user whether the database needs upgrading. If it does, the user must import the existing data into the default database in the application path, then relocate the default database elsewhere, a multi-step process that functions reasonably well, but is less user friendly than I would like. Is there, then, a method to import a database structure without changing the existing data itself (provided there are no subtractions or editing of datatable and field names, only additions)? Thanks.
-
I developed an application that uses an MS Access database to store test results, various parameters and other information needed for performing some the tests in question. All of that information needs to be preserved when updating (or upgrading the application). However, the nature of the upgrade may involve some changes to the underlying database structure (an additional data table, new field names, etc), that also needs to get updated. So, I do want to deploy a default database with each application installment, but want to preserve the data from the database utilized by the previously installed application. At present, I preserve this data by performing a few actions after installation that save the deployed database to a different location and point the application to that location. When I provide an upgrade, the default database is targeted for the application startup path and the first login using the upgraded something will tell the user whether the database needs upgrading. If it does, the user must import the existing data into the default database in the application path, then relocate the default database elsewhere, a multi-step process that functions reasonably well, but is less user friendly than I would like. Is there, then, a method to import a database structure without changing the existing data itself (provided there are no subtractions or editing of datatable and field names, only additions)? Thanks.
polishprogrammer wrote:
Is there, then, a method to import a database structure without changing the existing data itself (provided there are no subtractions or editing of datatable and field names, only additions)? Thanks.
I will answer your question by asking you a question. If you have an existing database in MS Access ( by existing I mean tables and rows in the tables), can you use MS Access to add new tables, new columns to existing tables without losing the existing tables and rows? The answer is yes is it not? Does that answer your question?
-
polishprogrammer wrote:
Is there, then, a method to import a database structure without changing the existing data itself (provided there are no subtractions or editing of datatable and field names, only additions)? Thanks.
I will answer your question by asking you a question. If you have an existing database in MS Access ( by existing I mean tables and rows in the tables), can you use MS Access to add new tables, new columns to existing tables without losing the existing tables and rows? The answer is yes is it not? Does that answer your question?
It answers the question at face value, more or less. My question had to do more with the mechanism for updating the database from C# code. I brushed up on my SQL a bit this morning and found the ALTER TABLE ADD COLUMNNAME command which works if I always know exactly what column names to add, but I want to automate the process. Basically, I'm look to set up a mechanism that will search all the tables in the database if the software version has changed and compare the table definition for each table in the existing database with the table definition for each and every table in the database that will deploy with the newer version of the software to determine if there are any changes to the database. If so, then I need to execute the ALTER TABLE ADD COLUMNNAME command to add those new fields to the existing database. Now, I can perform this process through some brute force type of programming, where I get the schema for each table in the newer version of the DB, compare it to the scheme for each table in the existing version of the DB, and then update the data accordingly, but if there's a more efficient way to accomplish the task, I would be glad to hear it.