Connection String Between MS Access 2016 and SQL Server 2012

[mad-tita]

Connection String Between MS Access 2016 and SQL Server 2012 to call stored procedure with parameters using a command object in VBA?

At work, I have a couple of Access Project programs developed in MS Access 2000 as .ade/.adp formats, and they are still usable in our current MS Access 2007 and SQL Server 2012 environment. With our move to MS Access 2016, I began to convert one of them to just be the "regular" .accdb format.

I began with creating an ODBC connection (System DSN), and then, I found out that in order to call in VBA my stored procedure with parameters via an ADODB command object, I had to have a second connection to SQL Server. I looked on the Internet and after several trials, I used this connection string and it worked in my test environment of MS Access 2007 and SQL Server 2016:
"Provider=MSOLEDBSQL;Server=OurSQLinstance;Database=OurDBname;Integrated Security=SSPI;"

Then, in order to limit the changes that I have to make, I reduced the scope to just remaining on SQL Server 2012 but also tried using MS Access 2016. The above connection string does not work in that environment combo, so I have been looking on the Internet for the format that will work. I know it would benefit me to read up and learn how to use connections and connection strings but due to deadline pressures and limited time, I am putting this out to see if there is a connection string format that would magically work for our environment combination (MS Access 2016 and SQL Server 2012).

If there is a link to an Internet resource related to ADODB connection strings for matrix combinations of SQL Server and Access version that anybody on the forum can provide, I would be grateful for that, too.

Thank you.

 

[theDBguy]

Have you checked?

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

 

[mad-tita]

Actually, yes--thanks much--but I had a hard time understanding how to find the one I need. I will give it another go, although a quick scan doesn't seem to yield anything about Access 2016.

 

[theDBguy]

Actually, yes--thanks much--but I had a hard time understanding how to find the one I need. I will give it another go, although a quick scan doesn't seem to yield anything about Access 2016.

I could be wrong, but I think connection strings have to do more with the target RDBS than the source file. Besides, if you are converting the ADPs to ACCDBs, which means you won't be using ADPs anymore, then I think there may be an easier way than trying to make your old code work in the new database.

First, have you already linked the SQL Server tables to the new ACCDB file?

 

[sonic8]

I could be wrong, but I think connection strings have to do more with the target RDBS than the source file.

Neither! Connection strings are passed to the ODBC or OLEDB driver/provider. The driver is responsible for processing the connection string and establishing the connection. Of course, drivers must not expect arbitrary connection string formats, but must adhere to ODBC or OLEDB standards. However, drivers are allowed to implement custom extensions to the default connection string specification.

 

[Minty]

If you can link to the remote SQL Tables and edit/work with the tables, simply copy the connection string that the table link uses.

I would definitely use version 17 or 18 of the dedicated MS SQL server driver from here.

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server

Download the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.

docs.microsoft.com

 

[sonic8]

If you can link to the remote SQL Tables and edit/work with the tables, simply copy the connection string that the table link uses.

That doesn't work. Linked tables use ODBC while ADODB commands use OLEDB. The connections strings will not match.
Edit: Actually, this is the primary problem with the non-working connection string posted above. Server and Database are ODBC connection string properties, which should be replaced by Data Source and Initial Catalog for an OLEDB connection string.

 

[Minty]

@sonic8 Thank you for clarifying.
The last time we converted an ADP I think we simply manoeuvred all the functionailty into pass-throughs where the table connection string would work, as it was the simplest way, but my memory of the whole process is pretty vague.