Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSN-less ODBC Connect (OFC 2000 SR-1)

    Any ideas on how to connect to SQL Server from EXCEL VBA using DAO object without requiring DSN or other file on client PC? I've tried several strings.... none work. I've got references set to DAO 3.6. Thanks.

    PS: This is what I'm doing (xxx replaces real data):

    dim esWsp As DAO.Workspace
    dim esCon As DAO.Connection
    const ConStr = "ODBC;UID=xxx;PWD=xxx;SERVER=xxx;" _
    & "DRIVER={SQL SERVER};DATABASE=xxx;"

    Set esWsp = DAO.CreateWorkspace("1", "", "", dbUseODBC)
    Set esCon = esWsp.OpenConnection ("Connect", dbDriverCompleteRequired, True, ConStr)

    BTW: connection does work with a DSN and a CONN string without the DRIVER and SERVER parameters.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSN-less ODBC Connect (OFC 2000 SR-1)

    This depends how your SQL server is setup, but probably, it is using a TCP/IP connection, so you should try using either the IP address or server name together with the port number, as follows.

    <pre>const ConStr = "ODBC;UID=xxx;PWD=xxx;SERVER=YOURSERVERNAME:14 33;" _
    & "DRIVER={SQL SERVER};DATABASE=xxx;"</pre>


    or

    <pre>const ConStr = "ODBC;UID=xxx;PWD=xxx;SERVER=255.255.255.255:1433; " _
    & "DRIVER={SQL SERVER};DATABASE=xxx;"</pre>


    (255.255.255.255 replaced with appropriate IP address of the server)

    In both cases, I've used port 1433 which is the default port on which SQL server works, but this can vary, you should check with your SQL server administrator.

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSN-less ODBC Connect (OFC 2000 SR-1)

    Tried SERVER:1433, doesn't work . If I use dbDriverPrompt I get a DSN setup dialog; if not, error 3146, ODBC call failed. I will try using IP address once I get it. Colleague here at work said he did some research into trying DSN-less with DAO and could not get it to work either.

Posting Permissions

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