Sending tables from 2 computers to get ONE excel sheet OR 1 Acc' table

[cuok]

Hi ALL!
Maybe this mission is not possible but it is a bit frustrating us so we must ask first:

My wife and me are working on 2 computers with same Access Program so we have identical structure Access tables but not same pupils (7 tables on each comp.).

From time to time (2-3 weeks) we need to combine the identical tables from the 2 computers, to one table.

What we used to do (till now!) was to send the table by e-mail from computer A to computer B using this command:

DoCmd.SendObject acSendTable, "TblMarksCourseD", acFormatXLS, "our@e-mail ", , , _
"Current Spreadsheet of Biochemistry", , False

Then we exported the “TblMarksCourseD” table from computer B to itself as excel sheet. Then combined the 2 tables to 1 by “copy and paste”. After this action we have a union excel sheet made of "TblMarksCourseD” from computer A and “TblMarksCourseD” from computer B


Then we repeat this action to all other tables and at last we have 7 sheets made of all 14 tables.

Is someone there can simplify what we are doing?

To be more clearer (we hope):

We need an automatic way to send to same e-mail 2 identical tables (by structure not PK) and to get them as ONE combined excel sheet !!


Thank you in advance for any help or hint

CUOK

 

[pdupreez]

As an initial fix to just get it into one Access point I would suggest you link the tbles from one computer to the other one. If you and your wife is on a network, share the directory on your wifes computer for example as "DATA" and the in you Access program under the "TABLES" right click in an open area, and select "Link Tables".

Browse to her "DATA" directory or add it directly using \\WifesComputerName\DATA\DatabaseName.mdb, Select the tables you want to link and voila, the table's data should now appear in your group of database tables. If they have exactly the same name, they will appear as SameName1

You can now create a query where you can combine the DATA from the two tables SameName and SameName1, and export that data to Excel using a form and a small piece of VBA code. You should also be able to directly import the query data from Excel using the Data import wizard.

Hope this will help

Regards


Pieter
PROUDLY SOUTH AFRICAN

 

[cuok]

Thank you Mr. Pieter!


Physically it’s can’t be done (to connect the computers). my “work room” is something like a warehouse behind the house!!

So we need the e-mail idea!

Hope you can help in this too!

CUOK

 

[pdupreez]

Hi CUOK

There is maybe two ways of doing this:

One : If you cannot connect via network, you can, if both are connected to a modem on different telephone lines, set up a dial up network connection between the two computers.

You can then on connection transfer the data from one machine to the other, in more or less the way as explained above, or you can copy the Database of the one machine onto the other machine, and then link the database directly to the other copied database on your machine or

Two : e-mail the sheets as before from wife to yourself. Save attachments from e-mail in a specific folder. Link directly from Access to the spreadsheets (right click on the tables area) and select "Link Tables"

In the file selection select "Files of type" at the bottom as Microsoft Excel, and then select the specific filename such as "Biochemistry" or whatever you named them. This will allow you to use the spreadsheet as you would use any other table, inside ACCESS.

By then creating a new query combining the data from your "HusbandsBiochemistry" to your "LinkedWifesBiochemistrySpreadsheet" which you have linked as explained above, you should be able to have an "almost up to date" connection to your wifes data, and can every time when she e-mails you the sheets, just copy them over the old files, and they should automatically update your tables and subsequent combined queries.

You may have to close your database before saving the files, as you could experience a share violation.

If you want to send me a copy of your database (without data is better), I can play around with it and send you a more detailed answer perhaps, or something.


Regards

Pieter
PROUDLY SOUTH AFRICAN
pdupreez@yahoo.com

 

[cuok]

Thank u my Dear!

Sorry i can't continue and apply your advice NOW, because of the "SABBATH" - I'LL continue (this time + 1 hour) tommrow

thank a lot and
CUOK