Best design for importing montlhly data

[JoeA2006]

I have to import data from several various excel files and other tables into
an application. I need to import each file into its own temporary table that
can be over written each month. I want to create command buttons to do this
process from a form.

1. For the access tables I import from other databases, should I run a
delete query, to delete the old version of the table then import, or
should I run a make table query and overwrite it?

2. I have basically the same question for the excel files. What is the best
way to use the TransferSpreadsheet method to overwrite the old table?

 

[Jeff Boyce]

See comments in-line below...

I have to import data from several various excel files and other tables
into
an application.

Remember that, once imported, your Access data and your source data are at
risk of being out-of-sync. Would it meet your needs to simply "link" to the
data, rather than importing a copy?

I need to import each file into its own temporary table that
can be over written each month.

Are you saying that each "file" you import now has a different structure?

I want to create command buttons to do this
process from a form.

If you have many (i.e., more than 2!) files to import, having a button for
each would take up a lot of screen real estate. If all files are available
at the same time, you could create a process to update all, and use a single
command button.

1. For the access tables I import from other databases, should I run a
delete query, to delete the old version of the table then import, or
should I run a make table query and overwrite it?

A delete query doesn't delete the table (structure), just the data in the
table. A make table query will wipe out the old table before
importing/creating a totally new table. If the data you are importing
(remember, check into linking instead) is structured identically, I'd create
an Access table that had the data types I needed, then import into that
table (after deleting all rows). So, why DO you need to delete the old rows
before appending?

2. I have basically the same question for the excel files. What is the
best
way to use the TransferSpreadsheet method to overwrite the old table?

?Link instead?

Note that you could simply link to all your "outside" data, then run queries
that update your permanent tables based on those links.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

You might also want to check the options you have set under:
Tools--Options--Tables/Queries--Auto Index on Import/Create. This is where
auto-indexing of imported columns is specified. If you have columns named as
specified, Access will automatically create indexes (sometimes totally
inappropriate) -- which can slow down your import process considerably. I've
been able to reduce large imports from hours to minutes by changing this
specification.

Dorian.

 

[JoeA2006]

Can I link excel files to the database even if the xls is a new file each
month rather than one that is updated. Would the link still work when the new
file is replaced? ( with the same file name and format)

 

[Jeff Boyce]

Joe

If you tell Access to look for an Excel-type file located in
C:\YourFolder\YourExcelFileName.xls, that's where Access will look.

If you pull a fast one on Access and substitute THIS month's Excel file for
last month's, with the same file name and in the same folder, Access won't
even know you've been there...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JoeA2006]

I felt like I need to delete the old data because there is nothing in the
spreadsheets that differentiates which period the data comes from. The
process I thought of was as follows- 1. Import the external spreadsheet or
table after deleting the old table. 2. Use the new data to run a query and
add a column for the month the records came form. 3. Append the data from
this query to my permanent table, where each record would have a month
associated with it.

The whole goal here of course is to automate as much of this as possible. I
have to work around some of the limitations of the external data of which I
don't have control of the fields, format etc. Is there a way to manipulate
the records with a series of queries to load them into the perm table "in one
step"?
Thanks

 

[Jeff Boyce]

"in one step"...?!

Even if you figure out 10 separate queries you need to run, you can still
create a macro to run all 10, so is that "in one step?"

Even if the spreadsheet data does not include a date or date/time field, you
probably could still come up with a more automated way to extract the
spreadsheet data and parse it into permanent, well-normalized Access tables.

I don't understand what you mean by "... run a query and add a column for
the month...". Are you saying that your permanent table structure uses
MonthNames as fields (i.e., columns)?

Regards

Jeff Boyce
Microsoft Office/Access MVP