2

I know it is possible to call a SQL Server stored procedure and populate a recordset in Access VBA by using the sp like a method on an ADODB Connection object:

Dim cnnTest As New ADODB.Connection
Dim rsTest As New ADODB.Recordset

cnnTest.ConnectionString = strMyConnectionString
cnn.Open
cnn.procMyTestProcedure param1, param2, rsTest

This will call stored procedure procMyTestProcedure passing prarmeters param1 and param2, returning the results to my local rsTest recordset. This all works fine and I have been using this technique for years. I have now come across a situation where I need to be able to pass a NULL as one of the parameters, but nothing I have tried so far seems to work. I have tried passing an empty string, the value Null and a local variable of type variant set to Null. None of these has worked. Does anyone know how to do this please? The parameter in the stored procedure is of type smalldatetime.

braX
  • 11,506
  • 5
  • 20
  • 33
Skippy
  • 1,595
  • 1
  • 9
  • 13
  • is your variable nullable in your proc? Regardless, a hack is this line of code right after your `as` in your proc to pass in a blank string but in the proc use it as a null `if @param='' set @param = NULL`. So basically passing in a blank string will turn into a NULL inside the proc. – S3S Jan 25 '17 at 17:22
  • Yes, I did think about doing that. I would have to redefine the parameter in the stored proc as string 'coz I can't pass an empty string to a datetime parameter. This would then have implications for validating a non-empty string before casting it as datetime. I just wondered if there was a 'proper' way of passing the null and avoiding the hassle of using a string instead. If not then I might go the long way round and use a Command object with defined Parameter objects (which can be set to Null). – Skippy Jan 25 '17 at 18:39
  • Ah i see what you mean about the datetime parameter. I'm not sure about how to do it because I don't use ADO but perhaps this helps http://stackoverflow.com/q/1747641/6167855 – S3S Jan 25 '17 at 18:47
  • Yes, that's going the Command.Execute route, explicitly declaring Parameter objects. I may end up doing this, but I prefer the more compact Connection.{proc_name} route. Just looks like this might not support passing Null parameters. – Skippy Jan 25 '17 at 20:10

1 Answers1

1

Just use null in VBA. It doesn't matter what data type you're using.

i.e.

    Dim parm1 As ADODB.Parameter
    Set parm1 = New ADODB.Parameter
    parm1.Direction = adParamInput
    parm1.Name = "StatisticsDate"
    parm1.Type = adVarChar
    parm1.Size = 50
    If IsEmpty(wsCell) Then
        parm1.Value = Null   'there is no DBNull - sorry
    Else
        parm1.Value = CDec(wsCell)
    End If
Fandango68
  • 4,461
  • 4
  • 39
  • 74
  • Thanks, tried this, but it fails with `Compile error: Variable not defined` and highlights `dbnull` – Skippy Aug 06 '19 at 19:04
  • @Skippy I've updated the answer. For datetime I use 0 but that is translated inside a function in sql, which makes it the "default" date in sql - 31/12/1899 12:00:00. If you need datetime logic, I'll update the answer. – Fandango68 Aug 08 '19 at 00:06