Access users can't use SQL 2016 (?)

Question

Access users can't use SQL 2016 (?)

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

Do you mean pinging from the user's pc? What would be the best way to do this?

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 2

If the question is - can I ping from the user's PC the server that hosts my SQL Server Instance containing the needed database? - the answer is yes I can:

C:\Users\campbell>ping nbcp-sql01-vm

Pinging NBCP-SQL01-VM.nbc-us.org [fe80::5df3:11d:e13b:89e8%3] with 32 bytes of data:
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms

Ping statistics for fe80::5df3:11d:e13b:89e8%3:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Sue_H SSC Guru Points: 90860 More actions · Answer 3

We're running out of things you can try!
You said this is a named instance. You said you opened port 1433. It's not usually recommended to use that port for a named instance. Are you sure that's the port it's listening on? You can check in SQL Server configuration manager - for the instance protocols, check TCPIP and check the ports or if it's using dynamic ports. If there is a default instance on that server then you want to use a different port. And I would check the other instances on that server to see what they are using for the listening port. All of them should be different. SQL Browser should be running on that server for the named instances - unless you specify the ports in the connection. The browser uses UDP port 1434

Did you try a test at the end of setting up the DSN in ODBC Administrator?
The only other thing I can think of is it maybe be trying to use a different protocol. In that case, you could try prefacing the server name with tcp. So the server name would be
tcp:NBCP-SQL01-VM\NBCSQL01VM

Sue

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Do you have SQL Browser running and port 1434 open?

Edit: Sorry, should have gone down further, would have seen Sue already said this.

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 5

briancampbellmcad - Thursday, February 8, 2018 12:55 PM
If the question is - can I ping from the user's PC the server that hosts my SQL Server Instance containing the needed database? - the answer is yes I can:
C:\Users\campbell>ping nbcp-sql01-vm
Pinging NBCP-SQL01-VM.nbc-us.org [fe80::5df3:11d:e13b:89e8%3] with 32 bytes of data:
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Ping statistics for fe80::5df3:11d:e13b:89e8%3:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

The ping shld be done from your server not from pc. I'll share an MSDN post today. I hope you'll resolve the error

Davis H Right there with Babe Points: 727 More actions · Answer 6

subramaniam.chandrasekar - Thursday, February 8, 2018 9:34 PM
briancampbellmcad - Thursday, February 8, 2018 12:55 PM
If the question is - can I ping from the user's PC the server that hosts my SQL Server Instance containing the needed database? - the answer is yes I can:
C:\Users\campbell>ping nbcp-sql01-vm
Pinging NBCP-SQL01-VM.nbc-us.org [fe80::5df3:11d:e13b:89e8%3] with 32 bytes of data:
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Reply from fe80::5df3:11d:e13b:89e8%3: time<1ms
Ping statistics for fe80::5df3:11d:e13b:89e8%3:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
The ping shld be done from your server not from pc. I'll share an MSDN post today. I hope you'll resolve the error

Are you saying he should ping the server from the server?
Even if you can ping, you still may not be able to connect. Trying to connect a telnet session from the PC would be better as it is a two way connection. It could be a DNS issue, so try connecting to the IP. Google how to do that with named instance. If browser is not running, then you need to assign and use a unique port. 1433 is default, not named.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 7

I installed SSMS on the client machine and I can access the SQL Server database from there. Just not able to get Access to open with the ODBC tables visible (which I can easily do from my machine). Does Access need a specific port open? Our only firewall is one between the internet and our network so it seems there should be no issue with the IP address or the ports (?)

Davis H Right there with Babe Points: 727 More actions · Answer 8

Have you tried re-creating the Data Source after SSMS install? I'm guessing it also installs the latest drivers for native and ODBC connections. You can download the latest and greatest MS providers and re-install.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 9

Do you mean reinstalling SSMS on the host server, client PC, or my admin PC?

Davis H Right there with Babe Points: 727 More actions · Answer 10

briancampbellmcad - Monday, February 12, 2018 6:51 AM
I installed SSMS on the client machine and I can access the SQL Server database from there. Just not able to get Access to open with the ODBC tables visible (which I can easily do from my machine). Does Access need a specific port open? Our only firewall is one between the internet and our network so it seems there should be no issue with the IP address or the ports (?)

You say here that you installed SSMS on the client machine. I assume this is also the machine where the Access DB is and where you are trying to create the Data Source.

Now that you have SSMS installed on this machine, have you tried creating the Data Source to the SQL Server?

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 11

I installed SSMS on the client PC and the database and put the Access database on the desktop. Opened it and went into the Linked Table Manager and created a new connection to the host server. Access could not see the database at all.

Davis H Right there with Babe Points: 727 More actions · Answer 12

Sorry, it's been a year or ten since I worked with Access, but I always used the ODBC Data Source Administrator to create the connector first, then use it when linking a table. I'm guessing it does something similar now, but it just feels like it is not using the latest provider.

Sue_H SSC Guru Points: 90860 More actions · Answer 13

Davis H - Monday, February 12, 2018 3:39 PM
Sorry, it's been a year or ten since I worked with Access, but I always used the ODBC Data Source Administrator to create the connector first, then use it when linking a table. I'm guessing it does something similar now, but it just feels like it is not using the latest provider.

I don't think it's the driver as much as how the User DSN is configured but I would still use the Data source administrator applet.
You always could do the registry hack but I always thought you needed an additional entry in
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources
not just
Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<DSN Name>
Maybe it's already there though...don't know. I would have thought it's most likely to be the DSN configuration as the connections with SSMS are fine so everything else for connectivity is in place.

Sue

john.perry 65725 Old Hand Points: 344 More actions · Answer 14

Just a thought, I saw an article a while ago but can't remember where, that says basically if you are using the 32 bit version of Office on a 64 bit machine you must use the 32 bit version of the ODBC administrator and not the 64 bit version.