VFP 9 failing to update MS access tables

[TinyNinja]

Hello,

I have written a visitor system in VFP 9 with MS Access 2016 as my database on a shared network drive.

I find at random times it just fails to update or insert records. Here is an example of a failed update. I removed the names of people but that is the code still. I will grab it after and run it in the MS Access query interface and it will update without a problem.

  Update tb_InBuilding Set LeaveTime='10/12/18 16:49:34', StaffOut='TestName' where  lastn='TestName1' And FirstN='TestName2' And building='TestBuilding' And entertime=#10/12/18 16:29:35#

I have made changes to VFP to give a delay using inkey(.2) just in case the program is trying to hit ACCESS to quickly between queries. I just made that change and haven't been able to see if it is successful people. I am waiting until Monday to see if I have more failed attempts.

Is it possible it could be my access drivers I have install? I originally install MS Access drivers 2010 not realizing that the system was using MS Access 2016. Even with the previous version of the drivers my code works properly with inserting and updating records. That was until I noticed recently that it was randomly failed to execute the database changes.

Has anyone experienced this before and could give some advice?

Thank you in advance!

 

[Olaf Doschke]

I'm not doing anything with Access anymore, but am a little familiar, this reminds me of the #date# syntax.

If this works as is directly within Access it shouldn't matter you mix syntax with String delimiiter ' and #, but it's surely better to use one syntax consistently.
Are you getting an error at the execution and how do you execute this? Is this the SQL of a remote view definition or do you run SQLExec?

ASide of any driver and syntax problems finding records to update via matching the entertime is something I assume will not work well when time is stored and compared with higher precision you can easily fail to find the record, why not use the ID of the table? I strongly assume this is intended to complete a visit record when he leaves. Does it scan the entertime from something the visitor gets when entering? Does that perhaps print current time and there is a lag sometimes causing the printed time to differ by a second?

In any case, you better know the ID of the record you want to update.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

If you are using SQLEXEC() to send your UPDATE statement to Access, are you testing the value returned by SQLEXEC()? If it is negative, that indicates an error. In that case, you can use AERROR() to see an error message and other information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

If it fails on matching the entertime, you could also losen that and query for an interval of 2 or 3 seconds, but of course a specific ID of the record created at entry would be better.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

This is the sql line I am using in the code.

The reason I use entertime is because I might have multiple records with the same name or location so by matching it on entertime I have a exact match.
The ID on the table is assigned to the person so it can show up many times. The primary key on the table is just a random set of increasing numbers but I could probably change that around so it pulls that number instead of time if that truly is the problem.
I don't mind changing my matching around. I have more history now on how the application is being used so I can tweak it more now.

 lsql=[ Update tb_InBuilding Set LeaveTime=']+Ttoc(Datetime())+[', StaffOut=']+ljstaff+[' where ]
	   lsql=lsql+[ lastn=']+llname+[' And FirstN=']+lfname+[' And building=']+lcbuild+[' And entertime=#]+Ttoc(letime)+[# ]
	   lexec=SQLExec(lconn,lsql)
	   If lexec<0
	   Strtofile(lsql,[tempw.txt],0)
	   Endif

I tried the ' vs # on my entertime when I was testing and Access likes the # when it is in the where spot.

I will start to include the AERROR() so I can see the true cause.

 

[Olaf Doschke]

>The reason I use entertime is because I might have multiple records with the same name or location so by matching it on entertime I have a exact match.

Of course, with ID I mean the primary key of the table, if there is a column named ID just being a foreign key, that's clearly insufficient and aq really bad choice for a foreign key.

If a "random set of increasing numbers" is the primary key of that table, it will still be a specifc value or several columns. Then you still would remember that and not anything just likely unique. You always go back to the same record with its primary key, that's what it's for. A compound key is not easy to use, you have to store it in multiple variables or columns during the visit until the visitor leaves, so refactoring that would be good, but if that causes a lot of refactoring work you can still also remember a set of values, if that's the key, that's really basic understanding of databases, you never think twice about this.

As you now show how you generate these datetime values, are you sure this is getting over correctly? As I already said the datetime type may have a higher precision as the second - or also a lower. so VFPs TTOC might not hit that exact value, that way you get no match as a match isn't done on nearest value. If you look for 10/12/18 16:29:35 and browsing the access table also shows 10/12/18 16:29:35 but stored is 10/12/18 16:29:35.001 you don't get a match. I would turn this into a BETWEEN clause.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Second, but perhaps more important, as Mike already said when SQLExecs return value is <0, there is an error you can fetch with AERROR.

You do react by putting that SQL statement into a text file for later analysis, that's okay, but you should also store the error info, Access likely will point out what's wrong.

For example, VFPs TTOC() might simply not be the right format.

So according to Mike (and me and VFPs help topic on SQLExec) do this:

If lexec<0
   Strtofile(lsql,[tempw.txt],0) && really? overwirte the existing file? second parameter 0 measns that!

   Local lnRow, lnCol
   For lnRow = 1 To [b]AERROR(paError)[/b]
       For lnCol = 1 To ALEN(paError,2) 
           Strtofile(chr(13)+chr(10)+Transform(paError[lnRow,lnCol]),[tempw.txt],1)
       Endfor lnCol
   Endfor lnRow
Endif

The main wight is getting that error info from AERROR, an error in the sql engine of a remote backend - here Access - does not cause an error exception in VFP and error handling or the system error message, you need to actively pull this error info.

By the way, because you overwrite the text file it might even be all updates failing when TTOC doesn't give you the correct formatting or the SET clause also needs the datetime delimited by #, but you only save the last failing statement.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

The code snippet was old and I have updated them all to a 1 instead of 0 so I can get a history of everything.

Olaf I will incorporate your code update to my application and see what the future errors are. I am also in the middle of changing the code to grab the primary key to update the records instead. I made a quick test change and it worked without an issue so I will proceed in changing it everywhere now.

Thank you Olaf and Mike!

 

[Olaf Doschke]

You might want to format the error info differently, this nested two loops simply grab every error array element and put it in a separate line, converting it to string using the universal TRANSFORM().

Look into AERROR and you get a description of what element or column of an array row has which meaning, but even without the message text should come out and perhaps clear up the issue.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

I have tweaked it to suit my needs.

Thanks again!

 

[TinyNinja]

I got my error and I now know the problem. Does anyone know of a good solution for this?

Is there a way to have VFP wait for the record to become unlocked to then update?

Update tb_InBuilding Set LeaveTime='10/15/18 14:47:38', StaffOut='teststaff' where ID=6576

1526
Connectivity error: [Microsoft][ODBC Microsoft Access Driver] Could not update; currently locked by user 'admin' on machine 'testmachine'.
[Microsoft][ODBC Microsoft Access Driver] Could not update; currently locked by user 'admin' on machine 'testmachine'.
S1000
-1102
1
.NULL.

 

[Tore Bleken]

Make a Do-While loop, and exit from the loop after a successful update or after x retries.

 

[Olaf Doschke]

VFP is only sending the update, what would need to wait is Access, not VFP.

SQLExec is sending queries and waiting for the result, it does not force a reaction or timeout or exception, that came from Access and the ODBC driver, so if that could be brought under control, then not on the VFP side.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Access Options allow specifying how clients lock, no locks, all records or edited records are the options. And there is an option about the number of tries.

User 'admin' on 'testmachine' looks like this was you yourself.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

And by the way, the first number 1526 of this log entry is the VFP error number. That's always 1526 for any ODBC error, no matter if a syntax error, type error, trigger error or locking error. So if you want to go for Tores idea react with a retry of the [tt]lexec=SQLExec(lconn,lsql)[/tt] when this element of paError[1,5] is -1102, the Access error number for locks by another user.

If you get any other error numbers (either different from 1526 or a 1526 error with another ODBC error number than -1102, you should, of course, still log this and not handle it by retry so you could also catch other erors and fix them.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

I created one login for my 4 separate forms to use. Would creating 4 logins solve the issue? I feel like that won't resolve the issue because it pertains to the individual record being accessed and not the user or am I incorrect?

I will see about doing a loop until the record gets updated/inserted. Trying to create a foolproof system.

 

[Olaf Doschke]

You should really look into extended options of Access. No, you don't lock users, users lock records. And the option to lock all records you read makes it most probable to step on your own feet. But logins don't lock anything in themselves.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

I updated Access to allow for 50 retries. It looks like the default was set to 2.
The few hours after I updated the system I had not seen any errors. I will check tomorrow to see if I need to create that loop to make sure the records get updated.