ODBC type to use? System or File

[LLBP]

I am linking to SQL tables from Access. I setup a file DSN on the server where the Access db resides and the linked table in Access uses this DSN. The SQL table uses host_name() to fill in a field. It always records the computer name of the computer that the DSN was created on rather than the computer name the record was created from.

Which is better/more efficient to use, SYSTEM or FILE DSNs? Where should the FILE DSN reside, on the server with the Access file or on the SQL server?

Can Access create a SYSTEM DSN if one does not exist?

Thanks for any help,
Brent

 

[ArtieChoke]

Which is better/more efficient to use, SYSTEM or FILE DSNs? Where should the FILE DSN reside, on the server with the Access file or on the SQL server?

I don't know about efficiency, but there's probably not much difference between the two. I suspect once the connection is open, the DSN is no longer accessed.

I use file DSNs when I have multiple users for the database. It's also easier to change server names in one place and you don't have to set up DSNs on each user's machine. I usually put the DSN with the database.

Can Access create a SYSTEM DSN if one does not exist?

Automatically? I doubt it. You could probably do it in VBA code.


"I think we're all Bozos on this bus!" - Firesign Theatre