LiveCode Forums. • Access .mdb

Page 1 of 1

Access .mdb

Posted: Tue Apr 09, 2013 7:40 am

by 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

Re: Access .mdb

Posted: Tue Apr 09, 2013 8:58 am

by 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

Re: Access .mdb

Posted: Tue Apr 09, 2013 10:09 am

by 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'

Re: Access .mdb

Posted: Tue Apr 09, 2013 10:19 am

by 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?

Re: Access .mdb

Posted: Tue Apr 09, 2013 10:32 am

by 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)

Re: Access .mdb

Posted: Tue Apr 09, 2013 3:41 pm

by 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")))

Re: Access .mdb

Posted: Tue Apr 09, 2013 9:26 pm

by 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

Re: Access .mdb

Posted: Wed Apr 10, 2013 12:42 am

by bangkok

Nakia wrote: 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')

Re: Access .mdb

Posted: Wed Apr 10, 2013 2:40 am

by Nakia

This fixed it

Code: Select all

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

Re: Access .mdb

Posted: Wed Apr 10, 2013 3:44 am

by 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.

Re: Access .mdb

Posted: Mon Apr 15, 2013 5:59 am

by 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')'."

Re: Access .mdb

Posted: Mon Apr 15, 2013 7:11 am

by bangkok

Nakia wrote: "[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&"'))

Re: Access .mdb

Posted: Mon Apr 15, 2013 7:34 am

by 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

Re: Access .mdb

Posted: Mon Apr 15, 2013 7:47 am

by 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

Re: Access .mdb

Posted: Mon Apr 15, 2013 11:43 pm

by 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