how create multi-user database

[eugzl]

Hi All.
I would like to create a multi-user DB. If I create a database in SQL Server and then link it to an Access database, will I get a multi-user database? Or do I need to split the Access database anyway?
In case if Access link to SQL Server what specific changes need to do in VBA code?
Thanks

 

[theDBguy]

Yes, no, and yes. Are all your users connected to the same LAN to access the SQL Server? Or, do you have remote users?

 

[eugzl]

Yes, no, and yes. Are all your users connected to the same LAN to access the SQL Server? Or, do you have remote users?

Hi theDBguy. Thanks for reply.
All users connected to the same network and they connected to the network remotely. Will an Access DB linked to SQL Server be faster?
Thanks

 

[GPGeorge]

Hi All.
I would like to create a multi-user DB. If I create a database in SQL Server and then link it to an Access database, will I get a multi-user database? Or do I need to split the Access database anyway?
In case if Access link to SQL Server what specific changes need to do in VBA code?
Thanks

This is a great question.

There are three tiers to any database application:

1. Data tier, or data layer.
   1. This is the part of the application where the data is stored. Technically, one might think of this as "the database". The physical objects holding the data are called tables. Access accdbs contain tables. SQL Server, Oracle, and so on, all are databases with physical tables in them.
2. An Interface tier, or layer.
   1. This part of the application consists of the objects through which users are able to interact with the data in the tables. Forms serve as data management tools, allowing users to add, modify and delete tables. Reports present a static view of data. Access accdbs contain these interface objects along with the tables. Other databases, such as SQL Server, do NOT have interface objects. You must create those interfaces with a tool specifically designed for that purpose. Access, for example, can provide that interface to SQL Server tables.
3. A Logic tier, or layer.
   1. This part of the application consists of code that automates much of the interaction between users, interface and data. Access has both VBA and Macros to perform that automation. SQL Server and other databases do provide powerful scripting tools, such as stored procedures, that can handle the automation of data, although, again, they can't manage the interface to it.

For that reason, Access is different from almost all other development tools. It includes tools for all three tiers of the application. In addition, Access can link to data in a huge variety of data sources, including SQL Server or SQL Azure, Oracle, MySQL, SharePoint Lists and Dataverse tables.

So the answer to your question is that once you migrate the data from the Access tables to the server-based database (SQL Server), you have "split" the database. In splitting the accdb into a Front End and Back End, the only difference is that that tables remain in an accdb, which doesn't have interface or logic in it.

 

[GPGeorge]

Hi theDBguy. Thanks for reply.
All users connected to the same network and they connected to the network remotely. Will an Access DB linked to SQL Server be faster?
Thanks

Probably not. It is a surprising revelation to many people when they migrate data from Accdb tables to SQL Server tables that the exact opposite often occurs. It slows down.

There are good reasons for that, but they all group under the fact that retrieving records from and writing records to the SQL Server requires a lot of traffic back and forth between the two and the way you manage that traffic has a significant impact on speed. Your remote users, in particular, are going to experience that slowdown.

A lot has been written here and elsewhere on the subject of performance degradation when migrating data from Access tables to SQL Server tables. Rather than rehash it here, I would suggest you do a search for such posts and discussions, looking for specific suggestions on improving that performance.

 

[Steve R.]

I would like to create a multi-user DB.

If you are already well down the "design road" with an an MS Access/SQL Server configuration; keep going.
However, if you are starting from scratch consider using an opensource database such as MariaDB where the user connects to the database through a browser such as Firefox. (Assuming you are in a position to select the database you wish to use.)

If you are already familiar with MS Access, developing the multi-user environment with SQL Server may be the more efficient approach. However, if you have time and wish to explore another method or creating a multi-user DB; look into using an opensource database/browser configuration.

 

[The_Doc_Man]

Will an Access DB linked to SQL Server be faster?

Depends. Performance will depend more on the nature of the database and the way its queries are structured and stored. It is POSSIBLE that your SQL action will be faster... but we've seen complaints about it being slower. There is a way to write queries so that the FE and BE don't have to continually interact. If you get that right, network transfers become significantly less and you enjoy a little extra speed. If you blow it, the FE/BE interactions make everything slower. It's all about how you offload the network.

Another factor is the disk bus on the SQL Server vs. the disk bus on your desktop or laptop. If the two machines are both relatively modern, they will be running in the 2.5 to 3.0 GHz range. It might break down to determining which machine has the fastest disk as to deciding which system will be faster. Usually the system with a Solid State Disk wins that race.