automating imports from excel to Access 2003

  • Thread starter Thread starter mzubler
  • Start date Start date
M

mzubler

Hi,
I'm trying to set up a process where I'm pulling data from another
application via an excel spreadsheet on a periodic basis. The other app is
a collection point for change requests. The key on my data files is this
change request # . I need to import new requests and append them to an
existing table. I also need to pull closed requests and update existing
records in the same table. In Access, I have a table with the data from the
other app and a related table where I'm putting information / notes about
these requests. Again, the key to all of these is the change request #.

I've set up the transfer spreadsheet macro to pull in new requests into an
blank table and then run an append query to get new records. I need help on
this because I'm getting import errors. I've seen references to building
import specs. Can you point me to more info on this procedure?
And I've also set up an update query to update the requests which have been
closed.

At this point, I think I've complicated this whole process so any
suggestions to simplify would be welcome...;-) My skill level is such
that I can tweak the VBA code but I'm still not comfortable with adding new
procedures.

Thanks for any help,
Marge
 
Hi,
I'm trying to set up a process where I'm pulling data from another
application via an excel spreadsheet on a periodic basis.  The other app is
a collection point for change requests.  The key on my data files is this
change request # .   I need to import new requests and append them to an
existing table.  I also need to pull closed requests and update existing
records in the same table.  In Access, I have a table with the data from the
other app and a related table where I'm putting information / notes about
these requests.   Again, the key to all of these is the change request #.

I've set up the transfer spreadsheet macro to pull in new requests into an
blank table and then run an append query to get new records.  I need help on
this because I'm getting import errors.  I've seen references to building
import specs.  Can you point me to more info on this procedure?
And I've also set up an update query to update the requests which have been
closed.

At this point, I think I've complicated this whole process so any
suggestions to simplify would be welcome...;-)     My skill level is such
that I can tweak the VBA code but I'm still not comfortable with adding new
procedures.

Thanks for any help,
Marge
Click to expand...

If I were in your shoes, I would do this:
1. link to the spreadsheet (File, Get External Data, Link...)
2. create a query to find the new records (used the Find Unmatched
query wizard), then turn that into an append query.
3. create a query to find the existing records (link the two tables on
the join field). then turn it into an update query.

then all you need is some code to kick it off... a macro or some
VBA...
something like...

SetWarnings False
DoCmd.TransferSpreadsheet... acLink...
DoCmd.OpenQuery "AppendNewRecords"
DoCmd.OpenQuery "UpdateExistingRecords"
DoCmd.SetWarnings True
 
Back
Top