I’ve got a series of blogs going that involved linking one data source to another. So far, I’ve covered linking to Excel data from the Access database, and linking to your Access data from an Excel sheet. Today I'm going to look at linking to SQL Server data from your Access database, and, as you might have guessed, my next blog will cover linking to SQL Server data from an Excel sheet.
Whew, that's a lot of linking, right? It can be confusing, but we're tapping into one of the most powerful features of the Microsoft products: integration. Many of my clients talk about software packages as though they are going to find the perfect package that will meet all of their needs. Realistically, though, that's never going to happen. Instead, you should focus on finding software packages that meet most of your needs, and then use integration tools to bridge the gap. And that's exactly why we're talking about linking.
When we looked at linking to your Excel data from your Access database, we saw that it allows you to view the Excel data, but not edit it. With SQL, we've got better capabilities.
Setting Up the Link
In my last example, I created an Access database and linked to my vendor list, which was in Excel. Today I'm going to expand on that by linking to purchase order data that is in my SQL Server database. Let's see how to set up the link, which is very similar to setting up the Excel link.
In my Access database, I'll go to the External Data tab and click "New Data Source". From the drop-down menu, I'll choose "From Database", and then "From SQL Server":
I'll choose to link the data, rather than import:
Data Source
The next prompt that comes up is asking me where to find the source data. With Excel, this was a simple matter of browsing to the correct folder, and choosing a file.
With SQL, though, we have to set up a DSN, or Data Source Name. You may already have an appropriate DSN on your computer, so look over the list, including both the "File Data Source" and "Machine Data Source" tabs, if you have both.
Assuming you don't already have a DSN, let's walk through the steps to create one. This may seem a little overwhelming at first, but if you take in one prompt at a time, it's not hard.
I'm going to work with a machine data source, as opposed to a file data source. Both have merits, and if you're going to share this Access application with others, you should research this. But for now, I'll choose the "Machine Data Source" tab, and then click the "New" button:
I'm going to create a "user" data source, which will only be available to me. If other people log on to my computer, they won't be able to use it:
Note: You may notice that "system data source" is not available in my screen shot. That's because I'm not logged in as an administrator. That's fine for this example. I only want the DSN available for my login, so this'll work.
The next prompt asks me which driver I'm going to use. This is how Access knows which type of data I'm trying to link to. I'm going to scroll down the list and choose SQL Server Native Client 11.0 or SQL Server. Although the two drivers have some differences, they won't be significant here, so I can choose either:
Note: Don't see a SQL Server driver? You may need to install one. It's a quick and easy task, and also a free download. Search for "microsoft sql server odbc driver download" and you'll find a download link from Microsoft. Download it, install it, and reboot, if necessary.
The next screen is simply a recap, asking me to confirm the choices I've made. I'll just click "Finish":
Not Quite Finished
After clicking "Finish", you're actually not quite finished. Another screen pops up asking you to name your DSN, and to identify the SQL Server that you'd like to link to. You can use any name you like for the DSN, and the description is optional. For the "Server" prompt, you'll need to know the name of your SQL server. Note that this is not the name of your SQL database. The server itself has a name. This is similar to identifying the folder that contains your Excel file - we're identifying the server that contains your database. You may need help from your database administrator if you don't know this. Here's what mine looks like:
Next, Access wants to know what credentials to use when connecting to the SQL Server. I usually use "Windows Authentication", which means it'll use my Windows login to connect to the server:
Next, I choose my database name from the top drop-down box. I leave all other options, and click "Next":
I don't change anything on the next screen, and just click "Finish". This time we really are just about finished:
One more screen pops up for me to confirm the options I've chosen. There is a "Test Data Source" button, and it's a good idea to do that, although if you've reached this point, chances are good that it's going to work. After that, you can click "OK":
Now, you'll be back to your "Select Data Source" prompt, and your new DSN should be selected. Click OK:
Finally we have to pick the tables or views in our SQL database that we want to link. I'm going to select my purchase order table, which is called "tblPurchaseOrders", and then I'll click OK:
Voila
And, we have a linked table! Wow, that was a lot more steps that I thought. It's funny how you quick and easy it seems for me, since I do it all the time.
If I look at my database window now, I see two tables with different icons:
The one with the globe icon is called "dbo_tblPurchaseOrders". That's my SQL table. When you link a SQL table, access automatically adds on the name of the owner, which, in my case, is "dbo".
The one with the Excel icon, called "tblVendorList", is the link to the Excel worksheet that I set up in a previous blog.
When I double-click to open the dbo_tblPurchaseOrders table, I have a fully functional link to the data. That means that I can change data, add new rows, or delete records. This assumes that you have permission to do these things in the SQL Server database, which is not a given. Your database administrator might have granted you read-only permission, and Access will abide by that rule.
Also note that the rules you set up in SQL Server are enforced here. I set up a rule that every record must have a PO number. When I try to add a new record in Access without one, it throws an error:
Bringing Data Together
The really cool part, though, is that I can now use both the SQL data and the Excel data together. I'll put together a quick query that uses them both:
You can see that I've joined the two tables on the Vendor ID, which is in both. I then include some fields from the SQL table and some from the Excel table. When I run my query, it brings together data from both:
I now have at my disposal the full power of Microsoft Access, including queries, forms and reports, giving me great opportunities to provide insight into the data, regardless of its source.
I've got quite a few clients who store their data in SQL server, but use Microsoft Access as a front-end tool. This option protects your data in the safety and security of SQL server, while allowing for rapid application development in Microsoft Access. Win, win.
Next...
I hope you get some use out of this linking technique. As I mentioned, my next blog will be about linking to SQL Server data from an Excel sheet. After that, I'll move on to talking about linking Access to Access, and why you might do that. To be notified when those posts go live, subscribe using the form below.
Linking is a powerful feature of Access, Excel and SQL Server, among others. Once you establish your links, you'll have a great base of data to work with.
Need help getting started? Reach out to me to start a discussion about your project. I can help you set up links, and then brainstorm ideas for presenting your data in meaningful ways.
Kim
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #microsoftaccess #msaccess #database #dataanalysis #smallbusiness
© 2023 North Port Solutions, LLC. All right reserved.