How to Use Microsoft Access to Connect to a SQL Database
Microsoft SQL Server
Microsoft SQL server is the most powerful database server in the world. With Microsoft Access, sharing out the actual databases is limited to 200 users, the complexity of the database, and the speed of the network. Generally, system administrators elect to upgrade Microsoft Access databases as performance begins to deteriorate. This upgrade to Microsoft SQL allows for more connections and unprecedented performance. Because of this upgrade, the right to create queries has virtually disappeared for the end user. MS SQL uses the Enterprise Manager or snap-ins that allow queries only to run on the server.
After the initial upgrade, many database administrators will allow users to make an ODBC connection to the database. These database administrators will create a user on the SQL server allowing the end user to have read only access when developing queries.
Microsoft Access’s Role
When the upgrade to SQL takes place, the old Microsoft Access shortcuts will, more than likely, be removed from the desktops of the end users who used it to open the older database. Microsoft Access still serves a very useful role in connecting to the Microsoft SQL server. The database administrator who performed the upgrade has generally worked with programmers to develop forms where your application is still useful. Because you don’t know what data you will need to ‘pull’ from the database, you can still use MS Access to link to the tables in order to perform queries of the data.
Creating an ODBC Connection
In order to create an ODBC connection, it is assumed that an administrator or power user must be used to create the connection. Typical end users will not have the rights to make such a connection.
In order to create the connection (Microsoft Access 2007), open Microsoft Access. Select create a new database. Name the database so that you will know it is the connection to the server.
When Access opens, click on the External Data on the Ribbon (MS Access 2007). Select More and ODBC Database.
The next screen is critical. You must choose LINK to the datasource. This will provide a connection to the SQL Server tables. The tables that are linked to the server will have read only access if the database administrator sets the permissions correctly (this is a preferred method). This also ensures data is not changed in Access but in the application that was written for SQL.
The next window will begin to add the data source, you should select new, select SQL driver, put in the SQL description, enter the server, the user name and password. At this time an option will come up to select the tables to link to. Select all of the tables and click OK. Access is now LINKED to your SQL server. This now allows you to write queries that can pull up data for special queries.
Creating an ODBC Connection
Conclusion
Although Microsoft Access is powerful and can be used for small robust databases, MS Access can be used to connect to SQL so that queries can be written and data can be checked for integrity. ODBC connections to databases can be used for SQL and programs such as Oracle and any application that uses SQL as the primary database.
