Pass through datetime

  • Thread starter Thread starter Abrm
  • Start date Start date
A

Abrm

Hello,

my date time setting in access are in dutch dd/mm/yyyy.
In SQl we have a datetime column with the settings mm/dd/yyyy

I use an input form to set the dates.
My users are dutch and are used to work with the format dd/mm/yyyy
The dates are passed as a parameter to SQL.

When I run the pass-through with these parameters, I get no records
EXEC GetRecords '06/01/2009', '08/01/2009'

when I run this, I get the right result
EXEC GetRecords '01/06/2009', '01/08/2009'

When I make an access query it works fine. then it looks like access is
making a translation of the date format.

Is this an Access issue or more a SQL issue?
How can I resolve this?

Abrm
 
when I run this, I get the right result
EXEC GetRecords '01/06/2009', '01/08/2009'

When I make an access query it works fine. then it looks like access is
making a translation of the date format.

Is this an Access issue or more a SQL issue?
How can I resolve this?
Click to expand...

This is more of a SQL server issue. You want to feed dates in sql server
fomrat.

So, Assuming we set up one pass through query that you use for "exec" runs
from code in access side code, you would use the following approach:

Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset
dim strSQL as string
dim strD1 as string
dim strD2 as string

strD1 = "'" & format(MyDate1,"YYYY-MM-DD") & "'"
strD2 = "'" & format(MyDate2,"YYYY-MM-DD") & "'"

strSql = "EXEC GetRecords " & strD1 & "," & strD1

Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = strSql
qdfPass.Execute

If the above is returning records, then you can use the syntax of

set rst = qdfPass.Execute

So, you need to send the date format to sql server in a correct date format.
The above code snip also shows how you can execute any stored SQL proc on
the fly in code, and not have created a pass through query for each time you
need to do something inside of code. (the above simply sends that MyPass is
some arbitrary pass through query that you've made, I usually make two for
my application use them over and over for all of my code).

One will be called MyPass (this once used for executing store procedures
that don't return records), and then I make another one called MyPassRecs
(this one's for stored procedures or simply passing SQL as a pass through
but it does return records). once I've created these two procedures, then I
can use the simple code as above to send any SQL I want to this SQL server
with a very minimal of code. eg:

Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "Exece sp_MyProc"
qdfPass.Execute

Using the above approach is only 3 lines of code that allows you to run any
store procedure on the SQL side of your choice at runtime inside of
access....
 
Back
Top