Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-31-2019, 01:33 PM   #1
EricLeBouffon
Newly Registered User
 
Join Date: Mar 2013
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
EricLeBouffon is on a distinguished road
Linked table to SQL Server without DSN file?

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...

Any hint?
Attached Images
File Type: jpeg Linked Tables.jpeg (85.1 KB, 16 views)

EricLeBouffon is offline   Reply With Quote
Old 10-31-2019, 01:36 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,237
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-31-2019, 01:43 PM   #3
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,302
Thanks: 34
Thanked 310 Times in 295 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

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.

AccessBlaster is offline   Reply With Quote
Old 10-31-2019, 01:51 PM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,900
Thanks: 120
Thanked 3,324 Times in 3,000 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Linked table to SQL Server without DSN file?

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-31-2019, 02:08 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,478
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Linked table to SQL Server without DSN file?

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-31-2019, 02:32 PM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,237
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

Quote:
Originally Posted by pbaldy View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-31-2019, 02:37 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,478
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Linked table to SQL Server without DSN file?

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.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-31-2019, 02:41 PM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,237
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

Quote:
Originally Posted by pbaldy View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-31-2019, 02:45 PM   #9
EricLeBouffon
Newly Registered User
 
Join Date: Mar 2013
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
EricLeBouffon is on a distinguished road
Re: Linked table to SQL Server without DSN file?

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...
EricLeBouffon is offline   Reply With Quote
Old 10-31-2019, 02:49 PM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,237
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

Quote:
Originally Posted by EricLeBouffon View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-31-2019, 06:16 PM   #11
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,237
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

Hi Eric. Not sure if you have seen this already, but it might help with your current situation. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
EricLeBouffon (11-01-2019)
Old 11-01-2019, 12:06 AM   #12
EricLeBouffon
Newly Registered User
 
Join Date: Mar 2013
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
EricLeBouffon is on a distinguished road
Smile Re: Linked table to SQL Server without DSN file?

Perfect!

Thank you very much for your help guys.

This community is truly amazing!
EricLeBouffon is offline   Reply With Quote
Old 11-01-2019, 08:03 AM   #13
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,237
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linked table to SQL Server without DSN file?

Quote:
Originally Posted by EricLeBouffon View Post
Perfect!

Thank you very much for your help guys.

This community is truly amazing!
Hi. We're here to help. Good luck with your project.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Tags
sql-server

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table based function using Row_Number() in SQL Server fails Linked Table connection Rx_ SQL Server 1 12-28-2016 08:51 AM
[SOLVED] Insert/update values into linked table (linked from sql server via odbc connection) dejanc Tables 2 12-17-2015 04:31 AM
Copy data from local Access table to linked SQL Server table WaiveIt General 1 01-14-2014 09:38 AM
Linked table From SQL Server accessman2 Tables 3 09-01-2006 11:52 AM
Append Records from an Access Table to SQL Server Linked Table Steven Deetz Queries 1 07-23-2006 05:05 PM




All times are GMT -8. The time now is 01:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World