04-03-09, 22:27 #1Registered User
- Join Date
- Mar 2009
Unanswered: Make or Alter table with primary in seperate database
I have a backend database that creates a make table query from an odbc. This has to be updated regularly as it has information such as employee id's, names, leave status, terms, etc. I use the table that is created from the make query (Hierarchy) and use it with a (Training) table which has training info (such as location, trainer, course number) and a third table (AgentTraining) that associates the primary keys from the "Hierarchy" table and "Training" table.
I need the relationship for when an employee is termed it will delete all records instead of me having to hunt down orphaned records. I've also built some form objects that (cascading combo's and such) I believe depend on these relationships. As right now everything works but the make query due to relationships.
To show how this is set up:
Table Hierarchy with PK AGENT_ID is tied to AgentTraining FK AGENTID I'll leave the association of other table out as I don't think it's necessary for this explanation.
Since I'm still a novice I didn't realize I couldn't do a make table with relationships so I'm at an dead end. I have been deleting the relationship between "Hierarchy" and "AgentTraining" and running the make table query on my backend, running a query to find the orphaned records in my main db then re-establishing the relationships.
Here's my SQL code for the make query:
SELECT [Agent Data].* INTO [IEX Hierarchy] IN 'M:\company\Databases & Reporting\CE Database\2009 CE Database.accdb'
FROM [Agent Data]
WHERE ((([Agent Data].LOB) Not In ("ADM","ANA","CAT","MGT","WFM")));
and specific help would be appreciated as I started learning VBA and SQL a little over a month ago.
here's what I've tried thus far to show what I'm trying to do:
Alter Table [IEX Hierarchy]
ALTER COLUMN AGENT_ID INTEGER
CONSTRAINT PK_AGENT_ID PRIMARY KEY
IN [DATABASE=C:\Documents and Settings\jgirea01\Desktop\2009 CE Database_2009.accdb;]
and also tried it this way
Create index PK_AGENT_ID
on [IEX Hierarchy].AGENT_ID ([DATABASE=C:\Documents and Settings\jgirea01\Desktop2009 CE Database.accdb])
and how would I drop the primary from my backend when updating?
DROP INDEX PK_AGENT_ID
ON [IEX Hierarchy]
Last edited by jmantn; 04-03-09 at 22:44.
04-03-09, 23:57 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
Was this already posted?
Instead of manipulating dropping/creating the primary key, why not instead create a table with a relationship/primary key and append/delete to that table? If you're structure dictates always creating new tables, you may want to revisit the structure and see how you can set it up to append records into that structure. I just don't think it's good practice dropping the primary key in order to update. It just means more chance for orphaned records. Although it's not perfect, MSAccess does a pretty good job managing relationships and cascade deleting/updating records. Messing with dropping/creating primary keys/relationships when updating just opens the door to other problems.
You can also change a specific query type to a "Dynaset (Inconsistent Updates)" to update problem records with a relationship but this is not ideal.
Last edited by pkstormy; 04-04-09 at 00:03.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
04-04-09, 16:27 #3Registered User
- Join Date
- Mar 2009
I tried your advice If I followed it correcty by renaming the table to Table1 that the backend database makes (so it wouldn't mess up my forms) and I did not set a primary on table1 so I could run the make table query at any time. This of course works fine
Now on my IEX Hierarchy table the one which use to be the table my backend created I have left AGENT_ID as the primary and to test this process I modified the status of some of my agents (Such as who they report to which changes about every three months as well as their department) and made a simple update query to update IEX Hierarchy from table1 as I believe you suggested however I still get a violation error saying I cannot update as records are locked. I remove the primary and the update for each agents status will then change.
THe problem I have is that everyone's status and dept and manager change very often not to mention the additions of new employees and termed employees. That's why I had a make table previously to keep updating our info by removing the old table and creating a new up to date table from an odbc. At this time I process around 30 status changes a month and every three months around 600 when everyone gets new schedules.
I had went a different route for a while which was forgoe the primary key on the IEX Hierarchy table and just use relationships in my queries and had a query to find orphaned records which worked fine until I then discovered due to my relationships I couldn't edit the data in queries as the recordset was being locked.... And adding a primary key back to the IEX Hieararchy fixed that so I'm back at step one.
If I didn't follow your directions please bare with me as I promise I'm trying and have spent over 15 hours searching various places online and in books trying to figure this out.