Connect to mssql (ODBC) database via Macro

[rwestein]

How can I connect to a mssql ODBC database via Macro? (in Windows)
When creating a database with a ODBC msql database that is working fine. But I only need to extract some info for the mssql db, So I’m trying to connect to the database via a macro.

I used the method to connect to a Paradox database using ODBC by Andrew Pitonyak’s AdrewBase document… But I need to use the login credentials to connect to the mssql DB…

And that is where I get lost…

/Rob

[Ratslinger]

Hello,

While tested on a Linux system with a MySQL ODBC connection, the same process should work for you.

You just need to add user & password:

Dim oParms() As New com.sun.star.beans.PropertyValue
AppendProperty(oParms(), "user", sUser)
AppendProperty(oParms(), "password", sPass)

For security, provide input box to enter actual User & Password info.

Then also change one line in routine.

From:

oCon = oManager.getConnection(sURL)

To:

oCon = oManager.getConnectionWithInfo(sURL, oParms())

Edit 2/27/18:

This sub also needs to be added:

Sub AppendProperty(oProperties(), sName As String, ByVal oValue)
  AppendToArray(oProperties(), CreateProperty(sName, oValue))
End Sub

which uses this Function:

Function CreateProperty(sName$, oValue) As com.sun.star.beans.PropertyValue
  Dim oProperty As New com.sun.star.beans.PropertyValue
  oProperty.Name = sName
  oProperty.Value = oValue
  CreateProperty() = oProperty
End Function

and this Sub:

Sub AppendToArray(oData(), ByVal x)
  Dim iUB As Integer  'The upper bound of the array.
  Dim iLB As Integer  'The lower bound of the array.
  iUB = UBound(oData()) + 1
  iLB = LBound(oData())
  ReDim Preserve oData(iLB To iUB)
  oData(iUB) = x
End Sub

I apologize for the inconvenience. All of the above is taken from the aforementioned ‘AndrewBase’ document.
If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[Ratslinger]

As an added note, instead of input box, use a dialog. Can then use a text box for the password and set the property Password character to mask it.

[RobVld]

I receive an error on “AppendProperty(oParms(), “user”, sUser)”:

BASIC Runtime error
Sub-procedure or function procedure not defined

[Ratslinger]

@Robvld Sorry for the problem. I forget that Pitonyak has used many other routines called from the samples. My testing has all those routines in another library which caused me to miss it. Have edited answer to include needed sub.

[rwestein]

Works great now. Thank you very much!

[Ratslinger]

You are welcome & again sorry for the missing subs. I’ve got to keep aware of Pitonyak’s wonderful examples.