Ask Your Question
0

Connect to mssql (ODBC) database via Macro

asked 2018-02-25 12:11:11 +0100

rwestein gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-02-26 17:56:43 +0100

Ratslinger gravatar image

updated 2018-02-27 21:03:06 +0100

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.

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2018-02-26 18:55:59 +0100 )edit

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

BASIC Runtime error Sub-procedure or function procedure not defined

RobVld gravatar imageRobVld ( 2018-02-27 10:53:56 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2018-02-27 15:55:41 +0100 )edit

Works great now. Thank you very much!

rwestein gravatar imagerwestein ( 2018-02-28 21:40:24 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-02-28 21:52:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-25 12:11:11 +0100

Seen: 1,157 times

Last updated: Feb 27 '18