Results 1 to 7 of 7
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    a database that pulls in records from another database table and allows updates

    Hello!



    I am looking for advise on the best way to build a database that is potentially linked to a table in another database however I am having issues with being able to make changes to the table so this may not be the best process...

    I have a database that is used by managers to audit their employees. I need to create a process in which the directors over the managers can complete audits on the audits the managers completed. Originally I created a separate database and linked the table to the managers database. I then created a query to pull the records based on the criteria and used that to create a Form. The director will need to add in notes for their audit but when I put in a notes field or text boxes to update the record with who completed the re-audit and when it was completed I get an error message that tells me it is not updateable.

    Soooo, is there a better way to complete this task? I didn't want to put the re-audit forms in the same database as the managers audits only because that database is already has so many tables, queries, forms and reports. I think it would be easier to maintain if it were in a separate database but maybe that's not possible....

    Essentially what I want to accomplish, however I need to do it, is to make the process as automated as possible. The Director can go in at any point during the quarter and pull a completed audit for any of their managers without me having to load the audits for them.

    I'm sorry that was a lengthy explanation and I would greatly appreciate any advice anyone can give! Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    link in the tables as linked external tables,
    make your queries on these and they will update.

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Great! Thank you! I will give it a shot!

  4. #4
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    I have linked the table and created the query. Here is the SQL in case that will help. Also, I linked the table to the back end portion of the database - Is that an issue?

    I have a button that is used to enter the user id of the auditor and the data audited - when I push it I get the error "This Recordset is not updateable".

    I also have a text box for audit comments - It does not allow me to type in it.

    Code:
    SELECT tblAutoAudits.AuditName, tblAutoAudits.Unit, tblAutoAudits.Manager, tblAutoAudits.UserName, tblAutoAudits.[Claim Number], tblAutoAudits.LossDate, tblAutoAudits.[Date Audit Pulled], tblAutoAudits.[Loss Description], tblAutoAudits.COL, tblAutoAudits.Auditor, tblAutoAudits.DateAudited, tblAutoAudits.Coverage, tblAutoAudits.[Investigation/ Liability], tblAutoAudits.Financials, tblAutoAudits.APD, tblAutoAudits.[Injury Evaluation & Settlement], tblAutoAudits.[Documentation/ File Coding], tblAutoAudits.[Communication & Customer Service], tblAutoAudits.[Audit Comments], tblAutoAudits.[Positive Feedback], tblAutoAudits.[Opportunity for Improvement], tblAutoAudits.[Overall Score], tblAutoAudits.[Skip Reason], tblAutoAudits.Area, tbl_Directors.[Director Name], Rnd([Overall Score]) AS Random, tblAutoAudits.ReauditedBy, tblAutoAudits.DateReaudited, tblAutoAudits.ReAuditFeedback FROM tblAutoAudits INNER JOIN tbl_Directors ON tblAutoAudits.Area = tbl_Directors.Area WHERE (((tblAutoAudits.[Overall Score]) Is Not Null));

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    some joins do not allow updates.

  6. #6
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Ahhhh! Easy enough to fix! You're the best! Thank you

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why would you not just add the directors to the main db? You're basically giving them access to the tables in the first db anyway. Plus, they have network permissions on that folder, otherwise they could not access the lined tables. Also, it's not just joins that prevent updates - certain query types, calculated fields, aggregate functions - there's a whole list of reasons. You are using random function in at least one field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-29-2018, 07:13 PM
  2. Kick People out of the Database to make updates
    By Ian Frost in forum Security
    Replies: 5
    Last Post: 09-29-2016, 08:31 AM
  3. Trying to force updates on Database
    By Ekhart in forum Programming
    Replies: 3
    Last Post: 09-01-2016, 01:24 PM
  4. Replies: 2
    Last Post: 01-11-2013, 01:19 PM
  5. Database Updates
    By avarusbrightfyre in forum Programming
    Replies: 1
    Last Post: 09-15-2010, 02:58 PM

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
  •  
Other Forums: Microsoft Office Forums