INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Using Access as front-end to SQL Server by AWithers
Posted: 14 Aug 02 (Edited 14 Aug 02)

Users who are familiar with the MS Access interface may find the initial upgrade to sql server daunting.  The following useful first step will allow users to continue using the interface/functions they are familiar with.

This involves linking the sql server tables in using odbc

First of all set up an odbc entry via control panel to your sql db

Then, in Access, choose the menu options file/get external data/link tables and change the file type to 'odbc databases' and then select the odbc entry you set up earlier

You will then be able to select all the tables you want to link in.  It will then be possible to query, update, append and delete data from the sql database (assuming the user has suitable ermissions) directly from Access

A couple of things to note.  Access needs to understand the primary key on the tables, if this is not set up in sql server you will need to define the fields which form the key.  Also Access will prefix the table names with the table owner and an underscore e.g.dbo_yourtable, but you can then rename them as you wish without affecting the underlying link.

While Access will let you link to a table withoout some kind of unique Identifier, it will not let you update, insert or delete from that table without one.

Also, Access will not auomatically reflect any underlying table structure changes you make. You will have to refresh the link on the tables when you change their structure in SQL Server.

I hope this is useful to someone and not too obvious to those who do this all the time

Andy


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close