SQL Server with Access Articles, tips and tricks
[Update] Post has been updated to correct the maximum size of the database to 10 Gigs When should you use SQL Server with Access? (Hint: Almost always) Look, I’ve heard the phrase “When all you have is a hammer everything looks like a nail” and given that our specialty is Access with SQL Server at I [...]
Avoid This Common Error When Executing Stored Procedure in MS Access We love running stored procedures from our VBA code, but there is one issue you need to watch out for: Executing a procedure that affects data already loaded on your form. If you're not careful you will get the following [...]
Search Microsoft SQL Server Database for Stored Data Have you ever had to decipher a database and identify which column has the data you need? I recently had to work with extracting data from a SQL database to use in Microsoft Access and Power BI, unfortunately the naming structure of the dat [...]
Detangling the New Microsoft SQL Server ODBC and OLEDB Drivers Some of you may already know that Microsoft backtracked on their planned deprecation of OLEDB and provided a new OLEDB driver. However, it can be a head-scratcher to figure what you should be using. When we were using SQL Server Native C [...]
Join Me For a Microsoft Access with SQL Server Academy Session We kick off the new season of Access with SQL Server group meetings this year with three months of training: Introduction, Intermediate and Advanced topics on how to optimize Access with SQL Server. Part 1: Introduction On Tuesday Septem [...]
Designing a Microsoft T-SQL Trigger On occasions when building a project involving an Access front-end and a SQL Server backend, we've run into this question. Should we use a trigger for something? Designing a SQL Server trigger for Access application may be a solution but only after careful conside [...]
Drag and Drop In MS Access and Conditional Sorting Drag and Drop In MS Access Doug was on a roll in January of 2004 when he wrote his article on drag and drop in MS Access. He tested it now using Access 2016 and it still works. This article shows you how to add drag-and-drop to your Access applicati [...]
Unanswered Questions About MS SQL Server Security and Access Control For many developers, database security and Access control is just something that gets in the way of development work. However, several recent security breaches have had devastating consequences and have caused a change in attitude [...]
Note: I'll be presenting on this topic at the Access with SQL Server group online. Please join me on September 13th at 6:30 PM CST, join the group to get an email with all of the meeting details, it’s free!
Insert CreatedBy and CreatedOn in Any Missing Data Tables in SQL When we design Access with SQL Server solutions we almost always add CreatedBy and CreatedOn to all of our tables, allowing our clients to see who created records in the Access frontend. Script to add CreatedBy and CreatedOn Automatica [...]
Use Microsoft SQL Server Native Client 11.0 for the Best Performance [Update] There is a newer version of this post here. In our experience in using linked tables with Access, we get the best response using Native Client 11.0 with DSN-less tables. You could use ODBC 11 for SQL Server but then you wo [...]
