Automating an XL import

  • Thread starter Thread starter Brown
  • Start date Start date
B

Brown

Running Access 2K3.

I have an Access app that is being sent to field offices. They will use
forms in this app to enter records which are then emailed (as XL
spreadsheets) via a macro to our office for inclusion in our central
database.

The records are about fifty (50) fields, six of the fields have names that
need to be adjusted since the remote application is designed to require
these to have data, (Access renames them to include "is a required field").
I have no problem massaging the files and importing them manually, but I
need to set up a "one-button" procedure to append the data to the existing
master data table.

What is the best approach to attempt in order to set this up?

Brown
 
Import them to a staging table every time.
Then write code/queries to massage them in the staging table.
Then write append query to move the clean data to real table.
 
OK, makes sense, I was looking at doing a macro, but could not find the
command for import. Is a macro not the way to do this?

Brown
 
Macros are for beginners.
Once you learn that they are really just VBA code - you may as well use real
VBA code.
Logically structured code is just as easy to read as a macro.
Use DoCmd.TransferSpreadsheet in code.
See Help for details.
 
Thanks Joe
I'll give it a shot --

Brown

Joe Fallon said:
Macros are for beginners.
Once you learn that they are really just VBA code - you may as well use
real VBA code.
Logically structured code is just as easy to read as a macro.
Use DoCmd.TransferSpreadsheet in code.
See Help for details.
Click to expand...
 
I do a lot of these type of imports where the data has to be "massaged"
first. You can use macros (Joe is right the VBA is better but macros do
work). I use Transfertext or transferspreadsheet to get the data in what I
usually call tblImport. My macros then run a series of queries that
manipulates the data and then appends the massaged data to the main table.
They work with no issues.
 
John,
That worked well, Thanks --

Brown

JohnK said:
I do a lot of these type of imports where the data has to be "massaged"
first. You can use macros (Joe is right the VBA is better but macros do
work). I use Transfertext or transferspreadsheet to get the data in what
I
usually call tblImport. My macros then run a series of queries that
manipulates the data and then appends the massaged data to the main table.
They work with no issues.
Click to expand...
 
Back
Top