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

[ODSCm]

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.

 

[xenou]

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!

 

[ranman256]

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]

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]

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]

By the way in my previous message, I forgot to write that I am doing it for testing at home for multiuser.

 

[zweifuss]

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)

 

[ODSCm]

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.