If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing a back-end database from code
I'm making some changes to a Ms Access 97 application. In
order to support some functionality changes, I need to make a couple of changes to the structure of tables in the back end database. The first change is remaining a field, the second is changing the length of a text field. Is there anyway I can do this from code so that it can be run when the application starts? Any help will ne most welcome. Miguel. |
#2
|
|||
|
|||
Changing a back-end database from code
Renaming a field is simple:
Dim dbBackend As DAO.Database Set dbBackend = DBEngine.Workspaces(0).OpenDatabase("Fullpath to database") dbBackend.TableDefs("TableName").Fields("FieldName ").Name = "NewFieldName" Remember, though, that the linked table in your front-end, and any queries that include that field name will also have to be changed. Changing the length of a text field is more problematic. The best approach is to add a new field of the correct size, run an update query to transfer the data from the old field to the new one, delete the old field, then rename the new field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Miguel A. Velez" wrote in message ... I'm making some changes to a Ms Access 97 application. In order to support some functionality changes, I need to make a couple of changes to the structure of tables in the back end database. The first change is remaining a field, the second is changing the length of a text field. Is there anyway I can do this from code so that it can be run when the application starts? Any help will ne most welcome. Miguel. |
#3
|
|||
|
|||
Changing a back-end database from code
Miguel
Doug's provided an approach for you. I'm merely curious... why do you feel you need to change this in code? How will you prevent the same fixes from being attempted to be applied every time the front-end opens? If you have more than one installation, how will you prevent each installation from attempting the update each time it starts? -- Good luck Jeff Boyce Access MVP |
#4
|
|||
|
|||
Changing a back-end database from code
Jeff,
In the front-end I have a control table with a set of flags. I've added a new one that will be set to true by the code once the database changes are made. The code simply has to check this flag. The application is in fact a standalone application, the front-end and back-end are stored in a server and every user uses the same front-end, this makes life a lot easier. The application was split to facilitate application changes which don't affect the database so that the problem of copying data from the old to the new version is eliminated. The reason why I want to make the database changes through code is that there are multiple uses of the application. The application is a project control tool and each project manager within the department may use it on their project (s). Therefore there are multiple instances of back- end/front-end. This way I only need to distribute an updated front-end and when the project manager runs it for the first time it will update the database structure. Miguel -----Original Message----- Miguel Doug's provided an approach for you. I'm merely curious... why do you feel you need to change this in code? How will you prevent the same fixes from being attempted to be applied every time the front-end opens? If you have more than one installation, how will you prevent each installation from attempting the update each time it starts? -- Good luck Jeff Boyce Access MVP . |
#5
|
|||
|
|||
Changing a back-end database from code
Miguel
I may not be understanding your network configuration, but it sounds like you are describing keeping a single front-end on a server. If you only ever have a single user at a time, this configuration will only irritate the network folks, as it requires pushing the entire form and code "up and down the pipe" to the desktop. If, however, you have more than one simultaneous user on a single front-end/back-end pair, you risk data corruption, as each user will be pushing pieces back and forth to the server. The more common approach, and less likely to require regular rebuilds, is to deploy a copy of the front-end to each users desktop. The same "automated" work you are already considering/doing can serve as a model for how you could have the individual desktop copies check for the "latest" version on the server and download it if there's been an update. Good luck! Jeff Boyce Access MVP "Miguel A. Velez" wrote in message ... Jeff, In the front-end I have a control table with a set of flags. I've added a new one that will be set to true by the code once the database changes are made. The code simply has to check this flag. The application is in fact a standalone application, the front-end and back-end are stored in a server and every user uses the same front-end, this makes life a lot easier. The application was split to facilitate application changes which don't affect the database so that the problem of copying data from the old to the new version is eliminated. The reason why I want to make the database changes through code is that there are multiple uses of the application. The application is a project control tool and each project manager within the department may use it on their project (so). Therefore there are multiple instances of back- end/front-end. This way I only need to distribute an updated front-end and when the project manager runs it for the first time it will update the database structure. Miguel -----Original Message----- Miguel Doug's provided an approach for you. I'm merely curious... why do you feel you need to change this in code? How will you prevent the same fixes from being attempted to be applied every time the front-end opens? If you have more than one installation, how will you prevent each installation from attempting the update each time it starts? -- Good luck Jeff Boyce Access MVP . |
Thread Tools | |
Display Modes | |
|
|