Re: Best design for importing montlhly data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

See comments in-line below...

"JoeA2006" <JoeA2006@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:08A918BF-D621-4A0B-8335-56CE825AAF0E@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • RE: Showing data based on other values
    ... Importing and linking have two different purposes. ... data not in the local database and that you do not want in the database. ... To create an append query you start out creating a regular ...
    (microsoft.public.access.dataaccess.pages)
  • Re: Running Update Macro disables switchboard
    ... What are the reports and query that you are deleing and importing? ... The macro begins with 'setwarnings' to 'no,' and echo turned off. ... As mentioned in my first message, I placed a final line in to close the database, as a work around. ...
    (microsoft.public.access.macros)
  • Re: Export multiple Tbls from one Qry
    ... Ken I am getting a bit lost, I have made a copy of my database and have tried ... to rename my fields to match your naming convention. ... Syntax error in query expression 'ManagerID='. ... data in another table) to separate EXCEL files via TransferSpreadsheet ...
    (microsoft.public.access.externaldata)
  • RE: Auto Numbering
    ... importing to Excel on my mind. ... Select Queries in the database window. ... Select all fields and drag into the query matrix. ... "Andrea Stimson" wrote: ...
    (microsoft.public.access.tablesdbdesign)
  • RE: exporting an excel file into an access table
    ... directly from the database to the remote PC and append them. ... It still leaves me with the doubt of how to import excel files from one pc to ... Open a New query in design mode. ...
    (microsoft.public.access.externaldata)