UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Appending Tow Tables With Relation To Onther Tables, Access 2010    
 
   
Sami
post Sep 30 2019, 08:21 AM
Post#1



Posts: 64
Joined: 3-April 19



Hi, I have a program to record the sent mail. I put it on tow computers in distant offices without net or connection bettwen them.
The program has tow tables with relation bettwen them, the first one for address and the second one for full mail and the key of relation is the id (autonumbering) in the full-mail table.
I want to append the tables on each data base to other one and keep the record with a relation.
So I import the tables with the relation form one data base to another manually but I can't append new table with old one in the data base and keeping relation properly
Cause when I append the imported full mail table to old one the key filed is Chang and get new value to fit the series of number.
And when I append the imported address table to old one they appear connected with wrong record couse the filed of relations has the value of the autonumbering field in the old full mail table.
How to append the imported tables with old tables and update the field of relation among records to the value of the new outnumbering filed.
Thanks
Go to the top of the page
 
GroverParkGeorge
post Sep 30 2019, 09:07 AM
Post#2


UA Admin
Posts: 36,033
Joined: 20-June 02
From: Newcastle, WA


This is expected behavior. You have two separate and totally independent Access Relational Database Applications. The Primary Key values for tables in one of them apply only to records in that table in that Access Relational Database Application. Therefore, when you try to copy them over to the other Access Relational Database Application, you get conflicts. Nothing you can do about that directly, although there are some potential workarounds. Depending on circumstances, you might be able to manage one of them, but it's going to require a significant programming effort to create a viable synchronization process. I would avoid that, if possible.

One of the main benefits of Access--in addition to being relatively easy to get started--is that it can connect to a wide variety of data sources, including other accdbs, SQL Server and other relational database management systems, even to Excel files. Here, that's going to be your best option. A single common data source is going to serve you better in the long run.

SOme options include using Office 365 and moving your Access tables to SharePoint as SharePoint lists. That way the Access Front End accdbs on each of your two computers at separate locations can both link to the same SharePoint Lists and work together,

Another option--and one that I would suggest more than SharePoint--is a remotely hosted SQL Server database, perhaps on a hosting site or on Azure. An Azure SQL database would allow both users to connect to it and work concurrently with your data. That eliminates the whole Synchronization conundrum.

None of those options is free, but all are low-cost. It's probably the most effective solution. Synchronization is a tough nut to crack unless you absolutely have no other choice.

I'm sure someone has the VBA and samples of queries needed to do this, and you might want to go that way. However, for $5 to $20 a month, a remote host, or Azure or O365 make a lot of sense for a critical business application.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Sami
post Sep 30 2019, 10:58 AM
Post#3



Posts: 64
Joined: 3-April 19



Thanks
But the big problem in my job that is there isn't any budget for the extra stuf , so I need something free.
Go to the top of the page
 
GroverParkGeorge
post Sep 30 2019, 11:07 AM
Post#4


UA Admin
Posts: 36,033
Joined: 20-June 02
From: Newcastle, WA


That's not uncommon, I'm afraid. Lots of times we run into conflicts between getting the job done and budget to do the job properly. I truly don't think that having a reliable way to share data remotely is extra in the sense that it's kind of crucial to getting the job done in your environment. That's not an extra component; it's part of the basic requirement.

Some organizations offer discounted services to non-profits. Is that an option?

I have only tried to do that kind of "roll your own" synchronization one time, quite a while ago. I don't have sample code, therefore, to do this.

That said, I have seen such procedures offered here. If you can't find one or more by searching our archives, someone should be along soon with something to suggest.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dale.fye
post Sep 30 2019, 11:56 AM
Post#5



Posts: 161
Joined: 28-March 18
From: Virginia


When I need to be able to synchronize data from un-networked computers with a networked computers, I generally use two fields:

1. Either the username or the computer name
2. a manually generated number (not autonumber) but which would be configured to be sequential and not repeat.
3. a Modified_DT field to allow you to identify which record was last updated if both the remote and local records have been updated recently.

This allows me to create queries for the synchronization process that allow me to update records which already exist or append records which don't.


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
nvogel
post Sep 30 2019, 01:12 PM
Post#6



Posts: 1,023
Joined: 26-January 14
From: London, UK


I agree with Dale's suggestion of using the computer name or location as part of a composite key. That's really the best way to handle "update anywhere" replication. Also you need to think about whether there are uniqueness constraints that ought to span both locations. Could it be that the same information gets updated in both places? If so then probably you will need a process to handle that and resolve any conflicts that arise.


This post has been edited by nvogel: Sep 30 2019, 01:13 PM
Go to the top of the page
 
Jeff B.
post Sep 30 2019, 03:34 PM
Post#7


UtterAccess VIP
Posts: 10,325
Joined: 30-April 10
From: Pacific NorthWet


A point of clarification … nvogel's comment mentioned a "composite key". My definition of that is multiple fields that, taken together, uniquely identify a record/row. I do not mean "put everything together into a single field".

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
nvogel
post Sep 30 2019, 04:23 PM
Post#8



Posts: 1,023
Joined: 26-January 14
From: London, UK


Thanks Jeff. That is what I meant: multiple columns rather than one.
Go to the top of the page
 
Jeff B.
post Oct 1 2019, 11:05 AM
Post#9


UtterAccess VIP
Posts: 10,325
Joined: 30-April 10
From: Pacific NorthWet


<nvogel>

I suspected you & I shared that definition, but I wasn't clear if the OP did...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Sami
post Oct 1 2019, 12:31 PM
Post#10



Posts: 64
Joined: 3-April 19



Thanks for all of you
I will edit the relation and make it among multiple column.
Thanks again.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 05:55 AM