Firstly I'm new to this form and Databases. I've little SQL or VB skills. So I am currently using Access To build a Db.
I have created a small database to be used as a viewer. It extracts data from a linked table, filters it into a new table, then filter it further into another new table. This table then is the control source for the viewer form. Which the user is only going to have access to.
The issue is I receive the following error: The Database engine could not lock table 'tblNICoillections - Step2' because it is already in use by another person or process' when running the below macro on timer.
The database is on a sever however all users have the correct access. Also the Db is not split, however the only linked table from which it pulls in the data is split.
The only file type there is, is .accdb.
Ive been using google which has formed my opinion that it is something to do with the queries using the Select Statement to re-create the tables and the 2nd table is still being re-created when the form is trying to access it. but I may be wrong.
Of course I am not familiar with your setup or reasons why you do it the way you do, but why not simply import the data from your linked table to a temp table and use that instead and then perform a delete and new append each time? Hard to say without seeing more of your setup.
Table 1 - Imports the required data
Table 2 - Is the result of Query 1 which filters Tbl1 to create Tbl2
Table 3 - Is the result of Qry2 which filters tbl 2 creating tbl 3, this is where the form pulls it data from
The premise of this database is to only show customers who are to collect orders as opposed to deliveries. This data changes so the idea is for the query to rebuild the table each time it runs - pulling in new data.
So, your refreshing your data by performing a timed event. Just curious, why does this other db have to be located someplace else? Why can't it be combined and just use different front end menu's? Your process is the cause of your problem. I would re-think the process.