Access .mdb

[Nakia]

Hi,

I am in the process of building a small appliaction that will interact with a Micrsoft Access Database.
I have successfully connected through ODBC and can fetch data fine but now am at the stage of wanting to update some records.

Please note: this is my first attempt at using any sort of database so if I have done this all wrong please guide me

I have constructed the below code to update some records but I keep getting an error when I execute the code
"[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'VIMSTime=2013-04-07 13:25:47' "

This is what one the completed query's looks like

Code: Select all

UPDATE EventDetail SET GPS_Lat_Coordinate=228023.42, GPS_Long_Coordinate=6609576.50, GPS_Alt_Coordinate=401.12 WHERE VIMSTime=2013-04-09 15:03:06

Note: I have tried chnaging the below code to add single quotes around the VIMSTime value but get the following error:
"[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1."

Code: Select all

on compareAndMerge
   -- Compare and where boolean = true update the VIMS Database accordingly
   repeat with x = 1 to the number of lines in lConvertedFileContents
      ## Placing all the GPS data into Variables
      put item 3 of line x of lConvertedFileContents into tTimeStamp
      put item 5 of line x of lConvertedFileContents into tLat
      put item 6 of line x of lConvertedFileContents into tLong
      put item 7 of line x of lConvertedFileContents into tAlt
      ## Run the query and place the results into a Gloabl variable
      put "UPDATE EventDetail SET GPS_Lat_Coordinate="&tLat&comma&space& \
            "GPS_Long_Coordinate="&tLong&comma&space& \
            "GPS_Alt_Coordinate="&tAlt&space&"WHERE VIMSTime="&tTimeStamp into tQuery
      put revdb_execute(gDataSourceConnectID,tQuery) into tResult
      put tResult
   end repeat

[bangkok]

First : in such case, it's necessary to "see" the SQL query you really send. Easier to read than the code that builds the query.

Just add :

Code: Select all

answer tQuery

Second : watch out with your "items" and your "itemdelimiter"... If you use tab, then set the itemdelimiter to tab

Third :

put "UPDATE EventDetail SET GPS_Lat_Coordinate="&tLat&comma&space& \
"GPS_Long_Coordinate="&tLong&comma&space& \
"GPS_Alt_Coordinate="&tAlt&space&"WHERE VIMSTime="&tTimeStamp into tQuery

You need to use single quotes for all the columns (for security), even VIMSTime.

Make it easier :

Code: Select all

 put "UPDATE EventDetail SET GPS_Lat_Coordinate='"&tLat&"',GPS_Long_Coordinate='"&tLong&"',GPS_Alt_Coordinate='"&tAlt&"' WHERE VIMSTime='"&tTimeStamp&"'" into tQuery

[Nakia]

Thanks for the Tips.

I actually had

Code: Select all

PUT tQuery

in the handler but removed it before posting.

That aside, I have tried as you suggested and now get the following error
"[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1"
The query now looks like this (copied from the message box)

Code: Select all

UPDATE EventDetail SET GPS_Lat_Coordinate='228023.42',GPS_Long_Coordinate='6609576.50',GPS_Alt_Coordinate='401.12' WHERE VIMSTime='2013-04-09 15:03:06'

[Nakia]

EDIT:

I found a typo (GPS_Long_Coordinate should have been GPS_Lon_Coordinate)
That has removed the error I was seeing before but now I am getting a new one

"[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression"

Any idea's on this one or is there a way to understand these errors a little better?

[Nakia]

Okay,

To add to my confusion..I have another handler on this card that queries this Table and that returns the format of the VIMSTime Column with the format
that matches what I am sending in my UPDATE query above.

But,
If I open this Database in EXCEL and view the Table, the VIMSTime Column is returning results with the following format:
"05/04/2013 07:51:04"

So, how do I know which format to use in the SQL Statement?

Also,
If I remove the WHERE from the SQL statement it does execute and write data to the database (obvioulsly incorrect data in wrong place but proves database can be modified)

[bangkok]

OK.

From

http://www.dbforums.com/db2/948347-how- ... ction.html

Try DateValue :

Code: Select all

