Sites: GlobalSpec.com | GlobalSpec Electronics | CR4 | Electronics360
Login | Register
The Engineer's Place for News and Discussion®

Previous in Forum: Medical Instruments Tracking Software   Next in Forum: Installation of Dll File
Close

Comments Format:






Close

Subscribe to Discussion:

CR4 allows you to "subscribe" to a discussion
so that you can be notified of new comments to
the discussion via email.

Close

Rating Vote:







4 comments
Power-User
Engineering Fields - Electrical Engineering - New Member

Join Date: Aug 2007
Location: Toronto
Posts: 237
Good Answers: 8

Updating Access 2003 Tables from 2 Excel Sheets

02/28/2012 4:56 PM

Hi Folks,

I have two excel spreadsheets, Archive.xls and Current.xls. They are identical format - the difference being that the Archive.xls contains dates from before Jan 1 and the Current.xls contains only the records from Jan 1 forward. {I needed to separate them since I run reports only on current data and including all the earlier records chokes my computer. The records go back some twenty years, making for a very large data set.}

In Access, I've Linked these to two tables named tblArchive and tblCurrent. Then, I created a new table called tblAll and wrote two Append Queries to load tblArchive and tblCurrent into my tblAll. I ran both queries and verified that all records were brought in.

So far so good.

Here's my problem. Since the first two tables are linked, any updates to my Excel sheets are reflected in the tblCurrent and tblArchives; however, my tblAll does NOT get updated and I can't run the queries again since that would duplicate existing records.

Somehow I need to pass the updates from the linked tables to my tblAll but I can't figure out how to do that.

Any guidance or suggestions would be greatly appreciated.

Thanks

Register to Reply
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Active Contributor

Join Date: May 2007
Location: The wilderness of NH - USA
Posts: 19
#1

Re: Updating Access 2003 Tables from 2 Excel Sheets

03/01/2012 7:02 AM

Have you tried to write a macro that increments the file using a date attach to the file name that changes when a file is querried.

Register to Reply
Guru

Join Date: Feb 2012
Location: just died off here
Posts: 522
Good Answers: 1
#2

Re: Updating Access 2003 Tables from 2 Excel Sheets

03/01/2012 8:53 AM

if i get you correct your [[Excel ] ]=>[[Access ] ]=>[tblAll ]
·······································\_____ _____/
················································\/
············································duplicate
·················································\_____ _____/
··························································\/
······················································triplicate

whatever
before writing a SUB that'd append "New data" to tblAll
i reccommend you
• copy [tblAll ] to another filename or location
• append "New data" to [tblTest ] (containing 1 .. few records in it)
• find/position pair of last matching records in DB copy2 & DB copy3
• transfer/append "New Data" records to DB copy3
• if prev works then ... trivia

i have some access foxpro experience but i don't do std. DB programming
(coz i don't do std. anything too much if any)

Register to Reply
Power-User
Engineering Fields - Electrical Engineering - New Member

Join Date: Aug 2007
Location: Toronto
Posts: 237
Good Answers: 8
#3

Re: Updating Access 2003 Tables from 2 Excel Sheets

03/01/2012 11:56 PM

Oy, not much help here folks. Lots of reads but few responses.

I checked on a different forum and someone pointed out I should be using the Union Query instead of the Append. Did that and it worked just fine. Hope this helps anyone following this thread.

Register to Reply Score 1 for Good Answer
Guru

Join Date: Jun 2011
Location: Phnom Penh
Posts: 3144
Good Answers: 86
#4
In reply to #3

Re: Updating Access 2003 Tables from 2 Excel Sheets

03/03/2012 3:11 AM

Thanks for sharing the third party provided solution.

__________________
Difficulty is not an obstacle it is merely an attribute.
Register to Reply
Register to Reply 4 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Copy to Clipboard

Users who posted comments:

ci139 (1); coyot1 (1); Graebeard (1); Wal (1)

Previous in Forum: Medical Instruments Tracking Software   Next in Forum: Installation of Dll File