Ask Your Question

How do I connect to SQL server in a macro in Windows

asked 2019-01-11 01:37:42 +0200

benttriker gravatar image

Be gentle. My coding life began doing operations research programming in Fortran. I have a form in a macro and wish to use info from a user to send back a listbox of items while in calc. My stumbling block is the connect method to SQL via ODBC. The DB is registered in base via ODBC but I have no clue as to the methods available to do this in the macro. Pointing me to to the proper OO documention or an example would be appreciated. benttriker.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-01-11 06:08:32 +0200

Ratslinger gravatar image


There are a few methods to connect to a database from Calc.

You have stated that you have a DB already registered. The connection to the base file is easy using the registered name.

Sub ConnectDB
    Dim oContext As Object
    Dim oDB        As Object
    Dim oCon      As Object
    oContext = CreateUnoService("")
    oDB = oContext.getByName("Registered_Name_Here")
    oCon = oDB.getConnection("User_Name_Here","Password_Here") 
End Sub

oCon is the connection you can use to perform your SQL with such as:

  oStatement = oCon.CreateStatement()
  oResult = oStatement.executeQuery(sSQL)
  Do While
      Print oResult.getString(1)

If you want to bypass the Base file and connect directly to the Database, then the basic directions can be found in the AndrewBase document found here -> Andrew Pitonyak Documents. Look at page 106 - 8.6.6. Paradox using ODBC. This was the basis for my answer on this post -> Connect to mssql (ODBC) database via Macro.

edit flag offensive delete link more


Thanks for the pithy, correct and gentle answer. Also the link to the Pitonyak docs is greatly appreciated. As per the proverb, better to be taught to fish than given a fish. Hope this may also be helpful to others. benttriker

benttriker gravatar imagebenttriker ( 2019-01-11 14:52:12 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2019-01-11 01:37:42 +0200

Seen: 89 times

Last updated: Jan 11