Questions about usage of SQL Server as Back end and MS Access as Front End

ODSCm

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Dear Friends,

I am now so close to finish my Access database (2016) which include both Front end and back end. This will be used by around 200 users. I have 13 table and 13 forms. Current size of the access is 30 MB. I think it will be 1 GB after a year. So I have a plan to use SQL Server as back end.
I am not sure at some points. For example if I use SQL Server,
- Will the general performance (speed for operations) for users be increased or will there be no changings?
- Can I not use the MS Access for 200 users choosing the edited option without problem when I split it?
- Will my VBA code be working further when I transfer the back end to SQL Server?

And the last thing, as it will be my first experience to use SQL Server together with Access, Do you have any information that you can recommend me how should I transfer my backend from access into SQL server correctly?

Thanks for your time.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
Its a tough one to answer. But to try to be specific:

1) speed should be adequate - the main thing will be network bandwidth and query design (Almost any query can run fast if its done right, or the opposite if done poorly. If you are trying to pull 100000 rows down over the network then you will be slow).
2) Not sure what you mean by "choosing the edited option"
3) VBA code in forms and reports will probably work. VBA code that works directly with data sources (querying, creating recordsets, doing CRUD operations) may need to be adapted.

If you have 200 users my recommendation would be to use a sql server backend and start the conversion immediately!
 
Solution

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,977
I have no problems with SQL server backend. no speed problems.

vb code doesnt care about the BE source. As long as the table names dont change on the Access side.
(which means no matter the sql server table name, you can name it anything on access side.)

Now, every user must have their own Access Front end. I have all users in their own network folder ( \\server\folder\users\ )
...bob\
...cindy\
etc. This is their FE app.

I keep all users in an access db table. This way I can update all Front Ends with a click. (the code cycles thru the list, copying the new version to their folder)
the shortcut is on their desktop, this way they always have the most up-to-date version on the server.
 

zweifuss

New Member
Joined
Dec 6, 2020
Messages
29
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Yes, move the backend to SQL Server. You will not regret it. SQL Server uses memory much more aggressively than Access for caching and has a better query optimizer. Data security and safety are much better too.

200 users on an Access backend is possible for read-only usage. Writing and editing data becomes problematic (e. g. page lock/table lock problems) at around 20 users. Your actual numbers may vary and depend on the database design.

The querying behavior of the available SQL Server ODBC drivers is slightly different. So a) do some testing and b) make sure everyone has the same driver and the same version.

Copy a fresh Access frontend to the individual user profile at the start with a script. That makes updating the frontend a lot easier and solves loads of problems. You should develop with a separate Access file.

Your VBA Code should not be affected. Generally, ADODB has an edge above DAO when communicating with a SQL Server.
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks for all answers.
As it will be depending on my first questions. I would like to ask here as well;

I now downloaded SQL server express edition to my personal computer. There is 3 more computer which is available at home. After I split MS Access, I will put MS Access Front End to all computers (4 computers together with mine) But I do not know how I will provide the connection between back end SQL Server which I will install to my computer and Front End - MS Access which will be located in other computers. I will be appreaciated with any direction.
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
By the way in my previous message, I forgot to write that I am doing it for testing at home for multiuser.
 

zweifuss

New Member
Joined
Dec 6, 2020
Messages
29
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ODSCm

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
You need to create the tables in a SQL Server database and then link those in the Access frontend.
There is a tool called SSMA for that: SQL Server Migration Assistant for Access (AccessToSQL) - SQL Server | Microsoft Docs
I found SSMA buggy and cumbersome, but a beginner can use it to learn how to do it manually. SSMA was created with beginners in mind.
Follow this advice to the letter: Migrate an Access database to SQL Server - Access (microsoft.com)
zweifuss thanks. I will look at all.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,510
Messages
5,636,746
Members
416,938
Latest member
sc58963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top