Sending dates to a procedure (1 Viewer)

[chrisdd]

Hi,

I'm sure there is a very simple solution to this, I just can't see it!

I've got a sql procedure that appends records between 2 dates from 1 table to another - there are 2 parameters @datefrom and @dateto, both have the date datatype

I'm using an access front end and have 2 text boxes (datefrom and dateto) to enter the dates on a form which I then need to send to the SQL procedure but no matter what I try I can't get it to work - this the the code I'm using to execute the procedure

Use database
exec schema.nonsk_appendtrial3 @datefrom=datefrom, @dateto=dateto

Any help would be much appreaciated!

 

[Minty]

You will need to format the dates into a format SQL Server understands, so concatenate a string together something like

Dim strSQL as String

strSQL = "exec schema.nonsk_appendtrial3 @datefrom = '" &  Format(Me.DateFrom,"yyyy-mm-dd") & "', @dateto = '" & Format(Me.DateTo,"yyyy-mm-dd") &  "'"
Debug.print StrSQL

Then use that string as your pass-through query to the server.

 

[arnelgp]

don't you think you need to do it the other way around
call the stored procedure from within access.
(1) How to pass parameter from Access pass-through to SQL stored procedure | Access World Forums (access-programmers.co.uk)

 

[chrisdd]

You will need to format the dates into a format SQL Server understands, so concatenate a string together something like

Dim strSQL as String

strSQL = "exec schema.nonsk_appendtrial3 @datefrom = '" &  Format(Me.DateFrom,"yyyy-mm-dd") & "', @dateto = '" & Format(Me.DateTo,"yyyy-mm-dd") &  "'"
Debug.print StrSQL

Then use that string as your pass-through query to the server.

Great thank you, much appreciated.

Only issue I've now got is connecting to the stored procedure in VBA! I've tried various bits of code but none seem to work, can you point me in the right direction please?

 

[Minty]

You don't connect to a stored procedure as such, you get it to either carry out an action or return records that you then assign to something.

The best route is to save a generic passthrough query and edit it on the fly - I use a module that does this.
Create a pass-through query - I call it qPT_Generic

Then simply modify it to accommodate your needs - there is a routine I use here;

Problem running passthrough recursive query as QDF in VBA

I have a SQL recursive query (below), which runs fine on SQL server and also runs fine when I create a QDF in in MS Access navigation window. But when I try to create it in VBA I get an error "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." The query...

www.access-programmers.co.uk

That I simply call to run or set the query to return records using a switch.
You can then use the query (qPT_Generic) as the record source for your form or report.

Bear in mind that this is not an editable recordsource, but can be used to drive updates in other tables.

 

[chrisdd]

Hi,

Thanks for this but it returns Run time error 3146 ODBC call failed when I use


Dim db As DAO.Database
Set db = CurrentDb
With db.CreateQueryDef("")
.Connect = db.TableDefs("WasteReturnQryData").Connect
.SQL = "exec schema.database.appendtrial3 @datefrom = '" & Format(Me.DateFrom, "yyyy-mm-dd") & "', @dateto = '" & Format(Me.DateTo, "yyyy-mm-dd") & "'"
.ReturnsRecords = False 'this will avoid error 3065
.Execute dbFailOnError
End With

Any futher help would be much appreciated

 

[chrisdd]

Thanks - I'm working on this method also!

 

[Minty]

To test the process create your sql string separately and the debug.print it as I did in the first example.
You should be able to paste that into SSMS and it will work or return a more meaningful error.

    Dim db As DAO.Database
    Dim strSql As String
    
    Set db = CurrentDb
    strSql = "exec schema.database.appendtrial3 @datefrom = '" & Format(Me.DateFrom, "yyyy-mm-dd") & "', @dateto = '" & Format(Me.DateTo, "yyyy-mm-dd") & "'"
    
    Debug.Print strSql      '''''''''' This wil appear in the Immediate window - Press ctrl+G to view it in the VBA editior
    
    With db.CreateQueryDef("")

        .Connect = db.TableDefs("WasteReturnQryData").Connect

        .SQL = strSql
        .ReturnsRecords = False 'this will avoid error 3065

        .Execute dbFailOnError

    End With

 

[chrisdd]

Oh my word - can't believe how long I've been looking at this when the solution may have been staring me in the face! You're a star - thankyou!