access front end and sql server back end

[seeker]

Our front ends for our data is in access and has all the queries, forms, and
reports with linked tables to a sql server back end. My question is when i
write select, update, insert into, or even delete queries in the front end do
I use # # for dates or ' ' for dates?

 

[Dale Fye]

As I recall, this depends on your query.

If you are updating a linked table, I believe the appropriate delimiter is #

However, if you are writing a Pass-thru query, I believe the delimiter is
the apostrophe.

HTH
Dale

 

[John W. Vinson]

Our front ends for our data is in access and has all the queries, forms, and
reports with linked tables to a sql server back end. My question is when i
write select, update, insert into, or even delete queries in the front end do
I use # # for dates or ' ' for dates?

If you're using a linked table, the query will be in Access syntax - with a #
date delimiter, your choice of ' or " delimiters for text, etc. If you use a
PassThrough query you will use SQL/Server syntax.

 

[seeker]

Thank you to the two of you. Does the syntax make a difference if the sql
script is embedded in the vba that is run by a button on a form? When the
sql script is embedded within vba should the dates be #??/??/??# or
'??/??/???? 00:00:00'

 

[John W. Vinson]

Thank you to the two of you. Does the syntax make a difference if the sql
script is embedded in the vba that is run by a button on a form?

No. It's still processed by the JET/ACE database engine and automatically
translated to SQL/Server syntax for you; use the JET conventions.

 

[seeker]

ok i have an update query as follows
update los_service set los_end_time = #10:15:00 am#, nbr_of_minutes = 105
where member_number = 942 and los_service_date = #2/5/2009# and los_beg_time
= #08:30:00 am# and los_end_time = #10:30:00 am#

this does not update the table I have tried changing time to hh:mm AM/PM
and that does not work either. What is wrong????

 

[John W. Vinson]

ok i have an update query as follows
update los_service set los_end_time = #10:15:00 am#, nbr_of_minutes = 105
where member_number = 942 and los_service_date = #2/5/2009# and los_beg_time
= #08:30:00 am# and los_end_time = #10:30:00 am#

this does not update the table I have tried changing time to hh:mm AM/PM
and that does not work either. What is wrong????

Presumably there is no record in the table where those criteria are met
exactly. A Date/TIme field is actually a Double Float count of days and
fractions of a day (times) since midnight, December 30, 1899; the Double has
an accuracy of microseconds, even though you can only display a time to the
second.

What do you see if you just do a select query

SELECT los_beg_time, los_end_time from los_service
WHERE member_number = 942 and los_service_date = #2/5/2009#;