SELECT SROSSI_ANDIP00F.* FROM SROSSI_ANDIP00F WHERE (((SROSSI_ANDIP00F.TMSVAR) >= DateValue("2003-06-05 13.37.54")))

[Nakia]

How to I use this dateValue option in my
UPDATE handler?

The page you linked to is more about the SELECT and as noted
Above I am not having any issues with SELECT

[bangkok]

Above I am not having any issues with SELECT

I guess you have a problem with VIMSTime.

The last error you got is : "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression"

And it seems that there are issues with Timestamp format with Access.

Therefore the problem seems to come from your :

Code: Select all

WHERE VIMSTime='2013-04-09 15:03:06'

Following the lead provided by google, you could try :

Code: Select all

WHERE VIMSTime=DateValue('2013-04-09 15:03:06')

or

Code: Select all

WHERE VIMSTime=DateValue('2013-04-09 15.03.06')

[Nakia]

This fixed it

Code: Select all

WHERE VIMSTime=DateValue('2013-04-09 15:03:06')

[Nakia]

I would love to now be able to able to apply factor to this WHERE condititon if this is at all possible?

Example:
WHERE VIMSTime=>(tTimeStamp - 2 seconds) AND VIMSTime=<(tTimeStamp + 2 seconds)

## Any pointers on adding this would be great.

[Nakia]

Afternoon,

Ended up with this as the statement I was asking about above but get the following errors.

Code: Select all

put "UPDATE EventDetail SET GPS_Lat_Coordinate='"&tLat&"',GPS_Lon_Coordinate='"&tLong&"',GPS_Alt_Coordinate='"&tAlt&"' WHERE VIMSTime>=DateValue('"&tPreStamp&"')"&" AND WHERE VIMSTime<=DateValue('"&tPostStamp&"')"   into tQuery

Error
"[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'VIMSTime>=DateValue('2013-04-15 06:59:01') AND WHERE VIMSTime<=DateValue('2013-04-15 06:59:05')'."

[bangkok]

"[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'VIMSTime>=DateValue('2013-04-15 06:59:01') AND WHERE VIMSTime<=DateValue('2013-04-15 06:59:05')'."

Too many "where" in your query.

It should be :

WHERE (VIMSTime>=DateValue('"&tPreStamp&"') AND VIMSTime<=DateValue('"&tPostStamp&"'))

[Nakia]

Thanks.

I tried this and got no syntax error or anything but it is returning true on some entires that dont fit the criteria.

Code: Select all

put "UPDATE EventDetail SET GPS_Lat_Coordinate='"&tLat&"',GPS_Lon_Coordinate='"&tLong&"',GPS_Alt_Coordinate='"&tAlt&"' WHERE VIMSTime BETWEEN DateValue('"&tPreStamp&"')"&" AND DateValue('"&tPostStamp&"')"  into tQuery

I will try your suggestion bangkok

[Nakia]

Okay, so BETWEEN and a combination of >= AND<= yield the same result.

Doing a little further digging it turns out that the only scenario returns a true (and therefore updates data in the DB) is when the two dates(tPreStamp and tPostStamp) span different days and this then returns a true for any date in tPostStamp.

So my query now looks like this...

Code: Select all

put "UPDATE EventDetail SET GPS_Lat_Coordinate='"&tLat&"',GPS_Lon_Coordinate='"&tLong&"',GPS_Alt_Coordinate='"&tAlt&"' WHERE (VIMSTime>=DateValue('"&tPreStamp&"') AND VIMSTime<=DateValue('"&tPostStamp&"'))" into tQuery

And the only time it will write data is if tPreStamp and tPostStamp jump dates...
example below;
tPresStamp = 2013-04-10 23:59:51
tPostStamp = 2013-04-11 00:00:01

[Nakia]

So it turned out that it needed the hash delimiters to work correctly..

Working query below:

Code: Select all

put "UPDATE EventDetail SET GPS_Lat_Coordinate='"&tLat&"',GPS_Lon_Coordinate='"&tLong&"',GPS_Alt_Coordinate='"&tAlt&"' WHERE (VIMSTime>=#"&tPreStamp&"# AND VIMSTime<=#"&tPostStamp&"#)" into tQuery