>I've noticed that even a select statement on one table is locking it. ie select prodcode from tuserprod somehow locks tuserprod table.

SQL Server uses read and write locks. By default you cannot update data that another process tries to read. SQL server achieves this by placing a read lock on data it SELECTs. See here for more information: http://msdn.microsoft.com/en-us/library/ms189122.aspx

>Funny thing is that even after I close the form in question, the lock still exists, how do I unlock this?

The lock exists as long as the transaction on the connection has uncommitted changed. If you use manual transactions and SPT, this means until you issue either SQLCOMMIT() or SQLROLLBACK(). With automatic transactions (the default), the lock is released after each statement completes.