Automating an XL import

[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

 

[Joe Fallon]

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.

 

[Brown]

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

 

[Joe Fallon]

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.

 

[Brown]

Thanks Joe
I'll give it a shot --

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.

 

[JohnK]

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.

 

[Brown]

John,
That worked well, Thanks --

Brown

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.