Solved

Importing Access Data into Access

Posted on 2015-02-12
19
136 Views
Last Modified: 2016-02-11
If I have filtered data from 1 main table and 20 linked tables. what is the best way to send that data to a colleague to upload into his identical access application. We cannot share the data source.

All tables use Random number type as primary key.

I have highly detailed project specification that should go direct to a supplier. Both have same access application
0
Comment
Question by:DatabaseDek
  • 7
  • 5
  • 3
  • +3
19 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 40606214
what do you mean by "to send to a a colleague"? where is this colleague?

export the filtered data to a text file and send by email, perhaps?
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 40606649
The only reliable method for maintaining unique keys is to use GUID values.  They key space on GUIDs is many orders of magnitude larger than 2^31-1.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 40606669
Ultimately "Sending" data to users is not the most efficient way to get data into a database.
You cannot easily "send" records (or record edits) to other users.

Your requirement of "filtered data from 1 main table and 20 linked tables",...complicates this even more...

Can we ask why you cannot share the same data source?

It is also not clear what this means:
"I have highly detailed project specification that should go direct to a supplier. Both have same access application"
...?
1. define: "highly detailed project specification"
2. So is the Supplier the Colleague

In other words, ...can you take a step back and explain to us the nature of the existing database, and why it was structured in this way?

JeffCoachman
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40606692
Can you import/export the records into/to an Access database and send that file?
0
 

Author Comment

by:DatabaseDek
ID: 40607510
An architect creates a Fire Door specification for a building and wants to send it to either a builder or to a fire door manufacturer for pricing or manufacture. There is the equivalent of a 500 column spread sheet's worth of data with 1 line for each door. The problem is that the data comes from a Project table with 20 linked tables.

What happens at the moment is that the builder or fire door manufacturer has to now manually copy the same data into their version of the same Access application. This is nonsense and should be exported/imported without error.
There can be hundreds of doors in each project. now consider that the architect will send the same specification to 5 or 6 suppliers all of whom will have the same imput/copying task and you can see the depth of the problem
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40607649
Do the Builder or door manufacturer then have to send the data back to the architect at some point?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40607759
You create a small database with the same tables as are linked in the big (project) database.  You transfer necessary data to the small database and send it.
0
 

Author Comment

by:DatabaseDek
ID: 40607889
Hi Dale
No, The data does not have to be returned.

Allimark
The data has to be transferred or imported to the second users access application
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40607902
@DatabaseDek
have you considered my suggestion of dumping the information to  a text file?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Expert Comment

by:aikimark
ID: 40607992
Do the recipients of the data have similar (matching) tables in their databases?
0
 

Author Comment

by:DatabaseDek
ID: 40608209
Hi Rey

If I can then import the text file in a way that sends all of the data to the correct tables and creates linking field data that would be great. I suspect (but do not know)that an export to excel would be easier?
0
 

Author Comment

by:DatabaseDek
ID: 40608220
aikimark

The tables in both applications will be identical
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40608249
@DatabaseDek
1. you need an export specification (on your side) for exporting the data to .txt or .csv
2. create an import specification to import the records from the text file and test if records are imported correctly
    a. send the import specification to the receiver of the file so they can add the records to the access db correctly.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40608397
You should be able to create append queries for each table, assuming that one side of the append will be linked tables to the other or use the IN keyword to point to the location of the source or target database.

You could also run some VBA code that would construct and run the append queries dynamically.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 40608487
Since you are simply exporting this data, I would probably create a database using the dbengine.CreateDatabase method.  And would then write a series of queries to create new tables in that database, something like:

SELECT * INTO tblMain
IN 'E:\yourTransportDatabase.accdb'
FROM tblMain
WHERE tblMain.Building = 1

Obviously the WHERE clause would depend on what you wanted to export, but I'm assuming as an architect, you would not send specifications for a single door or group of doors, but for an entire building.

You would then create a series of other queries to export the data from the other 20 tables, all being created using the make table query in the new database.  Then, on the other end, you would import the data into the tables of the users applications with similar queries.  The key here is that you would have to ensure that if you have relationships defined in your database, with referential integrity, that you import the tables in an order which will not violate primary key/foreign key constraints.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 100 total points
ID: 40608844
If the receiving vendor NEVER adds rows to the tables, then sending 21 separate files - one for each table - would allow him to append the data and have it link up.  If the vendor does update the tables, then you can't send records with pre-populated foreign keys.

First look into using XML to transfer the data.  I'm not sure if Access is smart enough to retain the table segments correctly.  You may have to build the XML file manually.  Same for importing.

Another alternative would be to write your own append code.  You would create a mini-app that contains only the necessary tables (empty) and the code to link to the tables in the client app and then append the rows using the DAO .Addnew method, inserting the appropriate foreign keys..  Your main app would export data to each table including the foreign key values.  Since there is no other data, there will be no conflict.

Then every time you need to send something to a client, you grab a clean/empty template and append the data.  Send the database.  At the other end, the client powers up your updater app, links to his tables and your code appends the data to the correct tables.
0
 

Author Comment

by:DatabaseDek
ID: 40611017
Thank you all. I will need some time to consider all this. Sending and receiving a txt file sounds great . I have had XML mentioned before so might need someone to do that for me. Creating a Dbase with code also sounds good. You have given me some work to do. I will come back later.

And again thank you all.
Derek
0
 

Author Comment

by:DatabaseDek
ID: 40628880
Thank you all. Great ideas. I think the best for me will either be Ray's text file or Pat's XML

As I can do neither I may need someone to do it for me. How do I get an expert to quote me.
0
 

Author Closing Comment

by:DatabaseDek
ID: 40628881
Brilliant. Again

Derek
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now