Access to sql server database

[Gaetanopr]

Hello, I have a macro that connects to a database that has a database in sql server as data sources, I can access it but at the first access I am asked to enter the database password although the user and password are indicated in the code.
How can I solve?
Thanks

Code: Select all

sSQL = "SELECT * FROM  CONTI"

Dim oDBC As Object
Dim oBD As Object
Dim oConexion As Object
Dim oDeclaracion As Object
Dim oResultado As Object
Dim sBaseDatos As String
Sh = Doc.Sheets.getByName(NomeF)
Sh.Unprotect("")
LR1 = LastRowCol(Sh, 0) + 1 
oRange = Sh.GetCellRangeByName("A4:A" & LR1)

    sBaseDatos = "EXample"
	oDBC = createUnoService("com.sun.star.sdb.DatabaseContext")	
	If oDBC.hasByName( sBaseDatos ) Then
		oBD = oDBC.getByName( sBaseDatos )
		oConexion = oBD.getConnection("sa","password")      '    user and password 
		oDeclaracion = oConexion.createStatement()
	    oResultado = oDeclaracion.executeQuery( sSQL)
	 
	    If oResultado.Next  Then
	       Dim mOpcBD(3) As New "com.sun.star.beans.PropertyValue"
		   mOpcBD(0).Name = "DatabaseName"
           mOpcBD(0).Value = sBaseDatos
           mOpcBD(1).Name = "SourceType"
           mOpcBD(1).Value = com.sun.star.sheet.DataImportMode.SQL
           mOpcBD(2).Name = "SourceObject"
           mOpcBD(2).Value = sSQL
           Sh.getCellByPosition(0, LR1).doImport( mOpcBD() ) 
        Else 
           msgbox ("There is no data to recover") 
        End If   

[Villeroy]

This is very easy to do without macro.
Start the Base wizard, connect a Base document to your existing database and register it (one check box in the wizard).
Add SQL queries as needed. "Direct SQL mode" accepts the syntax of your database. "Parsed mode" requires a Base specific syntax.
Open a Calc document.
menu:View>Data Sources, expand the tree of your registered database.
Drag the icon of a query or table into the document.
The resulting database range (see menu:Data>Define...) is linked to the table or query.
menu:Data>Refresh updates the query.
There are more configuration options and an auto-refresh timer: [Tutorial] Using registered datasources in Calc