Upgrading fields and inserting data in an exisiting application
So I have an MS Access 2010 Application being used. Over the past few months we have created a new one based on the customers feedback. It still has the same look and feel as the old one, we just added and renamed fields as requested and made a bunch more reports.
So taking the data from the current application and coping it into the new is not going to be an option. Things just wouldn't line up if we did that.
What is the best way to insert new fields into the existing tables?
What is the best way to rename existing fields?
This is what I imagine happening. If this is new application to be installed then there is no need for an upgrade and the application can be used as is. But if they have version 2.0 and want to upgrade to 3.0 without losing any information then during the install they will be asked if new installation or upgrade. When the click upgrade a window pops up and asks for the path to the backend of the current 2.0 application. Once selected it will ask them if they are sure they want to upgrade and then ask if they would like a back up made of their backend. If they hit yes a backup is made and then it asks them if they want to continue to upgrade. When they hit yes a window pops up saying the following:
Now upgrading [Table Name]
and it cycle through the tables being updated.
Then it changes to
Now inserting new queries and so on with forms and reports.
I can ask all these questions separately which is what I will end up doing.
But the my main questions I have is how do I get the new application to insert fields into the old tables, How do I insert new tables into the old application and how do I rename tables and fields in the old one.
I know it can be done and I remember doing this back in Access 2.0 but I can not remember for the life of me how. I do remember I had to create the table and then each field separately. All via code.
John
So taking the data from the current application and coping it into the new is not going to be an option. Things just wouldn't line up if we did that.
What is the best way to insert new fields into the existing tables?
What is the best way to rename existing fields?
This is what I imagine happening. If this is new application to be installed then there is no need for an upgrade and the application can be used as is. But if they have version 2.0 and want to upgrade to 3.0 without losing any information then during the install they will be asked if new installation or upgrade. When the click upgrade a window pops up and asks for the path to the backend of the current 2.0 application. Once selected it will ask them if they are sure they want to upgrade and then ask if they would like a back up made of their backend. If they hit yes a backup is made and then it asks them if they want to continue to upgrade. When they hit yes a window pops up saying the following:
Now upgrading [Table Name]
and it cycle through the tables being updated.
Then it changes to
Now inserting new queries and so on with forms and reports.
I can ask all these questions separately which is what I will end up doing.
But the my main questions I have is how do I get the new application to insert fields into the old tables, How do I insert new tables into the old application and how do I rename tables and fields in the old one.
I know it can be done and I remember doing this back in Access 2.0 but I can not remember for the life of me how. I do remember I had to create the table and then each field separately. All via code.
John
ASKER
I already have an application that uses a frontend and a backend that is deployed and working.
After working with the customer over the past few months and them using it. Meaning they have thousands of records already entered. There is a need to make some modifications.
I need to be able to change some field names and insert new fields into an existing table. Normally I would just open the database and do those changes. But that is not an option this time due to customer confidentiality.
I would love for a tool that creates a temp workspace, changes the names of some fields and insert new fields. I also will need to import or create new tables in the old database. These changes in the backend would bring the whole system from version 2 to version 3.
Does that explain it better?
John
After working with the customer over the past few months and them using it. Meaning they have thousands of records already entered. There is a need to make some modifications.
I need to be able to change some field names and insert new fields into an existing table. Normally I would just open the database and do those changes. But that is not an option this time due to customer confidentiality.
I would love for a tool that creates a temp workspace, changes the names of some fields and insert new fields. I also will need to import or create new tables in the old database. These changes in the backend would bring the whole system from version 2 to version 3.
Does that explain it better?
John
OK, so the challenge is that you've delivered the DB and now need to "blindly" make updates to it.
If you have some sort of upgrade piece in place (where they can click a button to pull something in) then I guess the question is about how to get it to run those upgrades that you supply.
If not (which it sounds like) then hopefully you at least have a "DBA" who can take your instructions and implement them. I'm guessing that you want to make this as simple as possible.
So are you planning to repeat this many more times? If so, it may make sense to work toward the first effort (an upgrade button)
Anyway, there are several solutions. You could create a new DB with empty tables, then have a macro to pull everything from the existing DB into the new one. You could include a form into an empty DB and have the DBA go through an import process to pull that piece into the existing DB. You could also have a macro that runs from this new DB (or even from another Office product like Excel or Word) and opens the DB and builds all of the pieces as needed. And I guess you could create a (VB/C#).NET app to handle the upgrade also. This would give you better tools to do the job, but then you'd have to learn new pieces to handle something that may not justify the effort.
If it were me, I would probably push for the first option. Create everything you need and deliver it. When the app first loads, have it ask for where the existing DB is located and migrate all the data into the new DB. When done, maybe rename the old DB so it's no longer usable without renaming it back (so it becomes archived but not lost.) (You could also have a batch script handle that and maybe rename the old one to something else then rename new one to the old name then launch it so the migration begins.) For this to work, you'll need to have all of your pieces in place and some translation scripts to migrate things to the new names (again queries might be a better way to go with that rather than rename fields.) The nice thing about this is that you can fully test it before delivering to minimize the risk of failure.
Let me know if any of these make sense and I'll try to get you going with the key parts.
If you have some sort of upgrade piece in place (where they can click a button to pull something in) then I guess the question is about how to get it to run those upgrades that you supply.
If not (which it sounds like) then hopefully you at least have a "DBA" who can take your instructions and implement them. I'm guessing that you want to make this as simple as possible.
So are you planning to repeat this many more times? If so, it may make sense to work toward the first effort (an upgrade button)
Anyway, there are several solutions. You could create a new DB with empty tables, then have a macro to pull everything from the existing DB into the new one. You could include a form into an empty DB and have the DBA go through an import process to pull that piece into the existing DB. You could also have a macro that runs from this new DB (or even from another Office product like Excel or Word) and opens the DB and builds all of the pieces as needed. And I guess you could create a (VB/C#).NET app to handle the upgrade also. This would give you better tools to do the job, but then you'd have to learn new pieces to handle something that may not justify the effort.
If it were me, I would probably push for the first option. Create everything you need and deliver it. When the app first loads, have it ask for where the existing DB is located and migrate all the data into the new DB. When done, maybe rename the old DB so it's no longer usable without renaming it back (so it becomes archived but not lost.) (You could also have a batch script handle that and maybe rename the old one to something else then rename new one to the old name then launch it so the migration begins.) For this to work, you'll need to have all of your pieces in place and some translation scripts to migrate things to the new names (again queries might be a better way to go with that rather than rename fields.) The nice thing about this is that you can fully test it before delivering to minimize the risk of failure.
Let me know if any of these make sense and I'll try to get you going with the key parts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could also use this utility to rename fields (or other objects) and have the change update everywhere in the database the old name was used.
http://moshannon.com/speedferret.html
http://moshannon.com/speedferret.html
I have attached a copy of one of the upgrades to an application I wrote. It will give you an idea of how to proceed. Some things are very tricky to do "blind" and so my client and I made an agreement that I wouldn't make certain types of changes.
1. No table or column deletes. We didn't want to ever remove columns/tables that contained customer data.
2. No name changes.
The bulk of the work is done with DDL queries. An error log is kept. Over the course of the past 6 years, we have rolled out schema changes 6 times as we added new features.
DEA-Ver37toVer38.zip
1. No table or column deletes. We didn't want to ever remove columns/tables that contained customer data.
2. No name changes.
The bulk of the work is done with DDL queries. An error log is kept. Over the course of the past 6 years, we have rolled out schema changes 6 times as we added new features.
DEA-Ver37toVer38.zip
ASKER
This is all great information and I do appreciate it.
This is my first real application used in the commercial sector. The customer is very happy with it, Making the changes to the front end has been easy. I can just replace the front end they are currently using with the new one.
These are the changes that need to be made.
Two new tables will be added.
One table will be deleted
One table will have six field name changes.
I tried to explain the field name changes that they can be changed on the form and reports and have no major impact on the application. They are very insistent that the fields name changes in the table as well.
The good thing about all of this is I love learning new things. Creating a tool to import or upgrade the table(s) is very interesting and could be beneficial later on in another application. The customer is very relaxed. They are not asking for this to happen right away. They said they would much rather wait and have it done right then to rush it and have to re-do it. Plus we are talking about 20K records they currently have in the database. Which is nothing as far as a database goes but this is their livelihood. And the best part is this is only for the customer who is using it. It has no effect on their customers. None of this changes how they will do business just how they will see the data. Their customers will never even know a change happened.
I will be working on it all weekend so I will update this thread as I run into new situations.
Thanks
John
This is my first real application used in the commercial sector. The customer is very happy with it, Making the changes to the front end has been easy. I can just replace the front end they are currently using with the new one.
These are the changes that need to be made.
Two new tables will be added.
One table will be deleted
One table will have six field name changes.
I tried to explain the field name changes that they can be changed on the form and reports and have no major impact on the application. They are very insistent that the fields name changes in the table as well.
The good thing about all of this is I love learning new things. Creating a tool to import or upgrade the table(s) is very interesting and could be beneficial later on in another application. The customer is very relaxed. They are not asking for this to happen right away. They said they would much rather wait and have it done right then to rush it and have to re-do it. Plus we are talking about 20K records they currently have in the database. Which is nothing as far as a database goes but this is their livelihood. And the best part is this is only for the customer who is using it. It has no effect on their customers. None of this changes how they will do business just how they will see the data. Their customers will never even know a change happened.
I will be working on it all weekend so I will update this thread as I run into new situations.
Thanks
John
Commentary on "They are very insistent that the fields name changes in the table as well"
This is like asking you to change the name on your drivers license from Matthew to Matt because they're tired of seeing the extra letters there.
The names in the table are irrelevant and could simply be Field1, Field2, Field3.
The proper way to address this (as I previously mentioned) is to create a query with "alias" names that give them whatever names they want, and encourage them to simply use the query with the desired names so that it does not break anything else in the application.
I know that may not help since some people simply don't understand the right way to do things, but I felt it was worth mentioning.
Just be prepared to find bits and pieces that you forgot to rename (especially if you have VBA references to the old field name) that will cause things to start breaking until you locate them and update them with the new names. This issue will never occur by building a query since old and new still work, and you can slowly start migrating all the old things to the new way as you find them without any impact.
This is like asking you to change the name on your drivers license from Matthew to Matt because they're tired of seeing the extra letters there.
The names in the table are irrelevant and could simply be Field1, Field2, Field3.
The proper way to address this (as I previously mentioned) is to create a query with "alias" names that give them whatever names they want, and encourage them to simply use the query with the desired names so that it does not break anything else in the application.
I know that may not help since some people simply don't understand the right way to do things, but I felt it was worth mentioning.
Just be prepared to find bits and pieces that you forgot to rename (especially if you have VBA references to the old field name) that will cause things to start breaking until you locate them and update them with the new names. This issue will never occur by building a query since old and new still work, and you can slowly start migrating all the old things to the new way as you find them without any impact.
rspahitz mentioned some of the gotchas with changing column names. The mechanism to make the change to the BE is easy enough, use a DDL query to alter the table. The problem is ensuring that the change is properly implemented in the FE. Think of how red your (actually the client since they are who is selling the product) face will be when their client runs a report you forgot to test and finds that it is sorting on the old field name. I also hate to say this but when you create your names originally, be conscientious about consistency and avoiding reserved words. You should not really have to ever go back and change names. If the business changes and that changes the meaning of the original name, live with it. In this case, I would probably add a description to the field on the table that explains what happened.
ASKER
Took longer than expected for me to get back but great advice and I was able to do everything I needed
John
John
I'm not sure of the problem here. Simply go to the table design and add the field. It will have no impact on existing data.
Or did you deploy the database as independent components that are not talking to a shared DB on the server?
>What is the best way to rename existing fields?
For this, your best bet is to build a query that includes everything from the previous table and includes "calculated" fields with the new names that simply pass through the fields with the old names.
e.g.
SELECT OldName As NewName From MyTable
In query view, it will look something like this in the Field row:
NewName: OldName