ADO Connection String not working when SQL on local machine

Here is the connect tring from table properties:
ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates

Here is the connect string from the ADO .Open connect string:
"ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"

Here is the ADO .Open code.
Set CNN = New ADODB.Connection
Dim strDEFConn As String
strDEFConn = FixConnStr(DEFCONN)
CNN.Open strDEFConn

The last line fails with the CNN.Open strDEFConn with this message:
Run-time error '-2147467259 (80004005)';
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

This code works when the SQL Server is on it's own server, but for testing at my own office, I have SQL Server on the same machine as the Access application. I'm getting the above error where SQL Server and the Access app are on the same machine. I can open a linked SQL table from the user interface, and VBA code that deals with the tables as Access tables works. It is the ADO .Open statement where the error happens.

Thanks for any help you can give me on getting this to work.Here is the connect tring from table properties:
ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates

Here is the connect string from the ADO .Open connect string:
"ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"

The error message is basically telling you that it can't find the DSN entry.

Okay, so you are using ODBC (which you have to because it's Access), how do you have the DSN configured? Is the DSN a user DSN or a user DSN. I'm not entirely clear on what it is that you are doing, but if the script is executed as a batch file using a system account, I think it won't find the DSN unless the DSN is a "system" DSN.

By the way, those connection string look a little odd to me. Did you check out www.connectionstrings.com?

Regards,

hmscott|||I think these are DSNless connections.

Ordinarily I have a switch in my apps that change the server to (Local) when developing and that pull up the server name when run by a normal user. However I am not sure how this will work with a named instance. Is there more than one instance on VICRAUCH?|||Yes, I have more than one instance on VICRAUCH. What I have to switch between different servers and databases is a local table in Access which I name "tblSetup" This is the table that holds all my "macro" variables. I use the term "macro" as in large scope. Those that relate to the app as a whole. This Access db seems to have had 3 or 4 different developers that have put it together and each one has sort of started over with their own methodology. One of my goals is to try to standardize the app without doing a complete re-write. But at the same time, be very responsive to this client when they want something done yesterday. This is continuing to be quite the challenge.
Because I am so new to SQL Server could you give me some insight as how you do the "switch" thing with the server from production to local?
Thanks,|||The "switch" is trivial - I meant (programmatically) swapping:
ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidateswith
ODBC;DRIVER=SQL Server;SERVER=(Local);APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates
However - you are connecting to a named local instance. I'm not sure how that would work. I don't have a named (local or otherwise) instance here at work but I do at home. I will try tonight if no one else sorts it out for you by then.|||What I did to get the named instance to work was "VICRAUCH\SRVRVGSMISC" as the Server= name rather than just SRVRVGSMISC. What you posted here is what needs to be done, and actually what I'm doing by changing the values in my tblSetup. I have in tblSetup the proforma connection string, Driver={SQL Native Client};Server=srvrName;Database=SQLdbName;Uid=use rName;Pwd=;and then before setting that connection string, I replace the placeholders in the proforma string with what is in the coresponding field of tblSetup.

Thank you very much for your help.