Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 2013 - Transfer Table Information From One To Another

Posted on 2015-02-24
12
187 Views
Last Modified: 2015-03-12
Hello Experts,

I am new to MS Access 2013 and have so far built a Master List table.  Periodically, I need to add 20 - 25 new records to the end of the Master List table.  Basically like adding new customers to the end of a customer table.  I am looking for a simple, safe and reliable method to import, export, copy or transfer these new 25 records into the Master File table without manually entering the data.  The data is currently given to me by a colleague in an excel format.  I have tried importing excel file and appending to end of Master List table.  It gives me a number of errors.  I then created a new temporary access table and attempted to copy, import and export with little success.   Keep in mind, I am somewhat new to Access and have been teaching myself.  I am not currently at the level of using macros or code to accomplish this task (if possible).  As I stated earlier, I am under the assumption that their is a simple, safe and reliable method for adding or appending this data to the bottom of my Master List table.  Thanks...
0
Comment
Question by:idejjedi2
  • 4
  • 3
  • 2
12 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 40628163
lets do it 1 step at a time
1. import the excel file to a NON existing table with this codes

 docmd.transferspreadsheet acimport,10, "NewTable", "c:\folderName\myexcelfile.xlsx", true, "Sheetname!"

2, create a query to append the imported records to the master list, making sure that you convert the field type from the NewTable to match the Master List table field type.

post back if you have any problem doing the above
0
 
LVL 23

Expert Comment

by:Eirman
ID: 40628204
You should firstly export your excel file as a CSV file (There's loads of info available on that).
Then import the CSV file into Access

If this gives you errors, you will have to re-arrange the field order in excel so the data types match up on importing.
0
 

Author Comment

by:idejjedi2
ID: 40628219
Hey Rey,

You have helped me out in the past and I always thank you for your assistance. I did not follow exactly what you said with the code you summarized above, but you put me on the right track.  For future reference for anyone else - I did the following:

1. Imported the excel file to a temporary database table.
2. Converted (if needed) field headers and data types to match the destination table that I want to "append" data to.
3. Opened Query Design and selected the newly created temporary database table.
4. Selected the fields that I wanted to "add" or "append "to the destination table.
5. Selected "Append" in the ribbon and selected the destination table and made sure that all the field names matched accordingly.
6. Ran the query...

I am not sure if their is a better more efficient way, but that is the way I accomplished the task.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628248
following what you outlined above;
are you getting any error? give more detail explanation.
0
 

Author Comment

by:idejjedi2
ID: 40628266
Rey, the steps I outlined above worked perfectly for me.  Thanks again for putting me on the right track.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628275
do you need more help?
0
 

Author Comment

by:idejjedi2
ID: 40628285
I will always need help with programming.  A lifetime learner... :) As far as this question, the steps outlined above worked exactly as I needed.  Thanks again.
0
 
LVL 23

Expert Comment

by:Eirman
ID: 40628290
Hi idejjedi2, Regarding your request for attention ....

Distribute the points as you see fit and if that means giving
all the points to Rey, I will not be in the least offended.

From the wording of your question, I thought you were seeking a non-code solution.
0
 

Accepted Solution

by:
idejjedi2 earned 0 total points
ID: 40628347
Eirman,  I am still learning the terminology in Access.  I utilized the Access ribbon, functions and design wizard to accomplish this task.  When I stated non-coding, I was specifically talking about macros and/or vba coding.  If I was confusing in my request, my apologies.  Again, thanks to everyone for their assistance.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 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