Autoload data at startup (from excel to access)

[natalie99]

Hello everyone

Progress on my project is going well, mostly thanks to everyone here's help :D

The last stage of the first stage (you know what I mean) is this:

I want to do either of the following:

a) create a Macro that runs at startup which will automatically load a file into my database and then run the final 4 queries and run the final macros which are 'Outputto' actions.

the file will always be the same name and format, and it needs to replace the data held in the already created table it will reside in, NOT add to the data.

or

b) create a form which allows me to do basically the same thing, although with the added benefit of having a BROWSE box where you can select which file to load (the file will still follow the template and need to replace the data in the table, not add to it).

Data file is called (approximately 45-50,000 lines by 60 fields)

LIVE.xls

Table for importing to is called

tblLIVEInventory

Final Queries

qryApprovedPayment

qryUnmatchedCID_R

qryUnmatchedCID_BT

qryMatchError

Macros

Export Unmatching Queries

Export Approved Payment Report

If anyone has any suggestions on the best option of a) or b) and a way to attack it please let me know!

Thanks :)

Nat

[MindBender77]

Hello everyone

Progress on my project is going well, mostly thanks to everyone here's help :D

The last stage of the first stage (you know what I mean) is this:

I want to do either of the following:

a) create a Macro that runs at startup which will automatically load a file into my database and then run the final 4 queries and run the final macros which are 'Outputto' actions.

the file will always be the same name and format, and it needs to replace the data held in the already created table it will reside in, NOT add to the data.

If anyone has any suggestions on the best option of a) or b) and a way to attack it please let me know!

Thanks :)

Nat

It might be easier to link this Excel file directly into your data base (Linked Table). Then, using a macro, run a delete query to delete all records form your original table and then an append query that adds the data from your linked table to your original table.

The easiest way to do what you ask is just link the file directly from data base and run your queries against it. This way you won't have to keep updating the table manually or using macros.

Bender

[natalie99]

thanks for the tip, this does indeed sound quite easy and solve my problem!

although i wish i knew that earlier! the table in question is the very first in a line of about 35 queries and 10 or so macros, if I change the name my outputs will all become Expr: 'blah'

any ideas how to overcome the name change problem?

thanks millions!

nat :)

[MindBender77]

thanks for the tip, this does indeed sound quite easy and solve my problem!

although i wish i knew that earlier! the table in question is the very first in a line of about 35 queries and 10 or so macros, if I change the name my outputs will all become Expr: 'blah'

any ideas how to overcome the name change problem?

thanks millions!

nat :)

You could try adding a 1 to the end of your original table's name and then name your link table the same as the original, not including the 1. This might fix the problem. If it doesn't, then you could simply remove the 1 from the name of the original table.

Bender