We are using Access 2010 as front end with all the forms and SQL Server for all the back end tables data. If we make design changes to any of the Access 2010 front end forms what is the best way to distribute these changes to all the users? All the users are accessing the shared Access database on the network with shortcuts files in each of their systems.
Access front end
- Thread starter lekkala
- Start date
Please correct me if I am wrong. I have my own copy of the database in my folder, so can I make changes in that copy and replace the original database on the server with this modified one?
Or should I import the form from the copy of database where I made changes.
Or should I import the form from the copy of database where I made changes.
isladogs
MVP / VIP
- Local time
- Today, 13:26
- Joined
- Jan 14, 2017
- Messages
- 19,011
Replace the database on the server with your own BUT does each user have their own copy of the FE on their computer?
If yes, what triggers the new FE to be copied to the user PC.
If no, disaster WILL happen at some point when the server FE gets corrupted
If yes, what triggers the new FE to be copied to the user PC.
If no, disaster WILL happen at some point when the server FE gets corrupted
Yes, each user have their own copy of FE in their system. Whenever there is a design change I need to update on the network server and the shortcuts point to that location.I always take backup of the database before making design changes which helped me when the FE gets corrupted.
Also can you please let me know why FE gets corrupted , like when I open it shows an error like cannot find the On Click event function it is pointing to , and when I check Alt F11 all the VBA code is lost.
What can be the reason for this ?
Also can you please let me know why FE gets corrupted , like when I open it shows an error like cannot find the On Click event function it is pointing to , and when I check Alt F11 all the VBA code is lost.
What can be the reason for this ?
isladogs
MVP / VIP
- Local time
- Today, 13:26
- Joined
- Jan 14, 2017
- Messages
- 19,011
You didn't explain how the FE gets copied to users' computers.
When you say 'the shortcuts point to' the network server, that sounds like users will run the FE from the server even if that's not the intention.
Therefore you may get multiple users doing that at the same time leading to corruption. The fact that you are getting FE corruption seems to confirm my suspicions.
If you can ever see an Access LACCDB or LDB lock file in the server location, that will be proof.
The Access FE is on the server and each user is given read/write access to the database and has a shortcut on his/her PC which refers to this server location of the database file.
Yes, there is a record locking information which can be seen when someone is using the database. There are 10 PC's where the users access their own copy of FE.
All the tables are linked tables to the back end which is SQL Server.
So every time when I need to make any design changes I do it in my own copy of FE database for the forms and reports design and any VBA code changes and after every one exists from the FE database(which can be shown without any record locking to that particular database on server), I update the FE with my copy of database on Server.
But these days I can see FE getting corrupted like loss of VBA code.
So in such cases I replace with the backup of previous day's copy. I am not sure what is causing this. Can anyone help me with this issue?
Thank you.
Yes, there is a record locking information which can be seen when someone is using the database. There are 10 PC's where the users access their own copy of FE.
All the tables are linked tables to the back end which is SQL Server.
So every time when I need to make any design changes I do it in my own copy of FE database for the forms and reports design and any VBA code changes and after every one exists from the FE database(which can be shown without any record locking to that particular database on server), I update the FE with my copy of database on Server.
But these days I can see FE getting corrupted like loss of VBA code.
So in such cases I replace with the backup of previous day's copy. I am not sure what is causing this. Can anyone help me with this issue?
Thank you.
- Local time
- Today, 13:26
- Joined
- Feb 19, 2013
- Messages
- 17,568
the type of corruption you describe is typical of multiple users sharing the front end.
Everything you have said up until your last post indicates this is what has been happening.
You are now saying each user has their own copy on their local machine. So perhaps something is happening when you distribute an updated FE to your users
My guess is that even if they have their own copy, they (or some) are still using the FE you have on the server. To confirm, suggest you remove the FE from the server.
Or if you are up for some coding, when the FE first starts it checks if its own path is a local one or on the server. If it is the server path, the FE closes.
As to other reasons for corruption, perhaps you have some code which messes up the vba (although it would also mess up your own copy as well) - so ensure you distribute a .accde file to your users, not a .accdb.
Other possibilities include users connecting to the backend via a wireless connection - although unlikely to corrupt the front end, it could corrupt the backend
Everything you have said up until your last post indicates this is what has been happening.
You are now saying each user has their own copy on their local machine. So perhaps something is happening when you distribute an updated FE to your users
My guess is that even if they have their own copy, they (or some) are still using the FE you have on the server. To confirm, suggest you remove the FE from the server.
Or if you are up for some coding, when the FE first starts it checks if its own path is a local one or on the server. If it is the server path, the FE closes.
As to other reasons for corruption, perhaps you have some code which messes up the vba (although it would also mess up your own copy as well) - so ensure you distribute a .accde file to your users, not a .accdb.
Other possibilities include users connecting to the backend via a wireless connection - although unlikely to corrupt the front end, it could corrupt the backend
isladogs
MVP / VIP
- Local time
- Today, 13:26
- Joined
- Jan 14, 2017
- Messages
- 19,011
These two quotes are contradictory
Are you sure about that?
Each user having a shortcut on their PC to the server location means they are all using the same database in the same server location. Result - corruption.
Each needs their own copy of the database itself (ACCDB file) on their own PC with a shortcut to the file on their own PC. Result - problems solved!
Are you sure about that?
Each user having a shortcut on their PC to the server location means they are all using the same database in the same server location. Result - corruption.
Each needs their own copy of the database itself (ACCDB file) on their own PC with a shortcut to the file on their own PC. Result - problems solved!
Sorry for all the confusion. Yes i did check in the user's computer and came to know each of them have a shortcut of the database which is on the shared folder on network.
This is how its being done from past many years.
So what is meant by having their own copy of front end? How can I do that
Thank you for all your answers it really helped me.
This is how its being done from past many years.
So what is meant by having their own copy of front end? How can I do that
Thank you for all your answers it really helped me.
isladogs
MVP / VIP
- Local time
- Today, 13:26
- Joined
- Jan 14, 2017
- Messages
- 19,011
The files & shortcuts need to be saved to each user's computer.
The network staff can do this very easily with a suitable script
Next you will need a method for transferring the latest front end to each computer when there is an update.
Either search for Auto FE updater or use my own version: https://www.access-programmers.co.uk/forums/showpost.php?p=1565263&postcount=12
