Excel VBA ADODB

[Alexander Szigetvary]

Hi NG!

I'm trying to access a MS SQL database using ADODB. I do the same in VB6
project without any problems.

I'm trying to execute a stored procedure, which returns a sinfle value.

Here is the code I'm using:

Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim DS As ADODB.Recordset
On Error GoTo Err
cn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=MyServer;Initial
Catalog=MyDataBase;Integrated Security=SSPI"
cn.Open
Set cmd = New ADODB.Command
cmd.CommandText = "NeueNummer"
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = cn
Set DS = cmd.Execute ** DS stays closed, for a reason I don't
understand.

Range("A4").Select
Selection.NumberFormat = "000000"
ActiveCell.FormulaR1C1 = DS!Nummer
DS.Close
cn.Close

Err:
MsgBox Err.Description, vbCritical
If cn.State = adStateOpen Then cn.Close


Any ideas?

TIA

Alex

 

[papou]

Hello
You need a reference to Microsoft Activex Data Objects Library
This may be added through Tools References in the VBA Editor menu.

HTH
Cordially
Pascal

 

[Alexander Szigetvary]

I have that of course.

Alex

 

[papou]

Sorry didn't notice your comment in the VBA code.
Have you tried debugging?
Try and replace adCmdStoredProc with its constant value (0 or 1 ?) in this
line:
cmd.CommandType = adCmdStoredProc

HTH
Cordially
Pascal