I need help making an ado connection string from ms access to sqlexpress 15.

Question

I've been trying to figure out a connection string for a couple days now. I'm new to ado. I've been using odbc for years, but I want to update the old colde.
Please also let me know if I need quotation marks and apostrophes and does there need to be a semi-colon at the end, etc.

An example would be very helpful. I've look at connections.com, and many other places only to get

Here's a couple ones I've tried:

Set cn = "Provider='SQLOLEDB';Data Source='.\SQLEXPRESS';Initial Catalog='tblUser';Integrated Security='SSPI';"
Set cn = "Provider=SQLOLEDB; Data Source=CCI-2021\SQLExpress;Database=ADOLearn;Integrated Security=sspi;"

I'd like to use sql authentication, but windows would be fine.

Thanks!!

Answer 1

I've been trying to figure out a connection string for a couple days now. I'm new to ado. I've been using odbc for years, but I want to update the old colde

With ADO - an API which has not been touched for twenty years? (And which was a piece of rubbish already when it was new.) I would recommend that you stick to ODBC.

Anyway, a typical connection string would be:

Set cn = "Provider=SQLOLEDB;Server=MACHINE\SQLEXPRESS;Database=ADOLearn;Trusted_connection=yes"   ' Windows authentication.
Set cn = "Provider=SQLOLEDB;Server=MACHINE\SQLEXPRESS;Database=ADOLearn;UID=NameOfUser;PWD=TheSecret"   ' SQL authentication.

It's a little confusing, because different APIs uses different keywords for the same thing, so it easy to accidently use something which does not work in a specific context.

One more remark: SQLOLEDB is a very old provider, which you normally should not use with SQL Server, because you are missing out on support for many new features. But since ADO is so old itself, you can just as well use SQLOLEDB, as ADO would only be confused by what is coming through.

Answer 2

Hi,

I'm new to ado

Are you sure that you speak about ADO and not ADO.Net?

ADO is a short for "ActiveX Data Objects". It is very old database provider which is usually used in C, C++ and Visual Basic for example and not recommended for .Net applications.

ODBC is not a specific driver name but a type of drivers. There is an ODBC driver for most data sources (different driver for different databases). For SQL Server you can use the "Microsoft ODBC Driver for SQL Server"

For most case, I would not recommend ether of these and you should use the "Microsoft OLE DB Driver for SQL Server" (MSOLEDBSQL) which is Microsoft recommendation.

In this case, check the following link to get the option of the connection strings which you can use:

https://www.connectionstrings.com/ole-db-driver-for-sql-server/

For example

// Standard security
Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;
// Trusted connection
Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
// Connecting to an SQL Server instance
Provider=MSOLEDBSQL;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;

Check the link for more options