Updating Back-end Databases

[lution]

I have a database that I've inherited that requires some changes to both the UI and back-end databases. Each user has their own UI and BE database so it's not as simple as going to the server and manually changing the tables.

Originally the designer appended (J) to the customers last name if they were a juvenile (unfortunately I can't just compare the creation date to the birthdate to see if they are juvenile). On my test server I've added a new property to the customer table and wrote an update statement to find all the last names with (J) in them and then set the new property to true. I figure I'll have to write a alter table statement to add the new property to the other databases.

What would the best way be to get all the existing databases updated to reflect the change without leaving the query or alter table sitting in Access for the user to run again?

 

[KeithG]

Why does each user have there own BE? I don't think it really makes much sense to break your DB into a FE and BE if each user has there own BE. I would link all the users to one BE or Just have the users use there own copy of the db not broken into a BE and FE.

 

[RuralGuy]

Keith,
I think I would separate the code from the data even on a single user system. It makes updating the code without touching the BackEnd data trivial.

 

[RuralGuy]

I do have to question, as you have, why everyone has a separate BackEnd; if that is what the OP was describing.

 

[lution]

I split the UI from the data so I could roll out additional reports/forms/ and general UI fixes to the users without having to mess up their data. In this case, I have an actual change I need to do the to data but I don't want to leave the query statement behind when its done.

 

[RuralGuy]

I think both Keith and I understand that. Our question had to do with why everyone was not using the same BackEnd? I guess there was no requirement to share the data.

 

[lution]

correct, and each user is at a different site with no need to share data between sites.

 

[RuralGuy]

If you have a form that loads at db startup, then create another form that does all of the alteration work for you with dynamic queries. Then when everything is completed, delete the form. The 1st form can have an If Exists(Forms!AlterForm) Then run it so it will only run once.