DSN File Required for Linked Table Front-end Access?

[ahjeck]

Hi, I have an Access Front-end and a SQLServer (2005) back-end setup. The tables are basically linked into the Access front-end via ODBC link with a DSN file. In addition, the authentication is through Windows Authentication.

My question is, once the Front-end Access file has established the link and saved the connection string into its linked SQLServer tables, is the DSN file required anymore (for end users)?

Like, I link the tables on my computer for testing purposes. Once it's set, I can pass the same frontend file to some test users, w/o setting up a DSN file on their machine. My logic tells me, if there are any changes to the location of the SQLServer db, I will have to re-establish the ODBC link on my end anyways, and then do the same thing, which is deploy the Access file to users with the modified connection string. So...is the DSN file required on users machines?

Thanks in advance!

 

[pbaldy]

Yes, but what I do is create a File DSN on the server, and link my tables using that. Then you don't need anything on the user PC's.

 

[boblarson]

So...is the DSN file required on users machines?

Yes, they would need the DSN if you are using that to connect with and link tables.

 

[boblarson]

Yes, but what I do is create a File DSN on the server, and link my tables using that. Then you don't need anything on the user PC's.

That's how we did it at my last job too. Just make sure that your people have access to the network folder that you have stored the file DSN's in.

 

[WayneRyan]

I don't use DSN files.

Sample OnOpen event:

Set tdf = dbs.TableDefs("SomeTable")
tdf.Connect = "Driver={SQL Server};" & _
              "Server=" & Me.txtServerName & ";" & _
              "Database=" & Me.txtDatabase & ";" & _
              "Trusted_Connection=Yes;" & _
              "Table=SomeTable"
tdf.RefreshLink

Wayne

 

[GolferGuy]

Yes, but what I do is create a File DSN on the server, and link my tables using that. Then you don't need anything on the user PC's.

Paul, could you run through the steps to do this please. I am a complete novice when it comes to linking to SQL Server from Access. It works fine on my development machine, but when I take it to production, I really not sure of how, or the "best" way. The only production setup I have worked with before, the prior developer had a two or three minute routine, EVERY time a user opened the DB, that would go through all the SQL linked tables and re-link them. At least I know that is NOT a good way of doing it.
Thanks!

 

[pbaldy]

Vic, what are you doing slumming over here?

I've got to run to a 9:00 meeting, so the quick version is to create a File DSN on the server (File Tab instead of User or System). Put it in a shared folder on the server (I use a $ share so it's not visible). In your application, link tables using that DSN instead of a User or System DSN on your computer. Now when you distribute the app to other users on the network, it should work without having to do anything to their PC.

Gotta go; post back if you have trouble.

 

[ahjeck]

Thanks for all the replies!

I was thinking that once a link has been established via DSN file, each table will have a connection string already inserted to connect to it, thus the DSN is no longer needed for the remainder of the connection. But then I was thinking at how it would 'refresh' each link if the DSN file is absent.

 

[Banana]

Not true.

In fact, I am using DSN-less connections for all of the tables, and they work just dandy as if they were connected via DSN. Some people say there's better performance if you don't use DNS. Other says it's better with DSN.

Here's Doug's excellent article explaining how to recreate the DSN-linked tables as DSN-less tables. This way, you can use graphical interface to connect with a server and get all tables you need, then just run his code and voila! No DSN, mama!

Linky

 

[WayneRyan]

Banana,

I agree see post 5 this thread. I used to dutifully make the DSN files, but
maintenance became intolerable.

Much better without them. I'll look at the link.

Thanks,
Wayne

 

[ahjeck]

Hi again, something's been bothering me and perhaps I've been confused but, when I created my linked tables, I created a File DSN long with it. I then used this File DSN to connect to the sql server.

But, when I look at the linked table's Description, nowhere does it point to that DSN file.

Here's what the description states:
ODBC;DRIVER=SQL Server;SERVER=(local);APP=Microsoft Data Access Components;WSID=AB0001;DATABASE=Inventory;Trusted_Connection=Yes;TABLE=dbo.tblItems

Isn't this the whole connection string?

Where do I find the DSN file that it was linked to?

 

[Banana]

Strictly speaking, you don't need to have a DSN file to make a connection, which is why you didn't see it anywhere in that string.

Some people have reported better performance without DSN, some didn't. However, one benefit of DSN-less connection is that you have one less file to distribute to your end users because connection made via DSN now depends on that file to complete the connection.

I gave the link to DSN-less connection code by Doug Steele above.

As for finding the DSN, I'm afraid I have no idea.

 

[tehNellie]

Where do I find the DSN file that it was linked to?

By default I believe they're in C:\program files\common files\odbc\data sources

 

[shahzad_syed]

I don't use DSN files.

Sample OnOpen event:

Set tdf = dbs.TableDefs("SomeTable")
tdf.Connect = "Driver={SQL Server};" & _
              "Server=" & Me.txtServerName & ";" & _
              "Database=" & Me.txtDatabase & ";" & _
              "Trusted_Connection=Yes;" & _
              "Table=SomeTable"
tdf.RefreshLink

Wayne

Hi Wayne,

I am new to Access. Can you please let me know what you mean by OnOpen event?

Cheers
Shahzad

 

[tehNellie]

pretty much exactly what it says on the tin, it's a block of code that executes before the object concerned, such as form and any data manipulation it tries to perform, is opened.

There's a better explanation of form events and the order in which they trigger here:
http://www.access-programmers.co.uk/forums/showthread.php?t=113078