?
Solved

Update key field in native Access table from field in linked table

Posted on 2012-08-20
13
Medium Priority
?
663 Views
Last Modified: 2012-08-26
How can I have a key field in a table native to Access be updated to reflect any changes to that same field in a linked table?  

I use one field, CONTRACT_NUMBER, from a linked table as a key field in a native Access table so users can enter dollar values against the CONTRACT_NUMBER in the native Access table.  If new contract numbers are added in the linked table how can the native table key field be updated with the new contract numbers?

I’ve attached an example database showing how I understand Access should be set up.  My limited Access experience means it may not be set up properly to do what I need it to do.  The only piece of the example DB that I can’t change is the table “SAP_LINKED_TBL_CONTR_FUND”.  This table is representing a linked table that gets the contract data that exists in our SAP accounting system.

To create the native Access table I used a "Create Table" query and copied the CONTRACT_NUMBER field from the linked table into a new table.  Was this the right thing to do?  

Thanks in advance for any help on this question.
CONTRACT-COMMITMENTS-4.accdb
0
Comment
Question by:dec789
  • 6
  • 4
  • 3
13 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38313714
Not real sure what you're asking, so I'll give this a shot.

If you are attempting to change the table schema of the source table, meaning change a data type, add a column, delete a column, change the keys, etc.
(1)  Make the changes in your source table.
(2)  Go into all apps that have a linked table to (1), and go to Linked Table Manager and relink.

If you are referring to values in the source tables, then any linked table once it refreshes (meaning every time someone opens it) would have access to those changes.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 total points
ID: 38313760
Not sure I understand your design here.

First, ...the tables are not related in the database window, so I am not sure what the relationship should be.

One To One?
Or
One To Many?
Or
Something else...?

My guess is that this is One to One because both tables have the same amount of records.

In some cases you just need to add automation to the "Lead" form to make sure the corresponding record is created in the "Slave" table

So on the AfterInsert event of the "Master" form (After a master record is created), you automatically create the corresponding record in the "Slave" table:

Something like this on the After Insert event on the "master" form:

Private Sub Form_AfterInsert()
Dim strSQL As String
strSQL = "INSERT INTO YourSlaveTable(YourKeyField) VALUES(" & Me.YourKeyField & ")"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

So this code will automatically create a "slave" (One To One) record in the Slave table.
...Automatically entering the Key (Linking) value from the "Master" form.

Notes:
1. The BeforeInsert event may be a better event for this..., ..in case you need to cancel the event, ...but the After Insert event may be OK for now.
2. I may still not be understanding something, ...and there may very well be a better way of doing this, so let's see what other Experts post.

;-)

JeffCoachman
0
 

Author Comment

by:dec789
ID: 38314022
Ok, obviously I don't do well at this asking question thing - sorry for the confusion.

The relationship between the two tables is one-to-one.  How can I relate two tables in "the database window"?  Maybe this is my problem.  I didn't know you can relate two tables other than in a query.  My attempt at relating the two tables is in the query I created.

My question is in regards to how to update the values in the native to Access table not changing the schema of either table.

I understand the Master/slave concept where a new record would be inserted into the slave table based on an insert event in the master table but I can't envision how Access would even know about an insert event in the master table.  The master table is in reality a SQL Server 2005 table that is being updated nightly with a download from SAP.  The "master" table in SQL Server is deleted every night and re-created based on the new download from SAP.  At that point any new CONTRACT_NUMBERS would appear in the "master" table which is linked to the Access DB.

JeffCoachman would your idea of using a BeforeInsert/AfterInsert event work given that the Master table does not really exist in Access?   Does Access know when a new record is inserted in a linked table?

 Again I may have messed up the relationship between the two tables so if you can see a better way to relate the tables I'm all for it.

Thank you for your patience.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 38314057
> I didn't know you can relate two tables other than in a query
Database Tools: Relationships is the place to establish relationships between tables.

What you see in the Query Designer is JOINs between tables, and is only for that query, and does not create a permanent relationship.

>the values in the native to Access table
I really don't understand what this means.  Values in a local (meaning not linked) table?

>where a new record would be inserted into the slave table based on an insert event in the master table
Access doesn't do triggers like SQL Server, where you can program something to automatically happen when a record is inserted/updated/deleted from a table.  You'd have to write custom VBA code to do both the inserting, and the resulting other inserting.

Also, not sure if this is what you're asking, but afaik you can't establish a relationship between a local Access table and a linked SQL Server table.  Most flaming grouches, er I mean SQL Server DBA's, would not like that.
0
 

Author Comment

by:dec789
ID: 38314304
> I didn't know you can relate two tables other than in a query
>>Database Tools: Relationships is the place to establish relationships between tables.
> Thank you that is very helpful

