Solved Connecting to sql server (1 Viewer)

[KitaYama]

I'm working on a Microsoft Visio project and I need to open a recordset to a table in sql server, read the data and work on each record.
I've tested the result on several PCs and all are fine, except only one PC.
This specific PC throws Runtime error: 3706 "Provider can not be found" on .Open line in following script:

OpenConnection:
    Dim Con As New ADODB.Connection
    stCon = "Provider=MSOLEDBSQL;Server=Server;Database=MyDB;Integrated Security=SSPI;"
    With Con
        If .State = 0 Then
            .ConnectionString = stCon
            .ConnectionTimeout = 10
            .Open
        End If
    End With
Return

I've downloaded and installed Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) ver 19 from here, still the same error.

If I change the provider to the following, the script runs and does the job.

stCon = "Provider=SQLOLEDB;Server=Server;Database=MyDB;Integrated Security=SSPI;"

Why even after installing the latest driver, the PC shows that error?

Thanks.

 

[tvanstiphout]

I always use the ODBC Driver 18 for SQL Server from here.
The connection string for Windows Authentication is: "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=[ServerName];DATABASE=[DatabaseName];Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes"

 

[KitaYama]

I always use the ODBC Driver 18 for SQL Server from here.
The connection string for Windows Authentication is: "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=[ServerName];DATABASE=[DatabaseName];Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes"

All the PCs that pass that connection, have ODBC Driver 18 installed.
I thought installing MSOLEDBSQL ver 19 would solve the problem. Which unfortunately didn't.

I'll give your connection string a test. But unfortunately the PC is in use and I can not work on it until Saturday. I'll test and report back.
Thanks for the advice.

 

[KitaYama]

I always use the ODBC Driver 18 for SQL Server from here.
The connection string for Windows Authentication is: "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=[ServerName];DATABASE=[DatabaseName];Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes"

I downloaded version 18 from your link. The installer asked me that a previous version on this machine has been found and weather I want to update it. I selected yes. The install finished successfully.

    Dim Con As New ADODB.Connection
    stCon = "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=Server;DATABASE=MyDB;Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes"
    With Con
        If .State = 0 Then
            .ConnectionString = stCon
            .ConnectionTimeout = 10
            .Open
        End If
    End With

Now the error is:
Runtime error : 2147467259(800004005) [Microsoft][ODBC Driver Manger] Data source or the driver can not be found.

Still the following string can open the connection:

stCon = "Provider=SQLOLEDB;Server=Server;Database=MyDB;Integrated Security=SSPI;"

 

[KitaYama]

I just noticed that we have the same (the following) connection string for Microsoft Access linked tables to the same sql server and the same database and it works just fine in the PC that shows error.
It seems that it's a Visio problem.

stCon = "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=Server;DATABASE=MyDB;Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes"

 

[sonic8]

Beginning a connection string with "ODBC;" is Access-specific for linked tables. I don't think this should be there in any other scenario.

 

[Josef P.]

For the connection test, you could use the ancient SQL-OLEDB provider, which I believe is included with every version of Windows.

Microsoft OLE DB Provider for SQL Server Connection Strings - ConnectionStrings.com

Connection Strings using sqloledb for connections to SQL Server, SQL Server 2000 and SQL Server 7.0.

www.connectionstrings.com

Example:
Provider=SQLOLEDB;Data Source=YourSqlServer;Initial Catalog=YourDataBase;Integrated Security = SSPI

New data types from newer SQL Server versions are not supported.

 

[KitaYama]

Beginning a connection string with "ODBC;" is Access-specific for linked tables. I don't think this should be there in any other scenario.

This explains why the following fails:

stCon = "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=Server;DATABASE=MyDB;Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes

But still I wonder why this one can not open the connection:

stCon = "Provider=MSOLEDBSQL;Server=Server;Database=MyDB;Integrated Security=SSPI;"

 

[KitaYama]

For the connection test, you could use the ancient SQL-OLEDB provider, which I believe is included with every version of Windows.

Microsoft OLE DB Provider for SQL Server Connection Strings - ConnectionStrings.com

Connection Strings using sqloledb for connections to SQL Server, SQL Server 2000 and SQL Server 7.0.

www.connectionstrings.com

Example:
Provider=SQLOLEDB;Data Source=YourSqlServer;Initial Catalog=YourDataBase;Integrated Security = SSPI

New data types from newer SQL Server versions are not supported.

Yes, that's true. As I mentioned in #1 I had to replace the connections string to SQLOLEDB to be able to open the connection.

I just noticed that the PC that fails to connect with MSOLEDBSQL, is running on Windows 10.
All other PCs with no problem are on Windows 11.
Maybe it's an some weird OS problem.

 

