Mar 9 2007, 02:37 PM
I am using two db. One keeps tables with data, second one has forms, through which users are putting, loading data to linked tables from first db. When user is pressing button, module with sql formulas (append queries translated into sql) is appending data to tables (the same data are loaded to two tables). Unfortunately, from time to time I see that the input is only in one table. I really do not know why the same input was not loaded into other table.
I suppose there can be three issues:
- table is locked by other user putting his data
- there is run a query on data in table and table is locked
- both db are on network directories, maybe there are problems with network in my company and connection to main db is cut
I will be glad for any help and advice.
Can you also please explain to me, do I lock table with data when I am running any query on this table, when I sorting table etc.?
Mar 9 2007, 02:41 PM
I'm guessing that the table is not "locked", rather, the query you are using is not an updateable query. Check that.
Mar 9 2007, 03:54 PM
Let me explain.
I have created an append query. Then I have opened it as sql and copied to module.
This sql is working. It is not that none of data inputs in form after pressing button is loaded to the table. Randomly, some inputs are not in table. That is why I think it is something with random locking of table. Or maybe access db and its module with this functions is stucking from time to time.
Mar 9 2007, 10:11 PM
Please explain in greater detail. I am not following you completely.
Mar 10 2007, 07:03 AM
Lets start from beginning.
Every user has a db with form with text box in which he puts some data. Then he press button under which is function. This function is loading data from text box into two separate tables, linked to db from other (main db) on network directory.
Randomly, some of data are append to one table in main db but not to other. On a daily basis this difference is up to ten data more in one table versus other table.
It means that on of tables is from time to time locked and is not accepting data from form text box.
Formula is working and written properly.
What can then lock the table?
Mar 10 2007, 09:30 AM
So, you are saying that one of the tables data is supposed to save to is "local" and the other table (Main) is on a server and linked to the db? One is local and the other is linked? And that the 'linked' (main) table ALWAYS gets the data, but the other (loocal) table does not?
Am I understanding this correctly?
Or are BOTH tables 'linked'?
Mar 10 2007, 11:29 AM
Both tables (table1 and table2) are kept in db1 on network directory. Both tables from db1 are linked to db2 which every user has on his local computer.
So every data pt in db2 in form text box has to be loaded into linked from db1 tables: table1 and table2.
Randomly data are not loaded to table1 or table2.
Mar 10 2007, 02:28 PM
Have you tried to set a break point in your code to see if the code is throwing an error?
Mar 10 2007, 02:32 PM
Can you take a look at all the 'missing' info that exists in one table, but not the other, and look for a pattern? Ie, everytime a value with x is being attempted to put in table1 it does not go, etc...
I am unaware of something that will 'lock' a table for a simple SQL INSERT query - are the two tables identical in structure? Is it the same data going into both? What is different between the tables?
I'm more led to believe that its not a locking issue, that it is code not firing under specific circumstances, or that it is trying to input invalid values in the table. Perhaps you could post your table schemas (table 1 and 2) and your module in question?
Mar 12 2007, 10:34 AM
Any chance of posting your app? It sounds like you are saving the text value in a table after each update of the control ... if that is the case then you may be running into record lock errors (not table lock). It would probably explain a lot if you can post your app (or a sample of what you are doing with the code).
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here