Adp Connection issues with SQL Server 1

[VBAHole22]

I designed a nice user interface using an Access project (.adp) based on a SQL Server 2000 db. It works fine and dandy and now I want to move it to another machine so the data entry technician can begin using it. I cannot seem to get it to work though. It will open but it has no tables and it says it is disconnected. I have tried meesing with the connection dialog but I can't get it to work that way either. I guess I am showing my ineptitude when it comes to security settings. It's just a mess considering we have a Novell login, and a Windoze login, then there are userids and passwords for SQL as well. I tried using the integrated security but that didn't work either. I tried every combination of uid and pwd that I could come up with in the connection dialog box but when I test connection it always returns a message to the effect that the server can't be found or the login is invalid or something like that. I even tried to create a blank .adp on this user's machine and then connect to the db but it wouldn't work either. Am I missing something here, or a lot of things?
Any help would be greatly appreciated.

 

[neecie]

I am not positive about SQL 2000, but in SQL7, if the Startup Service Account
(find it in the server properties dialogue on the security tab)
is the default "system account" then you can not access the server over a network.

You need to change it to "This Account" and assign a valid network logon

 

[VBAHole22]

I finally figured it out (actually someone here did for me). The issue was between Named Pipes and TCP/IP. Apparently Windoze was defaulting to Pipes and I needed TCP/IP. The fix was to go into the Control Panel>Administrative Tools>ODBC and attempt to add a system DSN and configure a bogus DSN just to get to the Client Configuration dialog. Once there all you need to do is open it and make sure TCP/IP is selected and then cancel out. You don't even need the DSN.

I can't explain how frustrating it was to find out the solution was something so idiotic. Had I posted the error message here someone may have picked up on the Pipe reference, who knows?

 

[neecie]

Glad your problem is solved!

 

[NickinDC]

I'm having the same problem VBAHole described, but I can't go around and run the fix on all the users who will have the problem when they open my ADP. (they are all over the country, on multiple domains.) Is there another way to trick Win2k into using a tcp/ip connection?

My ADP connection is "sqlserver.intranet.domain.com". Installing the SQL Server Client fixes the problem, as does the System DSN workaround described above. I've searched msdn, but can't find anything about this sort of problem.

Thanks for any suggestions.

 

[Norris68]

See Microsoft Knowledgebase article Q238949

HOWTO: Set the SQL Server Network Library in an ADO Connection String (Summary)

SQL Server allows you to use the following network libraries when establishing a connection. They are:
dbnmpntw - Win32 Named Pipes
dbmssocn - Win32 Winsock TCP/IP
dbmsspxn - Win32 SPX/IPX
dbmsvinn - Win32 Banyan Vines
dbmsrpcn - Win32 Multi-Protocol (Windows RPC)

The connection string:
"Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Extended Properties="DSN=yourdatasourcename;UID=sa;DATABASE=pubs;Network=dbmssocn";Initial Catalog=pubs"
connects to SQL Server through the MSDASQL provider using the TCP/IP network library.

This is essential if you are trying to push an ADO connection through a firewall.