>>What you see in the Query Designer is JOINs between tables, and is only for that query, >>and does not create a permanent relationship.
> Thank you for this information as well.

>the values in the native to Access table
>>I really don't understand what this means.  Values in a local (meaning not linked) table?
>Yes I mean to say the values in the local not linked table.  As new contracts get added I >was trying to get the contract numbers that appear in the linked table to automatically >appear in the local table.

>where a new record would be inserted into the slave table based on an insert event in the master table
Access doesn't do triggers like SQL Server, where you can program something to automatically happen when a record is inserted/updated/deleted from a table.  You'd have to write custom VBA code to do both the inserting, and the resulting other inserting.

>Sounds like I will have to come up with some VB code using the OnOpen event to search >the linked table for any new contract numbers that are not part of the local table then >use VB code to insert these new contract numbers into the local table.  Does that sound >about right???

Also, not sure if this is what you're asking, but afaik you can't establish a relationship between a local Access table and a linked SQL Server table.  Most flaming grouches, er I mean SQL Server DBA's, would not like that.
>Still learning...  Thanks for the information!
0
 

Author Comment

by:dec789
ID: 38314305
> I didn't know you can relate two tables other than in a query
>>Database Tools: Relationships is the place to establish relationships between tables.
> Thank you that is very helpful

>>What you see in the Query Designer is JOINs between tables, and is only for that query, >>and does not create a permanent relationship.
> Thank you for this information as well.

>the values in the native to Access table
>>I really don't understand what this means.  Values in a local (meaning not linked) table?
>Yes I mean to say the values in the local not linked table.  As new contracts get added I >was trying to get the contract numbers that appear in the linked table to automatically >appear in the local table.

>where a new record would be inserted into the slave table based on an insert event in the master table
Access doesn't do triggers like SQL Server, where you can program something to automatically happen when a record is inserted/updated/deleted from a table.  You'd have to write custom VBA code to do both the inserting, and the resulting other inserting.

>Sounds like I will have to come up with some VB code using the OnOpen event to search >the linked table for any new contract numbers that are not part of the local table then >use VB code to insert these new contract numbers into the local table.  Does that sound >about right???

Also, not sure if this is what you're asking, but afaik you can't establish a relationship between a local Access table and a linked SQL Server table.  Most flaming grouches, er I mean SQL Server DBA's, would not like that.
>Still learning...  Thanks for the information!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38318738
I'm getting a bit lost here....

What I proposed requires no linking and can work as a One to One...

You fire my code when you create a record in one form, and a matching record is created in the other table with the same key value...


JeffCoachman
0
 

Author Comment

by:dec789
ID: 38320532
I started this question on the assumption that linking was the answer but after reading your posts, JeffCoachman, I have come to learn that linking will not work.  I also see that the work will have to be done in VB and your INSERT routine posted at the top is what I will use.  

The piece of this that is still confusing to me is...

What event will cause the insert code to fire?  

The master table that will get new records added to it (new contract numbers) exists in SAP.  Then SAP exports out a text file of the entire master table every night into SQL Server.  My Access DB is linked to this master table in SQL Server.  How will Access know when a new record is inserted into the SAP master table?  This might be a really easy concept that I'm just not getting it.  Sorry!
0
 

Author Comment

by:dec789
ID: 38320533
I started this question on the assumption that linking was the answer but after reading your posts, JeffCoachman, I have come to learn that linking will not work.  I also see that the work will have to be done in VB and your INSERT routine posted at the top is what I will use.  

The piece of this that is still confusing to me is...

What event will cause the insert code to fire?  

The master table that will get new records added to it (new contract numbers) exists in SAP.  Then SAP exports out a text file of the entire master table every night into SQL Server.  My Access DB is linked to this master table in SQL Server.  How will Access know when a new record is inserted into the SAP master table?  This might be a really easy concept that I'm just not getting it.  Sorry!
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 38320603
>How will Access know when a new record is inserted into the SAP master table?
Linked tables will always load with whatever rows are in the source table, so you'll immediately have access to it.  

Now, do you expect Access to DO something about a new record?  If yes, then that would best be handled in the source, namely a SQL Server trigger/code, as Access is not well suited for this task.

Another option is if there is a timestamp in the table, then you can always create a query that does something like 'Give me all rows where the timestamp column is greater than the last timestamp'
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38321800
<>How will Access know when a new record is inserted into the SAP master table?>
Then this is what I was not understanding...

Sorry.
I thought you were creating the records in an Access form.

So please continue on with Jim...


Jeff
0
 

Author Closing Comment

by:dec789
ID: 38332949
Jeff and Jim,

Thank you both for your time and patience with my question.  I've learned a lot from the comments supplied and will implement a strategy that incorportes suggestions from both of you.

Sorry I took so long to get back to this question (had some other fires that needed tending).

Thanks again.

Don
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38334822
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question