Connection string error Access 2010

Question

Recently updates to security has left our development environment for Access 2010 not being able to connect to SQL Server. We updated our DSN to have ODBC Driver 13 for SQL Server. We added the appropriate driver to Access and now links via the DSN work. However our OLEDB connection string does not work. This is how we connect:

With cn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = conServer --variable to hold database
    .Properties("Integrated Security").Value = "SSPI"
    .Properties("Initial Catalog").Value = ""  --removed for security reasons
    .Properties("Persist Security Info").Value = "False"
    .Open
End With

We get a 3265 SQL Error on the data provider value. How do I change references or provider information to get it to connect with SQL Server 2014? Can I do this in a different manner and not be ODBC dependent? Do I need additional dll's on the computer? Any information would be helpful. Thank you.

Disco Disco · Accepted Answer · 2018-06-21T23:02:53

Found that I couldn't connect via OLEDB but did connect using the following:

cn.ConnectionString = "ODBC;Data Source= file location of DSN;DRIVER=ODBC Driver 
13 for SQLServer; SERVER=name of server; Trusted_Connection=Yes; DATABASE=database 
name; AutoTranslate=No;"
cn.ConnectionTimeout = 30
cn.Open

Connection string error Access 2010

1 Answers