Excel VBA ADODB

  • Thread starter Thread starter Alexander Szigetvary
  • Start date Start date
A

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
 
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
 
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
 
Back
Top