Cannot send ADO Date/Time parameter to a parameterized query

[Kevin Nechodom]

I have a table with 3 fields: UnitTime, Unit, and UnitCount
in an MDB database.

I have a query, qryWriteUnitOccupancy, that appends a record
to that table using 2 parameters: TimePoint, and PointUnit

I am using the following code to write a record:
For dUnitHour = FromDate To ToDate Step 1 / 24
With cmdUnit
.ActiveConnection = cnnUnit
.CommandType = adCmdText
.CommandText = "execute qryWriteUnitOccupancy"
Set prmUnit = .CreateParameter("TimePoint",
adDBTimeStamp, adParamInput, , Format(dUnitHour, "mm/dd/yyyy
hh:nn"))
.Parameters.Append prmUnit
Set prmUnit = .CreateParameter("PointUnit", adChar,
adParamInput, 5, sUnit)
.Parameters.Append prmUnit
.Execute
End With
Next dUnitHour

I run this, and no matter what the time is in dUnitHour, it
gets written to the table as a truncated date (no time). I
have stepped through this and watched the value of dUnitHour
step through hours, and I have run the query directly,
passing date/time and unit, and it writes just fine.

Any ideas?

Thanks,

Kevin Nechodom
University of Utah

[Douglas J. Steele]

Try adDate, rather than adDBTimeStamp. They're not the same thing, and
AFAIK, Access doesn't support adDBTimeStamp.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Kevin Nechodom" <Ke************@nospamhsc.utah.edu> wrote in message
news:d4**********@vegh.ks.cc.utah.edu...

I have a table with 3 fields: UnitTime, Unit, and UnitCount
in an MDB database.

I have a query, qryWriteUnitOccupancy, that appends a record
to that table using 2 parameters: TimePoint, and PointUnit

I am using the following code to write a record:
For dUnitHour = FromDate To ToDate Step 1 / 24
With cmdUnit
.ActiveConnection = cnnUnit
.CommandType = adCmdText
.CommandText = "execute qryWriteUnitOccupancy"
Set prmUnit = .CreateParameter("TimePoint",
adDBTimeStamp, adParamInput, , Format(dUnitHour, "mm/dd/yyyy
hh:nn"))
.Parameters.Append prmUnit
Set prmUnit = .CreateParameter("PointUnit", adChar,
adParamInput, 5, sUnit)
.Parameters.Append prmUnit
.Execute
End With
Next dUnitHour

I run this, and no matter what the time is in dUnitHour, it
gets written to the table as a truncated date (no time). I
have stepped through this and watched the value of dUnitHour
step through hours, and I have run the query directly,
passing date/time and unit, and it writes just fine.

Any ideas?

Thanks,

Kevin Nechodom
University of Utah

[Kevin Nechodom]

I wasn't aware of the limitations of adDBTimeStamp usage on
Access queries. Learn something new! Anyway, the problem
was the fact that I was trying to reuse an ADODB.Command
object in a loop, and it just spit out the same thing every
time!

Thanks!

Kevin

Douglas J. Steele<NOSPAM_djsteele@NOSPAM_canada.com>

4/25/2005 6:53:25 PM >>>Try adDate, rather than adDBTimeStamp. They're not the samething, and
AFAIK, Access doesn't support adDBTimeStamp.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Kevin Nechodom" <Ke************@nospamhsc.utah.edu> wrote
in message
news:d4**********@vegh.ks.cc.utah.edu...

I have a table with 3 fields: UnitTime, Unit, andUnitCount

in an MDB database.

I have a query, qryWriteUnitOccupancy, that appends a

record

to that table using 2 parameters: TimePoint, and

PointUnit

I am using the following code to write a record:
For dUnitHour = FromDate To ToDate Step 1 / 24
With cmdUnit
.ActiveConnection = cnnUnit
.CommandType = adCmdText
.CommandText = "execute qryWriteUnitOccupancy"
Set prmUnit = .CreateParameter("TimePoint",
adDBTimeStamp, adParamInput, , Format(dUnitHour,

"mm/dd/yyyy

hh:nn"))
.Parameters.Append prmUnit
Set prmUnit = .CreateParameter("PointUnit",

adChar,

adParamInput, 5, sUnit)
.Parameters.Append prmUnit
.Execute
End With
Next dUnitHour

I run this, and no matter what the time is in dUnitHour,

it

gets written to the table as a truncated date (no time).

I

have stepped through this and watched the value of

dUnitHour

step through hours, and I have run the query directly,
passing date/time and unit, and it writes just fine.

Any ideas?

Thanks,

Kevin Nechodom
University of Utah