Results 1 to 7 of 7
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Connection String from Access to SQL Server (Acces

    In an attempt from my other post to talk directly to SQL server instance from Access 2000 using ADO

    I am attempting to build a connection string from an Access database to a SQL server 2005 instance

    I do not want to use a DataSource which appears to work from a query.
    Rather I want to have a connection string.

    However I have tried several options and all seem to fail
    Although they work if used from VB6

    Attempts are below obviously with UID and PWD changed
    Using MS DataShape
    Set cnn2005 = "Provider=MSDataShape.1;Data Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=svr-sql;Initial Catalog=Training;User ID=uuu;Password=ppp"

    Using OLEDB
    set cnn2005 = "Provider=SQLOLEDB.1;Password=ppp;Persist Security Info=True;User ID=uuu;Initial Catalog=Training;Data Source=SVR-SQL"

    Using Native Client
    set cnn2005 = "Provider=SQLNCLI.1;Password=ppp;Persist Security Info=True;User ID=uuu;Initial Catalog=Training;Data Source=svr-sql"


    None of these work, and All return the same error message

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

    Any help greatly appreciated
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connection String from Access to SQL Server (Acces

    I assume that cnn2005 is a string, so you shouldn't use the keyword Set (that is for objects only). Try the following:

    cnn2005 = "Provider=SQLNCLI;Server=svr-sql;Database=Training;Uid=uuu;Pwd=ppp;"

    If cnn2005 is an ADODB Connection object, you cannot set it to a string value. Instead, you should use

    Dim cnn As New ADODB.Connection
    cnn.Open ConnectionString:="Provider=SQLNCLI;Server=svr-sql;Database=Training;Uid=uuu;Pwd=ppp;"

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Connection String from Access to SQL Server (A

    Apologies, I miss typed those entries they should have read

    strCNN2005 = etc

    However, when any of those strings is applied to a connection object
    and then that is opened the open action fails with the aforementioned error
    Andrew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connection String from Access to SQL Server (A

    Are you using SQL Server 2005 or SQL Server 2005 Express?

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Connection String from Access to SQL Server (A

    2005
    Andrew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connection String from Access to SQL Server (A

    I'm out of ideas, but perhaps this overview will help.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Connection String from Access to SQL Server (A

    The problem appears to be associated with Trusted and SQL Server connections
    In 2005 it appears (just a guess this) that the connection string parameters are different

    For example using the Native Client Connector

    Provider=SQLNCLI.1;Password=ppp;Persist Security Info=True;User ID=uuu;Initial Catalog=Training;Data Source=svr-sql

    does not work

    BUT

    Provider=SQLNCLI.1;Password=ppp;Persist Security Info=True;User ID=uuu;Database=Training;Server=svr-sql

    works
    Andrew

Posting Permissions

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