I’ve been traveling the USA for the better part of two years talking about how to rev up Access with SQL Server, after hundreds of attendees, many miles traveled and more road meals than I care to remember, I finally got around to recording it. Here it is and PLEASE leave a comment below!
Spread the knowledge!
About the Author: Juan Soto
Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.
12 Comments
Comments are closed.
Hi Juan
Your presentation is excellent. I just suggested to my boss to upgrade to SQL Server from Access. The main reason why is because of security concerns with MS Access. I know the log-on information can be found in the connection string and if a hacker can copy/paste the entire backend he can decompile it within minutes using programs found on the internet. Do you have any recommendations on how SQL Server can help us with security?. Also, how about SQL Azure? is it comparable in terms of reliability, or do you prefer using the on premise version of SQL Server? I work for the Electronic Data Interchange department and one of main jobs is data transit. I wanted to use SSIS and SQL Agent. Do you have that kind of information in your blogs?
Any suggestion is absolutely appreciated.
Thank you, Carlos
Horrified Microsoft has dropped support for Access Data Projects in Access 2013. The work involved to switch backed to Linked Tables is a nightmare. Try converting hundreds of views to pass-through queries manually. What I did do was to script all the views using SQL Server Express Studio Management. I then opened the script in Notepad++ where I used Search and Replace to remove all unneeded text. I then copied the remaining data into a Excel spreadsheet. I use Excel to split the data into View Name and View SQL columns. I then imported the Excel data into a local Access table. I then used a custom function to loop through each row and create each pass-through query with connection details embedded.
Anthony –
While I’ve yet to undertake any ADP migration project, I wonder if it’s actually simpler to just revise the form opening so that you bind an ADO recordset to the form instead – that way you don’t necessarily need to rewrite the queries and continue to use mainly same code that is ADO-based.
Hi Juan, thank you very much for your terrific presentation! It was very helpful and comprehensive.
I have had a bit of experience on using Access with SQL Server (really just starting on this) and I was myself really impressed at the speed and advantages of such combination! I was really glad of having found your blog and having watched the video as it gave me a lot of pointers on how to improve my use of powerful sql server.
Thanks again for your contribution to the Access world!
Luis
Luis,
Great to hear you liked it! On another note, we are hiring Access developers in Latin America, if you know of any please send them my way.
Thanks!
Juan
Hi Juan, I can’t understand a few bits of your presentation, could you please tell me what you say at the times indicated below? I underlined what I don’t get and put between parentheses what to me is the most likely guess as to what you said.
Thanks 🙂
8:54 Sometimes these local tables get replicated ________________ on SQL Server. That’s ok, they’re usually static and (not have) that many records.
9:06 Now you’ve got to start somewhere and _____________ important data into SQL Server and the absolute best to do it(in the end)is with the Microsof Migration Assistant
9:33 so if I have any attempt character field (for tax) I’ll put in varchar(10)
9:54 run into problems with your data in (sequel) Access
12:03 We use views all the time, we _________ sequel Server do the joins
12:53 now one of the drawbacks to using ________ and views
14:40 now _____________ you need to relate those
15:13 you don’t want to go to a hundred desktops _______ DSN. (There are ways) you can do to automate it through the active directory
16:10 if that product catalog is ________, the last thing you want to do
17:55 You can also do (some of)that for a form, but mostly people like to edit data on a form so _____________do that but for a report it’s really nice
Here are my answers:
8:54 Sometimes these local tables get replicated (mumbling, ignore) on SQL Server. That’s ok, they’re usually static and (not have) that many records.
9:06 Now you’ve got to start somewhere and that’s importing data into SQL Server and the absolute best to do it(in the end)is with the Microsof Migration Assistant
9:33 so if I have any 10 character field I’ll put in varchar(10)
9:54 run into problems with your data in (sequel) Access, I was referring to setting your bit fields to a default of Zero or your going to run into issues with your SQL data in Access
12:03 We use views all the time, we rather use sequel Server to do the joins and then bring the data into Access
12:53 now one of the drawbacks to using views as a data source to a form is that SQL Server may get confused as to which table you are trying to add, edit or delete, (this is only applicable in multi-table views).
14:40 You need to create a connection between your AD security groups with your SQL Server roles by adding the security groups to as users in your SQL Server roles. That way when a new user is hired by the company a network amin can just add them to a domain group and not need to go into your SQL Server database and add them there too. Very convinient
15:13 you don’t want to go to a hundred desktops and create a DSN. (Although there are ways) you can do to automate it through the active directory
16:10 if that product catalog contains a million records, the last thing you want to do is have access do the work for you, instead send the update SQL using ADODB command and have the server execute it
17:55 You can also use a pass through query as a form’s record source but then users can’t edit the data so it’s not recommended.
Did you find the video helpful?
Yes, useful and a demonstration that Access is really flexible
Great presentation. What would be interesting is to see how your cloud apps work in real life. I tried to do something myself but the data transfer was slower then it was from the local hard drive. It might be the server speed, but I would really like to see how you do it.
I was running away from SQL until I found your blog. Now I only need a little push to take me over the edge.
Glad to hear you are not running away anymore! There are many factors that influence speed, from the design of the tables to the connection properties. Make sure you follow are tips on designing tables and limiting data to just the record you need at the moment.
Hope that helps
Juan
Hi Juan,
I have a question (and thank you for all the blog entries, they are very helpful). I’m struggling with the understanding of where the work is done. Normally I would let a bound form take over the function of inserting and updating data (unless it was in bulk) but if I’m connected to SQL Server tables should I treat them like access tables and bind them to the forms for editing purposes or should I write something complicated and execute commands to sql server to edit the data, perhaps with a unbound form?
Thanks so much.
Joanne
Joanne,
Your question is why I decided to start this blog, there was not enough information out there on this topic and even some misconceptions.
The facts are the odbc technology is robust enough to treat SQL Server tables as regular tables with some major caveats:
Load only one record per form if you can.
Perform major operations on many records on the server using ADODB, not linked tables.
Properly design tables in SQL Server to make them work with Access.
Hope that helps!