First a little background, hopefully I can communicate the big picture here.
I have a MSDE database running on a dedicated database server, and am using a series of Access forms as the front end.
The tables are linked to the MSDE database by way of ODBC.
I am using a third-party database manager (EMS SQL Manager 2005 Lite for SQL Server v2.4.0.1) to create and modify tables which also allows me to type SQL queries and run them directly against the database without using access.
I have an access form where users can "reserve" a job number while they enter data. The process basically goes like this:
- click button
- use VBA to get the highest auto-increment key value in the database
- use a bit of logic to generate a job number based off the key value (plus one) and the department from which the job was entered. (so if the max key value is 100200 and the department was A, the generated job number is "A100201")
- insert a new record with only the new job number and the id of the user. this leaves intentionally only three fields filled in the table (the auto-increment key, the job number, and the user id)
After it does this, they can add line items to the order which are stored on a different table that I'm not yet concerned about.
There is the potential need to clean up the data, for example if the user accidentally "reserves" several job numbers and only enters the job particulars into one, or if they reserve a job number and don't have time to complete it and close the window.
I figured an easy way about that would be to run a delete query off the linked table when the data entry form is closed, something like "DELETE FROM tbl_jobs WHERE..." and this is where i tell it i want to get rid of all orders with the currently logged in user, where all but the initial 3 fields are null.
when i run it, access gives me a lock violation and will not delete these records.
I'm not entirely sure what I need to do to release the locks. I don't have any of the linked tables open, and there are currently no other users accessing this database as it's currently in development.
I can, on the other hand, use the SQL Manager software, type my query in their "execute query" feature and the records go away no problems so I'm assuming that Access is somehow the culprit here but I'm not sure why.
Any advice?
I have a MSDE database running on a dedicated database server, and am using a series of Access forms as the front end.
The tables are linked to the MSDE database by way of ODBC.
I am using a third-party database manager (EMS SQL Manager 2005 Lite for SQL Server v2.4.0.1) to create and modify tables which also allows me to type SQL queries and run them directly against the database without using access.
I have an access form where users can "reserve" a job number while they enter data. The process basically goes like this:
- click button
- use VBA to get the highest auto-increment key value in the database
- use a bit of logic to generate a job number based off the key value (plus one) and the department from which the job was entered. (so if the max key value is 100200 and the department was A, the generated job number is "A100201")
- insert a new record with only the new job number and the id of the user. this leaves intentionally only three fields filled in the table (the auto-increment key, the job number, and the user id)
After it does this, they can add line items to the order which are stored on a different table that I'm not yet concerned about.
There is the potential need to clean up the data, for example if the user accidentally "reserves" several job numbers and only enters the job particulars into one, or if they reserve a job number and don't have time to complete it and close the window.
I figured an easy way about that would be to run a delete query off the linked table when the data entry form is closed, something like "DELETE FROM tbl_jobs WHERE..." and this is where i tell it i want to get rid of all orders with the currently logged in user, where all but the initial 3 fields are null.
when i run it, access gives me a lock violation and will not delete these records.
I'm not entirely sure what I need to do to release the locks. I don't have any of the linked tables open, and there are currently no other users accessing this database as it's currently in development.
I can, on the other hand, use the SQL Manager software, type my query in their "execute query" feature and the records go away no problems so I'm assuming that Access is somehow the culprit here but I'm not sure why.
Any advice?
