DSN-less Connection works on some computers not others... 2

[kcolemanSLU]

Hi all,

I'd appreciate any ideas you have as we're running out and this problem is driving us nutty.

I've written a module that uses a DSN-less connection to connect MSAccess to SQL Server. It then automatically links all the tables/views an end user has the security to see. It's pretty simple program and has worked flawlessly for me and a few others I've had try it.

Yesterday I went to set up our first end user with this module and although there are no errors, no tables were linked. I opened up the module and followed through my debug.print statements to see where it wasn't working, but everything looked right. It was using the correct connection strings, had retrieved the correct table/view names, etc... it simply doesn't link them. Since there's no error, I really have little to go on.

I had the user log on to my laptop and the same database (it's on a shared drive) ran fine. When I logged on to her computer, it didn't work for me. I'm assuming it's something on her computer - but I'm not even sure where to look. We checked the UAC, took a look at the events log, verified she was an administrator on her computer, etc. I didn't check the firewall settings yet.

If you have any ideas or have encountered a similar situation, I'd really appreciate it.

Thanks!

Kimberly

 

[kcolemanSLU]

Forgot to mention, that using the regular Import > ODBC through a DSN works fine on this user's computer.

*head desk*

 

[smedvid]

Just a thought... try to Ping the server from the users PC. I recall having a firewall issue before where a group of users were blocked... htwh,

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[dhookom]

We have experienced similar results here at work. We found that is often helps to include the PORT in the connection string. You can do this by add the port to the server name like:

  ...;Server=USMyServer,1433;...

1433 is the default port but many of our SQL servers use an alternative.

Duane
Hook'D on Access
MS Access MVP

 

[kcolemanSLU]

Thanks I'll try these suggestions and get back to you. So far it is only this one user who is having an issue with the module which makes it even the more frustrating in some ways...

Kimberly

 

[smedvid]

Is this one user on a different network sub-net which may route it through a different firewall?

Also, does the user have proper permissions to access to back-end SQL Server DB. Perhaps ask user to login using a different PC, so see if problem is specific to that users' PC or login permissions.


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[kcolemanSLU]

Same network. I did have her log onto my computer and another computer and both worked fine. Her security is set up and working fine through SQL - I'm returning the proper table names to link - they just don't link. And everything works ok through the DSN...

Thanks!

Kimberly

 

[kcolemanSLU]

adding the port number didn't work, but thanks for the idea. I'm wondering if it's another program or another program's settings? It just seems odd to me that both her computers wouldn't work when no one else seems to have an issue. I took one of her computers back to my office and I'm getting the same result.

 

[kcolemanSLU]

Well I found the issue - the user's computer can't use DRIVER=SQL Native Client, but works fine with DRIVER=SQL Server

Should I just run with SQL Server as the driver?

Kimberly

 

[jrbarnett]

Install the SQL Native client on her PC.

John

 

[boblarson]

The rule of thumb right now, if I remember correctly, is:

User O/S = WinXP
SQL Server 2000 - SQL Server Driver
SQL Server 2005 - SQL Native Client
SQL Server 2008 - SQL Native Client(10)

User O/S = WinVista
SQL Server 2000 - SQL Server Driver
SQL Server 2005 - SQL Server Driver
SQL Server 2008 - SQL Native Client(10)

User O/S = Win7
SQL Server 2000 - SQL Server Driver
SQL Server 2005 - SQL Server Driver
SQL Server 2008 - SQL Server Driver


Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Darrylles]

Hi,

I just know I'm gonna need this info. in the near future.

I've starred kcole and Bob for this.

;-)

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[kcolemanSLU]

Just thought I'd follow up and confirm that installing SQL Server Native Client (we used 10) resolved the issue completely. Thanks for all the input and the star. We'll begin testing with Win7 shortly so thanks Bob for that information as well.