Listbox.requery

[Pat Hartman]

I want to create a new table in the BE that does not exist already. And in the other cases I want to add some fields to existing tables.

This is logic that does not belong in a relational database application. I have databases that do this but they are external to the actual FE and they are created specifically to convert versionA of the BE to versionB. If we have more changes, the new app converts versionB to versionC. So, there are valid reasons for creating/modifying tables. They do not include processes that the user runs.

This is even more terrifying if this is a multi-user application since you will get exclusive locks on the BE when a person modifies structure in the BE with your code.

 

[The_Doc_Man]

Good point, Pat. A schema change has to use exclusive locks. I was so focused on the mechanics that I didn't think about locking.

 

[Freshman]

Wow - that method would have been too labour intensive for me being the sole dev of a commercial app used by clients all accross the country for the last 20 years. So I had to do things to save me time and not force me to travel or spend time with each user. So the FE at each user would check for the lastest version online upon opening. If there is a new version (which I rolled out online as a exe download setup file using setup2go software) the latest version would install on the particular user's pc. Since the BE path was diffrent than mine (and for most users since some resided on the LAN for shared data use), the BE path was stored and the a routine would run (as a once-off after an update) to make any changes to the BE tables if required (not always) including relinking to the spesific BE path of each user. Everything works (still does) seemlessly without any user intervention or need for me to take stuff offline or take over any user's pc or make physical changes to each BE at multiple users. Like I said I created the Human Resources app many years ago and it has been running for the last 20 years and never fell over - thank goodness for that. The only times when I had to re-install anything was when a user got a new pc. But a few years ago I moved the whole app to web (over 100 tables, far more forms and queries and lots of reports). I was forced to learn php and html at my kitchen table by myself in 6 months as I was converting it. It was madness but now I have the full app at all clients running webbased of a mySQL BE. Much better as changes to the BE or FE is now a walk in the park with no need to run updates as you know. Every now and then I get asked to do an access project (small) but I have forgotton a lot hence my silly question in my OP here. Still I like access a lot and think it is very powerfull but nowadays I prefer web design since it is much easier to maintain and changes are more 'instance'.

That is a mounthful for your answer but lastly I can say that the HR access app developed and grew over many years as I kept on adding extra feaures which as you can imagine required extra tables and also extra fields to cater for additional feaures and modules which was not planned in the beginning since it started as a simple add but grew into a flagship app with multiple modules and fucntionality. So - yes there would have been the need to add extra fields to exsting BE tables which had nothing to do with breaking the 'rules' of normalization. Extra functions simply required data to be captured by the user which was not catered for before.

Regards

 

[Pat Hartman]

So I had to do things to save me time and not force me to travel or spend time with each user.

It wasn't too time consuming for me and I didn't need to travel to the customer site to update their BE's. I sent them an Access database or SQL Script depending on whether their BE was ACE or SQL server with instructions to make sure the users are out of the database. The Access conversion db made a backup of the current db and zipped the backup. Then the code renamed the BE to ensure that if a new user logged on, the log in would fail. Then it ran the updates against the renamed BE. It compacted the BE, zipped it again and then renamed the BE back to the original name. The replacement FE was installed in the master directory and the users were notified that they were back in business.

Updating the design of a BE while users might be actively engaged is very poor practice. If you can't do the update during off hours, then you at least have to ensure that no one is actively using the database.

All I can say is that you are one lucky person. Allowing BE updates in an environment where users might be working is dangerous at best and bordering on suicidal.

 

[Freshman]

I agree with keeping the User off the database while updating so I made provision for checking for the locking file and a few others things and then promting the User to exit so the update can happen. The above was but a summary of the process but the the "proof is in the pudding" 20 years no problem so if I had to explain all it seems like my process and precuaion was good enough
Nowadays I focus on converting Access to Web and I feel it is a better option while I'm still a die-hard Access fan and think that Access Developers are among the most inovative people as they are forced to think out of the box and come up with solutions due to limitations and they alwasy do
Enjoy the week.