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:
- Data tier, or data layer.
- 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.
- An Interface tier, or layer.
- 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.
- A Logic tier, or layer.
- 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.