Import table

[Jeff]

Hi,

We have a series of queries that are run every other day.

Before we run the queries we import a table into acess. Here is the problem.

We import into a table in access from excel. How can we automate this so
that the data in the acess table is replaced. When we import data to an
existing table the data is appended on the end.

THanks for your help

 

[Donna]

Jeff,

When you import into Access, you can choose whether to replace the table or
append the data to an existing table. Access Help can tell you how to change
the way you're importing. Or let me know what version of Access you're using
and whether the import is automated & I'll try to help some more.

Donna

 

[Jerry Whittle]

If you want all the data removed from the table first, create a Delete query
for that table. Run it before importing the data.

If you want to replace or update records already in the table, it becomes a
little more complicated. You'll need to first import the Excel data into a
temporary table or link the Excel spreadsheet as a table, then update or
replace data in the primary table from it. For this to work, you need a
primary key set up in the table. Do you?

Do you use a Macro, code, or run things manually to import the data and run
the queries?

 

[Jerry Whittle]

If you want all the data removed from the table first, create a Delete query
for that table. Run it before importing the data.

If you want to replace or update records already in the table, it becomes a
little more complicated. You'll need to first import the Excel data into a
temporary table or link the Excel spreadsheet as a table, then update or
replace data in the primary table from it. For this to work, you need a
primary key set up in the table. Do you?

Do you use a Macro, code, or run things manually to import the data and run
the queries?

 

[Wayne-I-M]

Hi Jeff

You could use
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to file",
acTable, "table name", "table name", structureonly:=False

Include in the code a check to see if the table already exists and if it
does then delete it. You need to do this or your will rename the table/query
like this
tblABC
after import new table will be called
tblABC1
then
tblABC2
etc.

I would import the table then run the query on this new table - as long as
it's the same name you will have no probelms

 

[Larry Daugherty]

Before importing data, run a Delete query on the table.

HTH