How do I connect to a SQL server back end? 1

[AndrewWolford]

Hello everyone,

I am very new to SQL server, so please bear with me...

I have a SQL Server database that I'm trying to build an Access front end around. There are 2 SQL tables that I need to access with 1 form.

I understand that I can link to these tables by going (in access):

File>Get External Data>Link Tables. Then choosing ODBC from the "type" dropdown box. I'm confused as to what to put in the "DSN Name" box. I click on "New" and begin to follow the wizzard, but I get hung up on the "Type the name of the file data source you want to save this connection to" prompt. BTW, the SQL database is on a server.

Once I create this connection, can I link to multiple tables? If so, can I create queries in Access to join the 2 tables? If I link this query to a form, do any changes I make to the data on the form get mapped back to the "actual" tables on the SQL server database?

Thank you VERY much for any help with this!

-Andrew

 

[Tranman]

Andrew,
The question "Type the name of the file data source you want to save this connection to", is asking you to give a name to a file that will contain a "File Type" DSN.

Unless you are wanting to share this DSN among different users, it would probably be better to use a "System" DSN instead of a "File" DSN.

To create a System DSN (this process differs a bit with different MDAC's/operating systems) in WinXP, go to START|SETTINGS|CONTROL PANEL|ADMINISTRATIVE TOOLS|DATA SOURCES (ODBC). Click the System tab, and click the add button. Select the SQL Server driver, then just fill in the blanks. The advantage of using a System DSN instead of a File DSN is that the System DSN "lives" in your registry, and your programs can always find it. With a File DSN, you have to keep track of where it is. (The functionality is the same with either one.)

Once I create this connection, can I link to multiple tables? You can link to any/all of the tables/views in your database. can I create queries in Access to join the 2 tables? Yes, you can.

If I link this query to a form, do any changes I make to the data on the form get mapped back to the "actual" tables on the SQL server database? Yes, all changes made to the data on the form will be applied to the underlying table, unless you do not have permissions needed to update the data.

Good Luck,
Tranman

 

[AndrewWolford]

Thank you for helping me out, I think I got it to work now. I can link to the tables and view them in Access now. The database will be used by multiple users, but I think the plan is to do a remote desktop connection to the server, and have the Access front end be accessed from there. For this type of use you suggest using a system DSN over a file DSN? Thanks again for all your help!

-Andrew

 

[Nevets]

I generally set up my users with system DSNs.
That way the DSN can be used by anyone who logs onto that PC, as long as they have permissions assigned on the SQL server.

I also recommend that you come up with standardized DSNs.
i.e: the description of a given DSN should be the same on each user's PC. Otherwise a user will not be able to open a table which was linked under a DSN which is different from their own.