INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...Thank you. I have never said it, but you have a great web site. It is of immense help...."
Where in the world do Tek-Tips members come from?
T-SQL Hints and Tips
Using Access as front-end to SQL Server
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
Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
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:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close