Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-08-2018, 12:50 PM   #1
lekkala
Newly Registered User
 
Join Date: Jan 2018
Posts: 11
Thanks: 9
Thanked 0 Times in 0 Posts
lekkala is on a distinguished road
Access front end

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.

lekkala is offline   Reply With Quote
Old 02-08-2018, 12:54 PM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,611
Thanks: 10
Thanked 2,066 Times in 2,021 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Access front end

Quote:
All the users are accessing the shared Access database on the network with shortcuts files in each of their systems.
Update the files on the network. Their shortcuts simply point to whatever is at that location.
plog is offline   Reply With Quote
Old 02-08-2018, 01:02 PM   #3
lekkala
Newly Registered User
 
Join Date: Jan 2018
Posts: 11
Thanks: 9
Thanked 0 Times in 0 Posts
lekkala is on a distinguished road
Re: Access front end

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.

lekkala is offline   Reply With Quote
Old 02-08-2018, 01:10 PM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Access front end

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
lekkala (02-08-2018)
Old 02-08-2018, 01:17 PM   #5
lekkala
Newly Registered User
 
Join Date: Jan 2018
Posts: 11
Thanks: 9
Thanked 0 Times in 0 Posts
lekkala is on a distinguished road
Re: Access front end

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 ?
lekkala is offline   Reply With Quote
Old 02-08-2018, 01:59 PM   #6
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Access front end

Quote:
Originally Posted by lekkala View Post
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 ?
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.
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
lekkala (02-12-2018)
Old 02-08-2018, 02:57 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,953
Thanks: 38
Thanked 3,224 Times in 3,124 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access front end

Quote:
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 ?
very simple, users are sharing the front end

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-12-2018, 09:03 AM   #8
lekkala
Newly Registered User
 
Join Date: Jan 2018
Posts: 11
Thanks: 9
Thanked 0 Times in 0 Posts
lekkala is on a distinguished road
Re: Access front end

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.
lekkala is offline   Reply With Quote
Old 02-12-2018, 10:11 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,953
Thanks: 38
Thanked 3,224 Times in 3,124 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access front end

Quote:
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.
this is where your problem is. each user should have their own copy of the front end on their machine, not a shortcut
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 02-12-2018 at 10:31 AM.
CJ_London is offline   Reply With Quote
Old 02-12-2018, 10:43 AM   #10
lekkala
Newly Registered User
 
Join Date: Jan 2018
Posts: 11
Thanks: 9
Thanked 0 Times in 0 Posts
lekkala is on a distinguished road
Re: Access front end

Sorry, each user has their own copy of FE in their PC's.
So what do you think that is causing FE corruption?
lekkala is offline   Reply With Quote
Old 02-12-2018, 11:23 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,953
Thanks: 38
Thanked 3,224 Times in 3,124 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access front end

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
lekkala (02-12-2018)
Old 02-12-2018, 04:12 PM   #12
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Access front end

These two quotes are contradictory

Quote:
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.
Quote:
Sorry, each user has their own copy of FE in their PC's.
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!
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
lekkala (02-13-2018)
Old 02-13-2018, 05:11 AM   #13
lekkala
Newly Registered User
 
Join Date: Jan 2018
Posts: 11
Thanks: 9
Thanked 0 Times in 0 Posts
lekkala is on a distinguished road
Re: Access front end

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.
lekkala is offline   Reply With Quote
Old 02-13-2018, 06:03 AM   #14
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Access front end

Quote:
So what is meant by having their own copy of front end? How can I do that
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...3&postcount=12

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
lekkala (02-13-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Other users can't access their front end when I am editing a different front end cricketbird General 6 08-14-2013 12:12 PM
Access Front End vs VB.Net front end ions General 2 10-08-2009 05:55 PM
Web front-end Vs Access front-end? VegaLA SQL Server 2 04-28-2008 08:36 AM
Using VB.Net as a front end to Access? BukHix General 6 11-06-2003 02:07 PM
Running 97 Back-End with one 97 front end and a 2000 front end Swannie General 1 11-15-2002 07:14 AM




All times are GMT -8. The time now is 11:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World