Linked table to SQL Server without DSN file? (1 Viewer)

EricLeBouffon

New member
Local time
Today, 18:18
Joined
Mar 30, 2013
Messages
7
I have a MS Access database (2013) that I created many years ago which accesses SQL Server tables (2016) through linked tables.

Recently I added a couple of linked tables and seemed forced to use a DSN file in the process.

When deploying my new version to my customer, the link tables fail unless I create a DSN file of the same name on the workstation.

Looking through Linked table manager (see attached screenshot), I can see a difference between these new linked tables and the old ones but I have no idea how I previously managed to avoid using a DSN file... :banghead:

Any hint?
 

Attachments

  • Linked Tables.jpeg
    Linked Tables.jpeg
    85.1 KB · Views: 218

theDBguy

I’m here to help
Staff member
Local time
Today, 11:18
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess but that looks like it's maybe a System DSN and not a File DSN because a File DSN would have a filepath, I think. How exactly did you create the new DSN?
 

AccessBlaster

Registered User.
Local time
Today, 11:18
Joined
May 22, 2010
Messages
5,825
All my linked tables have the DSN designation, even though I don't use a separate DSN config.
I wonder if it is the driver.
 

isladogs

MVP / VIP
Local time
Today, 18:18
Joined
Jan 14, 2017
Messages
18,186
Perhaps you used DSN-less connection strings which means there is no need to setup each workstation separately. None of my linked tables have a DSN designation.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:18
Joined
Aug 30, 2003
Messages
36,118
I mainly use DSN-less but do have an app that uses a File DSN. Neither shows the DSN name in the LTM, nor a path in the case of the File DSN.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:18
Joined
Oct 29, 2018
Messages
21,358
I mainly use DSN-less but do have an app that uses a File DSN. Neither shows the DSN name in the LTM, nor a path in the case of the File DSN.

This is why I was thinking the OP wasn't using a File DSN but something else.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:18
Joined
Aug 30, 2003
Messages
36,118
I'm saying there's no path with a File DSN (at least with 2010), so the fact that there isn't one doesn't indicate anything.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:18
Joined
Oct 29, 2018
Messages
21,358
I'm saying there's no path with a File DSN (at least with 2010), so the fact that there isn't one doesn't indicate anything.

Oh, sorry. Got it. I forgot the path won't show up because Access automatically converts it to a DSN-less connection string. Cheers!
 

EricLeBouffon

New member
Local time
Today, 18:18
Joined
Mar 30, 2013
Messages
7
It is a "System DSN".

It's been a long time since I created the initial table links so I don't remember how it looked.

For the recent linked tables, I was forced initially to create a (system) DSN.
Since then, I just refer to it again and again.

But when copying to my user workstation, it initially failed.

Then I noticed I could get it working by just creating a System DSN of the same name.

It's not really a disaster. Just a bit irritating...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:18
Joined
Oct 29, 2018
Messages
21,358
It is a "System DSN".

It's been a long time since I created the initial table links so I don't remember how it looked.

For the recent linked tables, I was forced initially to create a (system) DSN.
Since then, I just refer to it again and again.

But when copying to my user workstation, it initially failed.

Then I noticed I could get it working by just creating a System DSN of the same name.

It's not really a disaster. Just a bit irritating...
Ah, there you go. If you can create a File DSN, you can replace the System DSN with it, and you'll be fine when you install the db on another machine. Otherwise, you could try using a relinking code to switch all your links to DSN-less.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:18
Joined
Oct 29, 2018
Messages
21,358
Hi Eric. Not sure if you have seen this already, but it might help with your current situation. Cheers!
 

EricLeBouffon

New member
Local time
Today, 18:18
Joined
Mar 30, 2013
Messages
7
Perfect!

Thank you very much for your help guys.

This community is truly amazing! :D
 

EricLeBouffon

New member
Local time
Today, 18:18
Joined
Mar 30, 2013
Messages
7
New little problem...

On my development machine. it's all perfect.

BUT when I deploy, it fails to connect and I think the culprit is an additional "Network = DBMSSOCN" added to the connection string when converting to DSN-less connection.

When running the VBA procedure
Code:
FixConnections "MyServerName", "MyDB"

I can see my table definitions being created with a simple/beautiful connection string ("ODBC;DRIVER=sql server;SERVER=MyServerName;UID=eric;Trusted_Connection=Yes;DATABASE=MyDB")
Code:
tdfCurrent.Connect = strConnectionString
But when adding this table definition to the database
Code:
dbCurrent.TableDefs.Append tdfCurrent
, quite a few details are added to the connect property, including this "Network=DBMSSOCN"...

I do not have control of my customer environment so I can't modify the way SQL Server clients connect and/or firewall settings.

The majority of the connections modified were already DSN less before, did not have these additional details, and worked everywhere...

Is there a simple way to prevent this adding of "Network=DBMSSOCN"?


I thought about running this procedure on the customer machine directly (I suppose it would not add these dodgy details) but Murphy's law has decided that I am currently unable to remote connect to it!!! :mad:
 

Users who are viewing this thread

Top Bottom