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!
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.
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.
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):
Dim strSQL As String
Dim dbs As DAO.Database
strSQL = "CREATE UNIQUE INDEX U_Idx ON WM_Teams ( WM_TEAM_ID );"
Set dbs = CurrentDb
Set dbs = Nothing
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.