Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Import excel column range into access table fields based on key field

Posted on 2012-09-04
5
Medium Priority
?
694 Views
Last Modified: 2012-10-02
I'm trying to bring data into an access table that appends to existing records i..e adds new field values to existing records rather than adding new records.

I'm using access 2010 and have attempted Import Export Spreadsheet macro version but without success. When I run the macro teh timer spins but no data transfers.

Any help - gratefully received. Just need some tips on how to go about validating what I have set up for the import.

thanks

Ciaran
0
Comment
Question by:Needy11
  • 2
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38363705
this will import the excel records from sheet1 columns B1 to F100

docmd.transferspreadsheet acimport,10,"tableName","c:\folderName\myExcel.xlsx",true,"Sheet1!B1:F100"
0
 
LVL 40

Expert Comment

by:als315
ID: 38363725
May be you can show sample of source data and expected result? Do you like to add columns to existing table?
0
 

Author Comment

by:Needy11
ID: 38367195
Hi, Yes I want to add columns to existing data records. In the attached sample its the online time, offline time and total time columns that need to be added. is there a way of specifying which field\column each will go into?
samplecolumns.xlsx
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38367844
I prefer to link excel tables to DB. In this case you can modify existing table at first and then use query and add necessary information
0
 

Author Closing Comment

by:Needy11
ID: 38454018
tx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

578 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