question

SamCoburn-4056 avatar image
0 Votes"
SamCoburn-4056 asked SamCoburn-4056 commented

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

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!!

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you! I didn't know ADO was old. I was ignorant. I'll continue to use ODBC.

That sure makes things a lot easier for me.

Thanks again!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered SamCoburn-4056 commented

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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

Access is not a .NET application is it? My assumption is that Sam is indeed looking into the very old ADO.

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.

Microsoft certainly do not recommend OLE DB over ODBC in general. Whether to use OLE DB or ODBC depends on the context. In the case of Access, I say ODBC is the better choice, since you can use a modern driver.

I did not want to suggest using a more modern OLE DB driver to keep things simple. But if you are using MSOLEDBSQL with ADO, you also need to add DataTypeCompatibility=80 to the connection string.

To be even more modern, you should go with the MSOLEDBSQL19 provider. With this provider, connections are by default required to be encrypted, which can give you problems if your certificates are not in order. I kind of have the feeling that this is a little over the top for Sam for the moment.

0 Votes 0 ·

Yea, it was ADO and not ADO.Net. I just didn't know any better. Thanks for your help!

0 Votes 0 ·