Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-18-2019, 11:08 AM   #1
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Table adding records to another table

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?

chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 11:17 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,601
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Table adding records to another table

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-18-2019, 11:24 AM   #3
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Re: Table adding records to another table

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.

chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 11:34 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,601
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-18-2019, 11:45 AM   #5
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Re: Table adding records to another table

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.
chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 11:50 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,601
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
chriscardwell06 (06-18-2019)
Old 06-18-2019, 12:01 PM   #7
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Re: Table adding records to another table

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.

chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 12:02 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,601
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-18-2019, 12:16 PM   #9
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,314
Thanks: 431
Thanked 784 Times in 761 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 07-11-2019, 10:48 AM   #10
KHallmark
Newly Registered User
 
Join Date: Jul 2019
Posts: 12
Thanks: 0
Thanked 1 Time in 1 Post
KHallmark is on a distinguished road
Re: Table adding records to another table

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.
KHallmark is offline   Reply With Quote
Old 07-22-2019, 10:13 AM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Table adding records to another table

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding records to a table plawrence85 Tables 4 09-23-2008 10:26 AM
Adding new records in one table & adding to other tables. Rusty Forms 1 04-29-2004 07:45 AM
Adding Records to a Table saross Modules & VBA 3 08-05-2003 01:59 AM
Adding Records to a table ciao Modules & VBA 3 06-15-2002 12:23 PM
Adding Records to Table AngelaMC Forms 1 11-14-2000 01:30 PM




All times are GMT -8. The time now is 06:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World