Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions.
I had a database built in Access entirely, and had to make it so that multiple users could use it at the same time from different locations. Below are the things I have tried:
- I split it and used linked tables to multiple front-ends. This would have worked fine on a local share, however, this had to work for users outside of the network. I tried a VPN which ended up being way too slow using it with a regular Windows share.
- After some research I found out that you could link SQL Server tables to Access, and that would have better performance than using a Windows share. Which it was much faster, especially when you access the tables from outside the network via VPN. There were some things that still did not meet performance needs though.
- I then found pass-through queries, that are run on the server instead of the client, and don’t require the whole table to be requested. So then I thought “Perfect! I’ll convert the queries to pass-through queries and unicorns will run free again on the Earth” (not really, but I thought simply changing the query to a pass-through one would work), nope. Turns out you also need to convert the query from the Access SQL language to T-SQL. I have no previous experience with either language.
Is it worth the effort of converting 30 queries from Access to T-SQL? I have looked around online and most things I find I don’t quite understand to the point where I know what to change. Any help at all would be extremely appreciated.
30 queries does not sound like to much work in order to obtain the level of performance increase you will see having SQL Server do the work instead of Access. Especially if the data is going to expand to more users and is a more mission critical type thing.
Something you might consider is running your Access database through the SQL Server Migration Assistant for Access tool. This tool will actually move your queries to SQL Server. You can find more information about the tool here: Access to SQL Server Migration: How To Use SSMA