[sonic8]

@KitaYama , it might help diagnosis to do the following:
Create an empty text file, name it: Test.udl
Duoble click that file in Windows Explorer.
This will bring up the OLE-DB Data Link Manager. There you see a list of all installed OLE-DB providers.
The old provider for SQL Server will be listed as "Microsoft OLE DB Provider for SQL Server", the new one will be listed as "Microsoft OLE DB Driver for SQL Server" (v18) or "Microsoft OLE DB Driver 19 for SQL Server" (v19).
You can then choose the desired provider to create a new connection in the Data Link Manager and directly test the connection (like with the ODBC DS Manager).
If you close with "OK" and then open the test.udl with a text editor, you'll see the OLE DB connection string.

 

[sonic8]

All the PCs that pass that connection, have ODBC Driver 18 installed.
I thought installing MSOLEDBSQL ver 19 would solve the problem. Which unfortunately didn't.

That's probably because the different provider versions have different names.

Version 18: Provider=MSOLEDBSQL;
Version 19: Provider=MSOLEDBSQL19.1;

 

[KitaYama]

@KitaYama , it might help diagnosis to do the following:
Create an empty text file, name it: Test.udl
Duoble click that file in Windows Explorer.
This will bring up the OLE-DB Data Link Manager. There you see a list of all installed OLE-DB providers.
The old provider for SQL Server will be listed as "Microsoft OLE DB Provider for SQL Server", the new one will be listed as "Microsoft OLE DB Driver for SQL Server" (v18) or "Microsoft OLE DB Driver 19 for SQL Server" (v19).
You can then choose the desired provider to create a new connection in the Data Link Manager and directly test the connection (like with the ODBC DS Manager).
If you close with "OK" and then open the test.udl with a text editor, you'll see the OLE DB connection string.

I'll follow the steps as soon as I'm allowed to work on it.
Thanks for the details.

 

[Isaac]

I use ADO a lot, here is one in vbscript that works for me:

connStr = "Provider=SQLOLEDB;" & _

"Data Source=servername;" & _

"Initial Catalog=databasename;" & _

"Integrated Security=SSPI;"

 

[tvanstiphout]

I downloaded version 18 from your link. The installer asked me that a previous version on this machine has been found and weather I want to update it. I selected yes. The install finished successfully.

    Dim Con As New ADODB.Connection
    stCon = "ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=Server;DATABASE=MyDB;Trusted_Connection=Yes;TrustServerCertificate=Yes;Encrypt=Yes"
    With Con
        If .State = 0 Then
            .ConnectionString = stCon
            .ConnectionTimeout = 10
            .Open
        End If
    End With

Now the error is:
Runtime error : 2147467259(800004005) [Microsoft][ODBC Driver Manger] Data source or the driver can not be found.

Still the following string can open the connection:

stCon = "Provider=SQLOLEDB;Server=Server;Database=MyDB;Integrated Security=SSPI;"

I don't use this connection string to open an ADODB connection, but for the Connect property of a linked Tabledef object.

For an OLEDB connection I use OLEDB19 driver from here. The connection string is:
Provider=MSOLEDBSQL19;Data Source=[server];Initial Catalog=[database];Integrated Security=SSPI;Persist Security Info=False;Trust Server Certificate=True;Server SPN="";Access Token="";Host Name In Certificate="";Use Encryption for Data="False"

 

[KitaYama]

Sometimes I hate our IT team.
This morning I came to my desk early to run some tests, especially the one @sonic8 suggested in #10, only to find out that IT had replaced all the Windows 10 machines with brand-new Windows 11 PCs overnight, including the one that had the problem.
The new Windows 11 machine has no issue with the connection string.

If I had known they were planning this, I wouldn’t have wasted your time (or mine) on a problem that would be solved in a short time.
While I’ll probably never know what the original issue was, at least I learned a lot from all the responses I received.

For now, I’ll mark the thread as solved.
Thanks to everyone who shared their knowledge and experience.

 

[dalski]

No it was a good post thanks KitaYama, sound OS related to watch out for Win 10 here if any of us come across a similar issue.

 

[The_Doc_Man]

In my military contractor days, we would say that this problem was OBE - "overcome by events." @KitaYama - The post was quite legitimate, describing a real problem. The event that overcame the problem was the external interference of your IT group. You have no reason to apologize or feel that you did ANYTHING wrong. In fact, if any wrong was done, it was the IT group's unannounced change to what your folks were doing and how they were doing it. Having no warning and therefore no prep time means that they had no idea of what projects, if any, they would disrupt.