Multi User Access Front end to SQL best practice?

[Access_guy49]

I am new to the SQL databases and have been working on getting a few of our database applications converted to SQL

Currently I have a database in which i upsized to SQL from Access. The backend data went over fine, and the front end linked up nicely using the linked manager.

However, the front end is currently on our shared file server so that other users can access the front end. (this worked great when the backend was also Access)

Now, when users open the new frontend linked to SQL, the data is not displayed and the linked tables don't seem to be working.

I'm wondering if this has something to do with the File Data Source being created on my machine. ???

If that is the case, would i need to install the file datasource somehow on other machines? What is the best way to distribute a front end within our network when it is linked to an SQL Server??

I have been told in the past that it's best to have a local copy of the front end on every computer that uses it... would there be any issues doing this when it's an SQL backend??

Any help would be greatly appreciated, I think my biggest problem is that i don't know where to start and the sites i have found that do talk about it are pretty advanced for my limited understanding.

 

[boblarson]

The frontend should be a copy on EACH user's machine - even when it was in Access. If you were running the same frontend for everyone from the server then it is a miracle that it worked okay for you and didn't corrupt or anything.

See this for more about split databases and why it is important to have a separate frontend file for each user.

 

[boblarson]

And as for a DSN, you can either use a file dsn from a server or you can install a dsn to each user's computer or you can look into using a DSNLess connection (do a Google search for that as there is code out there by Access MVP Doug Steele and others for that).

 

[MSAccessRookie]

I am new to the SQL databases and have been working on getting a few of our database applications converted to SQL

Currently I have a database in which i upsized to SQL from Access. The backend data went over fine, and the front end linked up nicely using the linked manager.

However, the front end is currently on our shared file server so that other users can access the front end. (this worked great when the backend was also Access)

Now, when users open the new frontend linked to SQL, the data is not displayed and the linked tables don't seem to be working.

I'm wondering if this has something to do with the File Data Source being created on my machine. ???

If that is the case, would i need to install the file datasource somehow on other machines? What is the best way to distribute a front end within our network when it is linked to an SQL Server??

I have been told in the past that it's best to have a local copy of the front end on every computer that uses it... would there be any issues doing this when it's an SQL backend??

Any help would be greatly appreciated, I think my biggest problem is that i don't know where to start and the sites i have found that do talk about it are pretty advanced for my limited understanding.

I suspect that he fact that your Shared Front End "worked great when the backend was also Access" may have been a more a matter of Good Forutune and Coincidence, as opposed to anything else.

It is held by almost all of the experts that I work with that there is rarely a reason not to split an Access Database that is being used by more than a single user, and that when the Database is split, that the Front Ends should be located on the User Workstations, while the Back End should be located in a Common Area (most often on a network) that is available for all users to share.

The reason that you are having problems with SQL Server may be due to the face that Access tends to be more forgiving regarding Resource contention that SQL Server is, and as a result will nor return Record Locked errors as often.

Since your Database is already Split, give each user their own copy, and see if this makes any difference. As long as the Front End is linked to the Proper SQL Server Database, and each user is authorized to use that Database, there should be no errors in doing this.

NOTE: I assumed that since you already had people using the Shared Front End with the SQL Server before you began to have the problems that you described, so I did not take time to address the issue of Database Accessibility. If they are not already doing so, then Bob Larson's advice on how to allow them to do so becomes very important

 

[Access_guy49]

Thanks guys! it was a combination, I needed to store the DSN file on the server and then link to it from there (intead of being on my local development machine)
And then i also needed to add the user group to the SQL login so that they would be granted access to their data.

Thanks for the help.

As for my luck, i think for the most part it has been working fine with a shared front end because no two people ever seem to be using the database at any given time. It's usually only one person accessing data or adding records, and most databases i create here are only ever used by one or two people.
The only database that has 2 users that access it at the same time, is the planning database, and that one i created with local front ends. But i'll be sure to keep doing that for all future databases.

 

[snow-raven]

The frontend should be a copy on EACH user's machine - even when it was in Access. If you were running the same frontend for everyone from the server then it is a miracle that it worked okay for you and didn't corrupt or anything.

See this for more about split databases and why it is important to have a separate frontend file for each user.

I know this thread is old, but I found it useful and tracked down another location of Albert Kallal's article:

http://www.kallal.ca/Articles/split/index.htm