How To Administer your SQL Server database with MS Access



Administer your SQL Server database with Microsoft Access. Those who are familiar with Access might find this more appealing and you won’t have to install SQL on your local machine.

Administer your SQL Server database with MS Access. Those who are familiar with Access might find this more appealing and you won’t have to install SQL Server on your local machine to administer a remote SQL database.
To set up a link, follow these instructions:

  1. Create an empty database from within MS Access. 
  2. Next select the new button from the tables view menu on the right hand side.  In the popup window select link table.  You will then be presented with the standard windows file dialog windows. 
  3. From the file types combination box at the bottom of the popup window select the very last selection ‘ODBC Databases’. You will now be presented with the ‘Select Data source’ dialog. 
  4. Select the ‘Machine Data source’ tab. 
  5. Select the new button on the lower right hand side of the dialog box just below the data sources window. You will then be presented with the ‘New Data Source’ dialog. 
  6. Select the radio button ‘System Data Source’  then click the ‘Next’ button.  You will then be presented with the ‘Driver Selection’ dialog, select ‘SQL Server’ from the drivers listed then click the ‘Next’ button. 
  7. The next dialog box will show you the type of connection you have created click the ‘Finish’ button. 
  8. You will now be presented with the ‘Create new data source to SQL Server’ dialog.  Enter the requested information.  In the server box enter the IP address or name of the server that was given to you when you created the SQL database click the ‘Next’ button. 
  9. You will now be presented with the ‘Authentication’ dialog select the radio button ‘With SQL server Login ID’  the ‘Login Id’  and password boxes at the bottom of the dialog will now activate. Enter you Login ID and Password in the boxes at the bottom of the dialog, click ‘Next’ . 
  10. You will now be presented with the database name dialog, leave the defaults as they are and click the ‘Next’ button at the bottom of the dialog. 
  11. You will now be presented with the ‘Translation’ dialog, leave the defaults as they are and select ‘Finish’. 
  12. You will now be presented with the ‘Test’ dialog click the ‘OK’ button. You will then be returned to the ‘Data source’ dialog click the ‘OK’ button at the lower right hand side of the dialog. 
  13. You will now be returned to the MS Access work space.  Once you have returned to the work space you will be presented with the SQL login dialog, enter your user name and password the click the ‘OK’ button. 
  14. You will now be presented with the table dialog select the tables that you wish to work with then click the ‘OK’ button.  

You now can start working with your tables!

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *