>I've been developing workgroup applications (up to 50 users) with FoxPro (all >versions) for about 8 years now, so I understand record and file locking very >well. Now, however, I must move some of my applications to the web (imagine >that). The principal method of access to this data is going to be ODBC, using >SQL commands. I have developed a few apps in MS SQL Server, so I'm not too >worried about SQL, but I have done zero work with VFP and ODBC/SQL.

>Here is where I'm unclear. How do I lock a record using ODBC/SQL? Should I >even try? Also, has anyone tried using VFP with something like ColdFusion?

>Just trying to grasp the big picture here.

I would suggest considering using transactions rather than trying to lock individual records. You can use BEGIN and END TRANSACTION, COMMIT and ROLLBACK in SQL via ODBC from Fox. This way you can ensure an operation completes/updates successfully.

The alternative is to create your own record locking process by creating a stored procedure in SQL which will allow you to pass records (using a unique id and a table name) to it, and store the records into a table. Then if you check this table before allowing a user access to a record, you can prevent another user overwriting their changes. In simple terms, when you want to lock a record, add it to a SQL table. When you want to unlock it, simply delete it from this table, and check this table before allowing access to any update procedure.

The other thing you may want to consider is that if you are using SQL7.0, and speed is not a big issue, you can link you foxpro tables as a linked server and call them from a standard TSQL query using the OPENROWSET function. This way you may not need to migrate all your tables to start with.

As for coldfusion, I have played around with it, but not really spent enough time to comment on it's merits/downfalls.

Hope this helps

Cheers
Steve Lea