Creating the Project
Open the MS Access application from Start -> All Programs. This brings up the application and displays the following screen. The MS Access Project related items are shown bracketed in a red rectangle. You have two options, if you have created a Access project. For now, click on Project (New Data).
Access application just opened
It immediately opens a File New database window, with the default location My Documents with a default file name, adp1.adp. You should change this to something meaningful, such as ProjAcc in this case.
This makes the MS Access application name change to the chosen name, and at the same time opens up the Microsoft SQL Server database Wizard (there are more wizards in MS than in the Harry Potter books). The wizard has already recognized the resident SQL 2000 Server, XPHTEK including the authentication information (use a trusted connection, namely Windows authentication) and it has even given a database name, which is the Project name concatenated with “SQL” as shown.
Click Next and then click Finish.
The MS Access application screen has changed. Instead of being disconnected, it is now connected. Compare the two screens. Now it displays ProjAcc: Project-ProjAccSQL (Access 2000 Format).
If you now open SQL 2000 Server’s Enterprise Manager and look for the database ProjAccSQL, you will sure enough see a complete SQL database with all the objects as seen in the next picture.
Populating with Data
With the database created, it is now possible to add in data. In this tutorial, we shall see how we can link to an already existing SQL database so that we can use that data in the Access Project.
Linking with Data
You can link to existing data by going to the File –>Get External Data–> Link Tables…. In this tutorial you will link to the tables, and therefore you choose Link Tables.
This action wakes up another wizard, the Linked Table Wizard. The wizard helps you with linking to the tables on the database, using an OLEDB connection. Here you have two choices. The radio button Linked Server choice gives the most functionality, and allows storing the connection information on the SQL Server. For the tables that need to be used (or linked to), views will be created on the SQL Server. Make sure you read the information provided on this screen. By choosing the Linked Server, it may even be possible to update data on the server, if the OLEDB supports updating. The choice Transact SQL provides a read-only connection. After the choice, click Next.
This opens up the Select Data Source which allows you to browse for an existing connection from a listed source of existing connections, My Data Sources. Here you will make a new connection by clicking on the New Source… button as shown in the next picture.
This brings up the Data Connection Wizard as shown. Here you will see a list of all sources that support the OLEDB connectivity, such as OLAP Server, Oracle, and so on. Choose the Microsoft SQL Server and click Next.
Connecting to SQL Server
You need to provide the connection information to the server by filling out the needed information, namely, the server name and authentication information. If you choose the SQL Server authentication, you should provide User Name and Password. After this you click Next to access the next step of the wizard.
The next step is to choose the database and table. The wizard automatically opens up with the default database, master, as shown.
Selecting the Database
However, you can choose any other existing databases on the SQL Server. All the databases can be accessed from the drop down. Here the Northwind database is chosen as well as the the table Product Sales for 1997. Since this is just the connection step to the database, the table selection at this point does not really matter as you will shortly see. You may now click on Next.
This bring us to the next step, where the connection information is saved. The filename of this connection is the Server name, concatenated with the table you chose in the Database server connection step earlier, as seen in this screen. You may add a short description to this to help your future searches for a connection. You may now click Finish.
This brings you to this final screen in the Select Data Source screen. The connection file will also be saved in your My Data Sources folder.You may now click on Open to enter the next step, choosing the tables.