Connection Strings for VBA and MSOLEDB

They recently gave me a gig, which amongst setting up some databases included fixing up some spreadsheets to talk to the new database. This included fixing connection strings in VBA.

Background to the story the client wanted to move some systems from using Oracle DB as a back end to SQL Server as the data store.

Now two years ago I had never written from scratch a VBA script for Office Automation. It has now become a bit a skill set I have developed. I am not a fan of some of what I see. Not the code it’s good. More people have built a complete application using VBA and Excel. This is bad because it doesn’t deal well with governance. Potentially has security issues in that a username and password are available in the sheet,. Connection details including a host name are all potentially at risk.

On to the bit, you came to get. Many of you are aware Microsoft now endorsed OLEDB as the future. I am not going into the why, but it means you now have a new driver. We can find more info and the driver file from this page. Grab the driver and get it installed. Now the old OLE driver used the SQLOLEDB provider in connection strings long with the SQLNCLI driver are still deprecated. The new driver is using the MSOLEDBSQL Provider.

I needed to update stings, and I was needing to form the string based on limited information, connectionstrings.com wasn’t helping it’s not listed there as yet.  Noone else was showing details of a connection string to an Azure DB which was what I wanted to do.

I have access to the new VBA book “Microsoft Excel 2019 VBA and Macros, First Edition”, it disappointed me that the book didn’t show usage of the new driver, it has a copyright date of 2019 and they had released the driver in 2018

I will save you the hassle of finding this out for yourself. Here is the big reveal:

Dim UserName As String
Dim Passwd As String
Dim PType As String
Dim SqlString As String
Dim ReturnArray

Dim sConnectionString As String
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset


 PType = "MSOLEDBSQL"
 Hostname = "tcp:mytestdb.database.windows.net"
 InitCatalog = "Adventureworks"
 UserName = "dbtest@myytestdb"
 Passwd = "somecrazychars" 
 Trusted = "yes"


 sConnectionString = "Provider = " & PType & "; " _
                        & "Data Source = " & Hostname & "; " _
                        & "Initial Catalog = " & InitCatalog & "; " _
                       & "User ID = " & UserName & "; " _
                        & "Password = " & Passwd & "; " _
                        & "Trusted_Connection = " & Trusted
                     
SqlString = "SELECT TOP (5) *   FROM [SalesLT].[Customer] "
con.ConnectionString = sConnectionString
con.Open
rst.Open SqlString, con
ReturnArray = rst.GetRows


Also, don't forget about the issues with non-alphanumreic chars. Check out the Strong Passwords


Ok, Enjoy.

See ya round

Peter