Call Stored Procedure from Access (1 Viewer)

[Amritp]

I have a sql stored procedure "dbo.WiegenMain" as below:​

[COLOR=#000000]ALTER PROCEDURE [dbo][/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000][WiegenMain]  
[/COLOR][COLOR=#00008B]AS[/COLOR][COLOR=#000000]  
BEGIN 
 insert into dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]wiegen [/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Tag[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] Auftr[/COLOR][COLOR=#000000]ä[/COLOR][COLOR=#000000]ge_anzahl[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] Wiegeraum[/COLOR][COLOR=#000000])[/COLOR][COLOR=#00008B]
select[/COLOR][COLOR=#000000] dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]datepart2[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]BUCHUNG_BIS[/COLOR][COLOR=#000000])[/COLOR][COLOR=#00008B] as[/COLOR][COLOR=#000000] Tag[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] count[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]distinct AUFTRAGSNUMMER[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000] Kurztext [/COLOR][COLOR=#00008B]as[/COLOR][COLOR=#000000] Wiegeraum 
 from dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]tblZEITERFASSUNG inner join dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]tblBELEGUNGSEINHEIT 
[/COLOR][COLOR=#00008B]on[/COLOR][COLOR=#000000]  tblZEITERFASSUNG[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ID_BELEGUNGSEINHEIT[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000]tblBELEGUNGSEINHEIT[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ID where ID_BUCHUNGSART[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]9[/COLOR][COLOR=#00008B] and[/COLOR][COLOR=#000000] ID_BELEGUNGSEINHEIT [/COLOR][COLOR=#00008B]in 
[/COLOR][COLOR=#000000]([/COLOR][COLOR=#00008B]SELECT[/COLOR][COLOR=#000000] ID_BELEGUNGSEINHEIT FROM dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]tblPROZESS_BELEGUNGSEINHEIT  WHERE ID_PROZESS [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000])[/COLOR][COLOR=#00008B] and[/COLOR][COLOR=#000000] ABSCHLUSS[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#00008B] AND[/COLOR][COLOR=#000000] dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]datepart2[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]BUCHUNG_BIS[/COLOR][COLOR=#000000])=[/COLOR][COLOR=#000000] dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]datepart2[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]getdate[/COLOR][COLOR=#000000]())[/COLOR][COLOR=#000000] group by dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]datepart2[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]BUCHUNG_BIS[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000] Kurztext   [/COLOR][COLOR=#00008B]END[/COLOR]

I want to call this stored procedure in Microsoft Access using VBA, I have tried the following:​

First try:​

​

[COLOR=#00008B]Function[/COLOR][COLOR=#000000] InsertWiegen[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] conn [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] ADODB[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Connection 
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] rcs [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] ADODB[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Recordset [/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] strSQL [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] strDel [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#00008B]
Set[/COLOR][COLOR=#000000] conn [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] CurrentProject[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Connection strDel [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"DELETE dbo.wiegen WHERE Tag = '"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#00008B]Date[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'"[/COLOR][COLOR=#000000] strSQL [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"INSERT INTO dbo.wiegen ([Tag], [Aufträge_anzahl], [Wiegeraum]) "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] _          [/COLOR][COLOR=#800000]"SELECT dbo.datepart1([BUCHUNG_BIS]) as [Tag], count(distinct [AUFTRAGSNUMMER]) as [Aufträge_anzahl], Kurztext as [Wiegeraum] from dbo.tblZEITERFASSUNG inner join dbo.tblBELEGUNGSEINHEIT on tblZEITERFASSUNG.ID_BELEGUNGSEINHEIT=tblBELEGUNGSEINHEIT.ID where ID_BUCHUNGSART=9 and ID_BELEGUNGSEINHEIT in (SELECT ID_BELEGUNGSEINHEIT FROM dbo.tblPROZESS_BELEGUNGSEINHEIT WHERE ID_PROZESS = 3) and [ABSCHLUSS]=1 AND dbo.datepart1([BUCHUNG_BIS])= dbo.datepart1('"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#00008B]Date[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] group by dbo[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]datepart1[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000][BUCHUNG_BIS][/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000] [Kurztext][/COLOR][COLOR=#800000]"  
Set rcs = conn.Execute(strDel)  
conn.Execute (strSQL)  
Set rcs = Nothing  
conn.Close  
Exit Function  
End Function
[/COLOR]

Second try:​

​

[COLOR=#00008B]Function[/COLOR][COLOR=#000000] InsertWiegen[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] conn [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] ADODB[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Connection [/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] rcs [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] ADODB[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Recordset
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] cmd [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] ADODB[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Command [/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] strSQL [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#00008B]
Dim[/COLOR][COLOR=#000000] strDel [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#00008B]
Set[/COLOR][COLOR=#000000] conn [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] CurrentProject[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Connection 
[/COLOR][COLOR=gray]'Set cmd.ActiveConnection = CurrentProject.Connection[/COLOR][COLOR=#00008B]
Set[/COLOR][COLOR=#000000] cmd [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#00008B]New[/COLOR][COLOR=#000000] ADODB[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Command 
cmd[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ActiveConnection [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] conn 
cmd[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]CommandText [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"WiegenMain"[/COLOR][COLOR=#000000] 
cmd[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]CommandType [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] adCmdStoredProc  
strDel [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"DELETE dbo.wiegen WHERE Tag = '"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#00008B]Date[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'"[/COLOR][COLOR=#00008B]
Set[/COLOR][COLOR=#000000] rcs [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] conn[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Execute[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]strDel[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] 
cmd[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Execute  [/COLOR][COLOR=#00008B]
Set[/COLOR][COLOR=#000000] rcs [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]Nothing[/COLOR][COLOR=#00008B]
Set[/COLOR][COLOR=#000000] cmd [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]Nothing[/COLOR][COLOR=#000000]  
conn[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Close  [/COLOR][COLOR=#00008B]
Exit[/COLOR][COLOR=#00008B]Function[/COLOR][COLOR=#00008B]
End[/COLOR][COLOR=#00008B]Function
[/COLOR]

dbo.datepart2() Function:​

​

[COLOR=#000000]ALTER [/COLOR][COLOR=#00008B]function[/COLOR][COLOR=#000000] [dbo][/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000][DatePart2] [/COLOR][COLOR=#000000](@[/COLOR][COLOR=#00008B]date[/COLOR][COLOR=#000000] datetime[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] 
returns datetime 
[/COLOR][COLOR=#00008B]as[/COLOR][COLOR=#000000]  
begin  
[/COLOR][COLOR=#00008B]return[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]cast[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]CONVERT[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]varchar[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]12[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000]@[/COLOR][COLOR=#00008B]date[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]101[/COLOR][COLOR=#000000])[/COLOR][COLOR=#00008B]AS[/COLOR][COLOR=#000000] datetime[/COLOR][COLOR=#000000]))
[/COLOR][COLOR=#00008B]end
[/COLOR]

Problem: Both the methods give the error -
​

'The conversion of a char datatype to a datetime datatype resulted in an out-of-range datetime value.'
​

The stored procedure is working perfectly in SQL Server Management Studio, but raises error in Microsoft Access. I would really appreciate, if anyone can find out what is causing the problem..​

​

 

[boblarson]

Don't know if this will help but I made this procedure to run any stored procedure for my workplace:

' Procedure : RunSP
' Author    : larson
' Date      : 6/16/2009
' Purpose   : Runs a stored procedure with the parameters passed if parameters
'             are included and just a stored procedure if not.
'---------------------------------------------------------------------------------------
'
Function RunSP(mstrSPName As String, Optional mstrParams As String) As Boolean
' mstrparams should be made up of three types of things, each param as a pipe
' delimited data with each param delimted by a semi-colon.
' for example when calling this procedure -
' Call RunSP("spName", "@param1|adChar|" & Me!YourParam & ";@param2|adChar|" & Me!SecondParam
    Dim varsplit As Variant
    Dim varSplit2 As Variant
    Dim intParam As Integer
    Dim intCharLength As Integer
    On Error GoTo Errors
    OpenADOCon
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cnn
        .CommandText = mstrSPName
        .CommandType = adCmdStoredProc
        ClearParams
        If mstrParams <> "" Then
            varsplit = Split(mstrParams, ";", , vbTextCompare)
            For intParam = 0 To UBound(varsplit)
                varSplit2 = Split(varsplit(intParam), "|", , vbTextCompare)
                If UBound(varSplit2) > 0 Then
                Select Case varSplit2(1)
                Case "adInt"
                    Set prm = .CreateParameter(varSplit2(0), adInteger, adParamInput, , varSplit2(2))
                Case "adChar"
                    intCharLength = Len(Nz(varSplit2(2), 0))
                    Set prm = .CreateParameter(varSplit2(0), adChar, adParamInput, 255, varSplit2(2))
                Case "adDate"
                    Set prm = .CreateParameter(varSplit2(0), adChar, adParamInput, 255, varSplit2(2))
                Case "adLongChar"
                    Set prm = .CreateParameter(varSplit2(0), adLongVarChar, adParamInput, 64000, varSplit2(2))
                End Select
                .Parameters.Append prm
                Debug.Print "ParamName: " & prm.Name & Chr(9) & prm.Value
                End If
            Next
        End If
        .Execute
    End With
    CloseCon
ExitHere:
    Exit Function
Errors:
    Select Case Err.Number
    Case Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RunSP of Module basSQLOps", , CurrentDb.Properties("AppTitle")
        Resume ExitHere
        Resume
    End Select
End Function

We have a connection that has already been opened, and that is defined here:

Option Compare Database
Option Explicit

Private cnn As ADODB.Connection
Private cmd As ADODB.Command
Private prm As ADODB.Parameter
' Procedure : OpenADOCon
' Author    : larson
' Date      : 6/16/2009
' Purpose   : For a general ADO Connection
'---------------------------------------------------------------------------------------
'

Private Function OpenADOCon() As Boolean
On Error GoTo Errors

    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = "DSN=Barkley;"
    .Open
    OpenADOCon = (.State <> adStateClosed)
    End With

ExitHere:
     Exit Function
Errors:
     Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenADOCon of Module basSQLOps", , CurrentDb.Properties("AppTitle")
            Resume ExitHere
            Resume
     End Select
        
End Function