Table adding records to another table (1 Viewer)

chriscardwell06

Registered User.
Local time
Today, 15:38
Joined
Aug 18, 2011
Messages
38
We have a table that is an ODBC connections from our erp system. Any time a new sales order is added into the erp system I need the ODBC table to also create that record on another table. We will call it Engineering sales order table. I've googled and cannot find this answer. Is there a way to set this up?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:38
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I understand but an ODBC connected table, would indicate the table is stored in the ERP system. If this is the case, and you want the ERP system to duplicate data in this table, which is also in the ERP, wouldn't you need to have the ERP do it?
 

chriscardwell06

Registered User.
Local time
Today, 15:38
Joined
Aug 18, 2011
Messages
38
The ERP system contains all the info about the order. But for instance another table (sorted by the same sales order) would contain info about which engineer was working on it for example. Obviously in the erp system when a new order is entered there won't be a record for that sales order (so 28283) on the engineering sales order table. I want to get the engineering sales order table to add the new record (so 28283) once it's entered into the erp system or the ODBC connection.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:38
Joined
Oct 29, 2018
Messages
21,358
The ERP system contains all the info about the order. But for instance another table (sorted by the same sales order) would contain info about which engineer was working on it for example. Obviously in the erp system when a new order is entered there won't be a record for that sales order (so 28283) on the engineering sales order table. I want to get the engineering sales order table to add the new record (so 28283) once it's entered into the erp system or the ODBC connection.
Hi. Please remember we don't have any visibility to your environment, so what could be "obvious" to you may not be so obvious to us. For example, I have no idea if the engineering sales order table is stored in the ERP system or not. Is it?
 

chriscardwell06

Registered User.
Local time
Today, 15:38
Joined
Aug 18, 2011
Messages
38
I apologize for that. No it is not in the ERP system. It is in the access file that we are building a database off of. The access file has the linked ODBC connections and it also has a table that I've made inside it that contains the engineering data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:38
Joined
Oct 29, 2018
Messages
21,358
I apologize for that. No it is not in the ERP system. It is in the access file that we are building a database off of. The access file has the linked ODBC connections and it also has a table that I've made inside it that contains the engineering data.
Okay, thanks for the clarification. So, let's say the ERP table gets a new record, how would your Access table know it? The only thing I could think of is to manually execute a find unmatched query against the local and linked table; and if there's a mismatch, you can execute an APPEND query to the local table. So, someone could execute this process, or you could set it up to run every so often, or only when the db opens.
 

chriscardwell06

Registered User.
Local time
Today, 15:38
Joined
Aug 18, 2011
Messages
38
That might do what I need it to do. Anytime a user opens a specific form I could make it execute those steps and add the new order. I'll give that a try.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:38
Joined
Oct 29, 2018
Messages
21,358
That might do what I need it to do. Anytime a user opens a specific form I could make it execute those steps and add the new order. I'll give that a try.
Sounds like a plan. Good luck. Let us know how it goes...
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:38
Joined
Sep 21, 2011
Messages
14,046
That might do what I need it to do. Anytime a user opens a specific form I could make it execute those steps and add the new order. I'll give that a try.

Might be less process intensive if you save the last retrieved number and retrieve all records with a number greater than that, and update at the end.?
 

KHallmark

Registered User.
Local time
Today, 12:38
Joined
Jul 11, 2019
Messages
12
You will need to create a form for the main table and then click on the other table and drag it onto the form. Follow the prompts until you get to the choice to select fields or create your own and click ok. A subform is created automatically for this.

Then right click on any open space, click 'Properties'

go to link master fields, put your fields you want to match, ex. First name;Last Name; Cell Phone.... there needs to be a ; between each field in Link Master Fields properties and Link Child Fields. Save and close and now every time you create a new record, you will have to use the subform on the main form to create the new records. Basic data will be automatically copied over and then you can add the additional data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:38
Joined
Feb 19, 2002
Messages
42,973
If you can modify the design of the ERP tables, you can add an after Insert trigger to insert a row in a separate table in the ERP database. Of course, there would be no way to identify the engineer unless the ERP already does that.

At a practical level, you most likely cannot modify anything in the ERP database and that would mean that you would need to do this process from Access. I would keep a table with a date and time and use that to select all sales orders entered after that date/time or use a DMax() on a form to find this value from YOUR local table. Then add a new row to the log table when you are done and append the highest date/time you selected from orders. If your sales order table does not include a date/time, you can use sales order ID instead assuming it is assigned sequentially. You don't need a find unmatched. That will get time consuming since it will require joining your ACE table to the ERP database. Doable but slow. Using a query to find the Max() sales order ID (or Date/time) in your Access table, will let you create an append query that selects sales orders higher than the last one in your ACE table.
 

Users who are viewing this thread

Top Bottom