Accessing an Access DB from Outlook via VBA

Question

The situation:

In Outlook I get a message from a server. The content of the message needs to be put into an Access db. But, there may not exist another message with the same date. So, I need to look into a db if there is already a message with the same date and time. If there exists one, then it needs to be replaced and otherwise the message needs to be added to the database. The database contains a list of current positions from the vehicles on the road.

The problem:

I have problems to compare a date time with a date time in an Access DB via VBA. The query I use returns no records but there is a record in the database.

This is the query I use:

  adoRS.Open "SELECT * FROM currentpositions WHERE ((currentpositions.  
  [dateLT])=" & "#" & date_from_message & "#" & ")", adoConn, adOpenStatic, adLockOptimistic

Second I need to now what the result is of that query.

How can I determine the number of records that my query gives me?

Thanks camastanta

Answer 1

If date from message is a date, try:

"SELECT * FROM currentpositions WHERE ((currentpositions.
[dateLT])=#" & Format(date_from_message,"yyyy/mm/dd") & "#)"

That is, format the date to year, month, day order.

Does date_from_message have a time attached? Does dateLT have a time? If so, it may be best to exclude the time, if only date is relevant.

You can get the number of records returned from the RecordCount property, but you need to use the right cursor type:

adOpenKeyset: 1
adOpenStatic: 3

I note you are using adOpenStatic, so:

adoRS.Recordcount

Should suit.

Answer 2

Thanks for that: the format is working but I still have the problem with the time. I need to compare a date/time.

I use the date/time from the message-body to compare it with a date/time in an access database. If there exists a record with that time then I have to do nothing and otherwise I need to be add the message-body the database.

In fact I use more fields to determine if the record is existing but I have only problems with the date/time-field. The message body is like this: 10125,Road Runner,16/12/2009 18:28:00,(16/12/2009 17:28:00 UTC),11,51.32400000,3.20000000, Position report (with fields:db-number,name,date local,date utc,status,position,other)

Regards Camastanta