SQL Server with Access Articles, tips and tricks
As you may already know, my specialty is Microsoft Access with SQL Server, I LOVE the combination of the two! On 12/2/10 I had the chance to present at Chicago's SQL Server User Group. The topic was MS Access tips, insights and strategies with SQL Server. During the presentation I made mention of MS [...]
There are many ways to insert records and retrieve that record key value, this approach will probably be among the quickest since all the processing occurs on the server, not in Access. The technique uses ADODB recordsets to fetch records from SQL Server. CODE: Private Function CreateDatabaseRecord( [...]
We LOVE using SQL Server with Access! I frequently mention that SQL Server Express was the best thing to happen for Access, the two combined have the best 1-2 punch in the industry. I encourage you to explore using both technologies, or better yet, hire us to do the legwork. Here are some table desi [...]
Note: This is part three of a series of articles on Access Data Projects, Click to see Part 1 Click to see Part 2 Why I learned to love Microsoft Access Data Projects As a seasoned Access programmer, it seams all I do lately is Access with SQL Server, in part because of the great combination of SQL [...]
Note: This is part two of a series of articles on Access Data Projects, Click to see Part 1 Click to see Part 3 Access ADPs can provide you with the flexibility and power only a direct connection to SQL Server can provide, but a major drawback to a developer is the login box that will always show u [...]
Note: This is part one of a series of articles on Access ADP projects. Click to see Part 2 Click to see Part 3 Where and Filter Clause do not work when using DoCmd.OpenForm Statement One of the issues you will find when working with an Access Data Project, (ADP), is the lack of functionality in the [...]
If you're using a SQL Server database with an Access frontend, then this article is for you. Leveraging the power of Stored Procedures from Access has numerous benefits: your server can perform transactions that would take far longer in Access. Recently I came across the following issue when working [...]
SQL Server views are one of the best tools a Microsoft Access programmer can use to limit exposure to data and improve Access performance. Unfortunately, you can't modify data in a view unless it has an unique index, read on if that is the case for you. (You can create indexed views in SQL Server, t [...]
(Note: I loved the code I found on the original posting, but it would not work without a simple change. I'm copying the post here and highlighting my changes, talk about a time saver!) Granting Select to all tables for a login can be an extremely painful and lengthy process. Utilizing the SQL Server [...]
This is a modification to an earlier tip I wrote, whereas this code will only record delete changes, my earlier article will record delete and update changes to the data. I found code online by Brett Kaiser and I modified it for my purposes, with a mind to other Access developers I made some changes [...]
