Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    10

    Unanswered: Linked Tables & Queries

    Hi Folks,

    I'm after a bit of help with some linked tables and queries I have in a database I'm trying to create. Bit of an amateur so having to use some trial and error!

    I am pulling two tables from an oracle database into Access.
    • "WM_Agents" contains people first names, last names, a team ID and lots of other person information.
    • "WM_Teams" The other contains the team ID and associated team name.


    I'm using a query ("Agent & Team Query") to merge the two names together and then bring across the team name. all working so far.

    I have another table ("Quality Data") in which the peoples names will be pulled across using a lookup from the first query. This gives a nice combo box drop down to use when selecting the name. This table has other fields for date, quality score & notes, all manually inputted.

    I then combine the data from the "Quality Data" table with the "Agent & Team Query" to give me a query containing all of the fields ("Quality All Query"). This then nicely ties back the team name to the agent so that we can score team totals etc.

    The problem I have is that when creating the last query the data all then seems to lock into place. I can't then update or change the info. I can see the dropdowns on the agent name but cannot change them and I cannot add new data.

    Apologies for the essay, hope that makes sense and help greatly appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As general guidelines:

    1. Every linked table must have an IDENTITY column (usually the PK but not always, any column with a UNIQUE constraint can go) if you want to be able to update its contents. If necessary, you can define such a constraint locally (at the Access database level) by creating a local pseudo-index on the linked table (see the Jet SQL DDL CREATE INDEX documentation in Access Help).

    2. A query joining several tables must comprise an IDENTITY column for each table you want to be updatable. There might be other conditions that determine if the query (or part of it) is updatable or not.

    3. The tables must of course be updatable according to the constraints and the security parameters defined for a given user at the server level.
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Posts
    10
    Sinndho, thanks for coming back to me.

    For 1) OK I think i understand and having a look at the tables i am using only one of the linked tables seem to have any problems. All other have a unique primary key.

    The WM_Teams table has 3 fields:
    • WM_TEAM_ID - Number Format. Required = Yes, Indexed = Yes (No Duplicates). I think that this should be the primary key but isn't & I think could be used as the IDENTITY but could the primary key marks on the others effect this?
    • WM_SITE_ID - Number Format. Required = Yes, Indexed = Yes (Duplicates OK). This seems to be marked as a primary key.
    • WM_NAME - Text Format. Required = Yes, Indexed = No. This is also marked as a primary key.


    I can't change the properties of the linked table, but maybe the Jet stuff you refer to could?

    For 2) Sorry not sure I get this, do I need to actually include the IDENTITIES columns in my query or do they just need to exist on the tables I am using in my query?

    For 3) I should be OK as I am building the database and I am the one currently trying to run the queries.

  4. #4
    Join Date
    Jun 2011
    Posts
    10
    OK, i've been playing with this and seem to have come up with a solution, although it doesn't seem to elegant.

    I have created a blank table "Agent_Team" with two columns and the Agent being the primary key with unique values.

    I use an append query to pull the correct data from the other two tables and pass this into the "Agent_Team" table.

    I use the new table to feed through the database allowing updates.


    I've had to create a delete query to clear the "Agent_Team" table before running the append query which doesn't seem to great and idea but seems to work ok when tied to opening of an input form.

    Any thoughts on how to improve this are appreciated but at least it works for now.

    cheers

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    WM_TEAM_ID should indeed be used. If Access does not recognize it as the Identity column, possibly because the table seems to have a composite index (WM_SITE_ID + WM_NAME ) marked as Primary, you can create a Unique constraint on the linked table (this constraint will be local to Access and you don't need any special permission at the server level to create it):
    Code:
    Function AddUniqueIndex()
    
        Dim strSQL As String
        Dim dbs As DAO.Database
        
        strSQL = "CREATE UNIQUE INDEX U_Idx ON WM_Teams ( WM_TEAM_ID );"
        Set dbs = CurrentDb
        dbs.Execute strSQL
        Set dbs = Nothing
        
    End Function
    I'm surprised though, that Access does not recognise the Primary Key as defined at the server level.

    This remains to be tested and measured, but I believe that you could get a slightly better performance by dropping the table [Agent_Team] and using a SELECT INTO query to rebuild and fill it. However, I'm not sure that the gain would be worth the work.
    Have a nice day!

Posting Permissions

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