UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Connection To Sqlexpress Server With Odbc 17 Failed, SQL Server 2012    
 
   
marinko888
post Jul 30 2020, 03:58 PM
Post#1



Posts: 130
Joined: 9-April 19
From: Croatia


Hi,
I am trying to deploy my MS Access application on another PC and I am succeeding in connection with SQL Server driver:


Attached File  Capture1.png ( 64.12K )Number of downloads: 2



but I am not succeeding with ODBC 17 Driver for SQL:


Attached File  Capture2.png ( 64.35K )Number of downloads: 1



It seems like it cannot find the SQL server...


Attached File  Capture3.png ( 55.71K )Number of downloads: 3



TCPIP is enabled...

Any suggestion?
This post has been edited by marinko888: Jul 30 2020, 03:59 PM
Go to the top of the page
 
AlbertKallal
post Jul 30 2020, 07:26 PM
Post#2


UtterAccess VIP
Posts: 3,126
Joined: 12-April 07
From: Edmonton, Alberta Canada


New drivers in most cases require that you have what is called the SQL "browser" service running.

The browser service is what translates the running instance of SQL server behind the scenes to the standard port number AND IP address assigned to EACH SQL server instance.

So, if you have

MYSERVER-PC/SQLEXPRESS

AND

MYSERVER-PC/ACCOUNTING

So, you can have two instances of SQL server running from the one SQL server install. On start up, each instance (behind the scenes) is actually assigned a differnt IP address. So the browser service translates this into the standard IP address. But the two above instances are in fact given different (local) IP address. (or is a port number???). Can't quite remember.

So, while older drivers and even SSMS? Well, if you launch say the brand new SSMS (18.xx???), then you find that you can't connect to SQL server, but a older version of SSMS will! (it just goes and attempts the default and that works).

So, now the newer drivers actually "respect" the "instance in a more correct way. And as a result, if you don't run the browser service, you find that SSMS, or the newer drivers can't connect.

The service I speak of is this one:



So, in above, we have SQL server running, and also the browser service running. You need that for things to work with later clients, and you find that even later verisons of SSMS don't connect now if you don't have the browser service running.

The browser service is similar to a NAT router translation service, and it will route the incoming IP address of the server to an IP address assigned to each instance of SQL server running on your computer.

I seem to recall that you can set the default instance of SQL server to use, and not necessary have to run the browser service. But, I find it is now quite much required for for newer client software (such as native 17) to connect to the correct instance of SQL server.

And I also recall that somewhat "older" versions of SQL server tended to work for reasons of compatibility since in the past, most SQL server installs used a default instance, and the browser service was not required. These days, it is in most cases required.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
marinko888
post Jul 30 2020, 07:52 PM
Post#3



Posts: 130
Joined: 9-April 19
From: Croatia


Thank you very much for your answer. I was running in circles for quite a while...
I will give it a try...
Go to the top of the page
 
marinko888
post Jul 31 2020, 08:41 AM
Post#4



Posts: 130
Joined: 9-April 19
From: Croatia


Hi,
I have activated SQL server Browser service and it is running now:

Attached File  Capture1.PNG ( 25.46K )Number of downloads: 0


but still cannot connect to SQL Server instance:

Attached File  Capture2.PNG ( 15.49K )Number of downloads: 0


That is when I am trying to connect with My Data Sources DSN from within the MS Access application.
But when I try to connect directly with ODBC the connection has been established...

Attached File  Capture3.PNG ( 49.67K )Number of downloads: 0


Ignore all mentioned above...I have eventually found the solution...

Anyway thanks a lot for your help. You have helped me to move on with the deployment...
Have a good one!

This post has been edited by marinko888: Jul 31 2020, 09:07 AM
Go to the top of the page
 
AlbertKallal
post Jul 31 2020, 09:16 PM
Post#5


UtterAccess VIP
Posts: 3,126
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, just keep in mind one more tip:

ALWAYS but always create and use a FILE dsn. Access will automatic convert the connection to DSN-less for you.

This means, that once you link the tables, you can distribute the front end to any workstation, and you don't have to setup anything on that workstation.

Of course if you use a native 11 or later connection? Well then of course that native driver has to be on each workstation, but you not have to setup or create or use a DSN on each workstation.

So, do adopt the use of a FILE dsn. Once you link the tables, then the DSN is 100% ignored, not required, and in fact you could delete the DSN and the linked tables will continue to work.

And for the users here? Might have helped to point out what the issue was.

But, the best tip I can share is the FILE dsn trick - it will case by default all your tables to be linked as dsn-less.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 08:10 AM