Importing and appending data

[David Ferguson]

I have a table [MainData] which holds 2000+ records and this is updated
three or four times a day by importing a Work To list from Excel.

I do this using a macro which has the following steps

1 Clear [ImportTable] using a delete query
2 Import Excel file into [ImportTable]
3 Append [ImportTable] to [MainData]
4 Delete [ImportErrors] table

The [ImportTable] will consist of all the records in the [MainData] table
(2000 approx) plus any new records (10-20 approx). This obviously takes a
while to run. Is there a better way of doing this that will maybe reduce the
run time of the macro.

Thanks in advance

Dave

 

[Nick Coe \(UK\)]

In David Ferguson typed:

I have a table [MainData] which holds 2000+ records and
this is
updated three or four times a day by importing a Work To
list from
Excel.

I do this using a macro which has the following steps

1 Clear [ImportTable] using a delete query
2 Import Excel file into [ImportTable]
3 Append [ImportTable] to [MainData]
4 Delete [ImportErrors] table

The [ImportTable] will consist of all the records in the
[MainData]
table (2000 approx) plus any new records (10-20 approx).
This
obviously takes a while to run. Is there a better way of
doing this
that will maybe reduce the run time of the macro.

Thanks in advance

Dave

Dave,
Why not link the spreadsheet, or appropriate part of it,
into Access as a table. Then create a query which selects
only those rows that exist in the linked table and not in
MainData.

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

 

[Frank Stone]

Dave,
i think Nick has a good idea. I use linked xl files all
the time. saves on disk space too. why have the same data
in two places.once linked the xl file acts just like a
access table that can be use any way an access table can
be used.
I would recomend the you link to a named range in excel
instead of the whole file. access will sometimes link to
blank rows and columns that it thinks contains data. You
can use this sub in xl's on close event to reset the range
to accommidate any new data.
this way it would always be up to date.
Sub macSetRangeName()

ActiveWorkbook.Names.Add Name:="RngName", RefersTo:= _
Range("A1:G1", Range("A1:G1 ").End(xlDown))
Application.Goto Reference:="RngName"

End Sub
Regards
Frank

 

[David Ferguson]

Frank, Nick,

Thanks for the ideas. My only worry is that the Excel file in question is
itself created new each time by importing a text file from our PM system and
using a macro to separate it into columns and removing unwanted rows. There
are also a couple of extra columns added in as well, and because records are
taken out and new records appear each time, its order and size are different
each time. Will this cause a problem with linking it to Access or am I just
better to carry on as is?

 

[Frank Stone]

hi,
yes a slight problem in nameing conviction. the linked xl
file would also have to have the same name and location.
but you are using a macro to do the text file import. how
are you running the macro in the new file. if you are
running it from another xl file, you could create the new
file, run the macro, copy the data into you linked file,
resize the range name all in one macro.
I have a time delayed macro that fires 4am each weekday.
it opens 11 other files and runs macros in them. all the
code is in the master time delayed file. You could do
something like that. maybe not time delayed. I still like
the linked file idea.
good luck
Frank
i will check back on this thread.

 

[Nick Coe \(UK\)]

In David Ferguson typed:

Frank, Nick,

Thanks for the ideas. My only worry is that the Excel file
in
question is itself created new each time by importing a
text file
from our PM system and using a macro to separate it into
columns and
removing unwanted rows. There are also a couple of extra
columns
added in as well, and because records are taken out and
new records
appear each time, its order and size are different each
time. Will
this cause a problem with linking it to Access or am I
just better to
carry on as is?

How necessary is it to massage the text file in Excel rather
than link or import the text file to Access?

Relatively straight forward to allow for file name/path
changes, reduce user intervention.

MVPS has a chunk of code to call the Windows file dialog.

http://www.mvps.org/access/api/api0001.htm

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----