I would like to move my Access backend to SQL Server. (developer edition). I have tried the SSMA 7.5 ver, but i need office 64 bit for that. Then I tried ver 6.0, but that does not give the option for connecting to Server 2016, only up to 2014. So, if I can't use the SSMA, what would be the next best way to move the back end to SQL Server?
Access 32 bit can be used to link tables to 64bit SQL Server. Tables are linked typically using ODBC, an API developed to communicate to databases regardless of the database or OS. Developer is free for development now but use Express for a backend for Access.
By linked, do you mean similar to an Access split DB? Or is it somehow through the use of recordsets?
Yes, very similar to splitting an Access database, only the back end in on SQL Server,
I got the tables to link, renamed the links, and was able to get some of the forms working.
My questions:
Should I use views? All my queries are done in Access - some are created queries, some are done with SQL in VBA.
Sometimes I get an ODBC call failed error - not sure why - I am using a file DSN.
Sometimes my search queries (VBA/SQL) don't work right - should I convert the VBA SQL to a different standard?
For example, a text box that searches multiple fields as you type - calls a module function, passes the form object byref, so the text box text can be used in the SQL query.
Should I be using stored procedures, triggers?
I would also like to keep track of who logs in, and store datetime, username, ip address - is there an easy/built in way to do this?
Thanks for your help.
I would change to use views instead of queries and procedures instead of parameterizes queries.