I'm sure this has been asked before. I will layout as much as possible to see if It's worth moving my Back End (BE) Access database to one of the SQL choices.
We have 2 databases. Both in Access 2010. The BE contains all the tables and queries. Update, Delete, Add and simple search queries that return data. The Front End (FE) contains only one table with one row (for version control).
The total size of the BE is less than 1 gig. This is a conservative size according to what we currently have in the database and how large it will grow. It will eventually include archiving of tables > 3 years old.
There are 44 tables (currently no temp tables) and about 60 queries. A lot of lookup/Validation tables (about 20) such as ClientMaster, TransationType etc. We will have up to no more than 10 (usually a max of 5 at a time) accessing the system.
The FE houses all the forms, does the call to the queries on the BE which access, deletes, appends etc to the BE data.
One BE database. and each user will get their own copy of the FE. Currently each user while accessing the same tables to the BE will very unlikely be accessing the same rows. They are assigned specific Unique keys for the data they will be accessing. It is true there will be bulk retrieval of entire tables. The largest table will be no more than 3000 row for these bulk loads. Usually into list boxes, comboboxes, datasheets, etc. The majority of the vba code in the FE will run against the specific Keys and related data for each user.
We have seen even with one user the process can run slow. The BE sits on a server locally(same city) as 80% of the users accessing it. The 20% of the users are currently offsite (in Arizona) still will access the local server.
For the BE I'm not sure of the network share server, model, storage space, cpu, memory etc. We found moving the FE to the local pc (Windows 7, 8 gig memory, 250 gig SATA drives (no SSD yet) improve the process a little. Maybe 10% depending on the user load, query running etc. That issue is that the FE will not be backed up. Even though it really does not house any data it could lockup and have a user have to start the process all over again.
This is a new process written to replace auditing of contracts from a manual process to a automated process. It is very intense VBA code on the FE. Mostly exception and very little table driven. So this is not a existing or finished product. It is being written as we speak.
Did I give enough information to ask the question of keeping the BE in Access. Or what are my options (# of users total and simultaneously, total storage size, total number of tables and the work needed to move the tables and queries to one of the SQL choices.
Obviously cost will be a factor in the decision. Mainly the cost of the BE solution and if there is a lot of work to do the actual move.
As far as I know it's whatever current version of SQLServer available (or we may be able to piggy back on a SQLServer license currently installed. That is highly unlikely. Don't ask why but believe me it is). The limits for SQLServer Express which is free and MySQL which I believe is also free. I know a move away from Access with a SQL option in the very least will involve ODBC configuration on the FE for each user or will the ODBC only be necessary on the BE to the SQL tables? And are these to be all Linked tables?
I realize without looking at the actual code and data it's hard to give a definite answer but if anyone has done similar moves I'd appreciate hearing what you had to go thru. One thing I like about the move is to user SQL pass-thru so I will be able to write true SQL and eliminate some of the issues I have with having to do a process in multiple queries.
Thanks for your time!