Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: 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.
    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.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by GolferGuy
    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
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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,

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The "switch" is trivial - I meant (programmatically) swapping:
    Code:
    ODBC;DRIVER=SQL Server;SERVER=VICRAUCH\SRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidates
    with
    Code:
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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,
    Code:
    Driver={SQL Native Client};Server=srvrName;Database=SQLdbName;Uid=userName;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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •