2

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.

Improve this question
1
  • This SO question may help, but 30 queries isn't that many and you probably should learn TSQL if you're going to have an MS SQL backend - just bear down and get it done is my advice.
    – LowlyDBA - John M
    Commented Aug 5, 2015 at 16:50

1 Answer 1

Reset to default
2

To me 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

Improve this answer
2
  • Thanks! I have tried using SSMA for transfering queries, but I get a lot of errors. Append and Delete queries give the error "Delete/Append query cannot be converted because it is not supported.". Other ones fail either with the error "External variable cannot be converted" or "The identifier 'IIf(BOOLEAN, UNKNOWN, NUMERIC)' was not converted." I've begun converting the queries manually and so far Select Queries have been fine, a few minor issues with me not knowing how to change certain function's formatting to T-SQL.
    – António Bezerra
    Commented Aug 5, 2015 at 22:57
  • Those are good questions to ask on this site. I would not put it all in one question though, maybe break it up with the ones for append/delete, then another one with the variables. Just show what you have tried and the version of access and SQL Server.
    – user507
    Commented Aug 6, 2015 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.