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.