Updating excel data with dsn less connection vba

Rated 3.85/5 based on 673 customer reviews

The knowledge articles contains solutions and guides.

Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver.

This was in preparation for using Microsoft Access as a front-end tool for a SQL Server 2005 database.

Though Access can be considered a sub-par application by the relational database elitists, it can be a very capable alternative for querying data from SQL Server without the learning curve associated with Visual Studio.

When creating the DSN for this process, you configured it to use either SQL Server or Windows (Trusted) security.

You will be prompted again at this point when attempting to make use of the DSN.

Once selecting the 'OK' button on the previous screen you will be prompted to select a 'Unique Record Identifier' for each table.

updating excel data with dsn less connection vba-62

updating excel data with dsn less connection vba-7

updating excel data with dsn less connection vba-46

updating excel data with dsn less connection vba-87

The first step in setting up a linked table is to navigate to the Linked Tables dialog in Access.The table linking will still complete successfully whether you set a Primary Key or not.Once you have made your way through the previous step you will see a table listing similar to what is below.A few people have now asked me ‘Why such an article? Simply put, Microsoft has, once again, not provided a simple step by step explanation of how to accomplish this task.I was banging my head trying to get a simple ODBC connection functional and couldn’t locate instructions that actually worked anywhere.

Leave a Reply