Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2013
    Posts
    23

    Unanswered: Newbie - Needs Help!!

    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.

    any ideas?
    Attached Thumbnails Attached Thumbnails macro.PNG  

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Is the database on a server and do the user(s) have the correct rights? Check that there are not 2 different mdw files. Is the db split? Do a Google Search.

    HTH

  3. #3
    Join Date
    Oct 2013
    Posts
    23
    Thanks you for your response.

    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.

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    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.

  5. #5
    Join Date
    Oct 2013
    Posts
    23
    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.

    Ive attached a stripped back version.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    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.

  7. #7
    Join Date
    Oct 2013
    Posts
    23
    Re-thought it and it now works - Thanks.

    Changed the queries and structure of the Macro. See attached.

    thanks for your help.
    Attached Thumbnails Attached Thumbnails 1.JPG  

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •