Automating Append Query

[Boochy]

Hello Everyone,

I am in the process of trying to automate a daily task of appending data from my excel file (which is already automated) into my access database.

I was able to use SetWarnings to make the database ignore the first onscreen prompt. The issue I am having now is I have not figured out a way to make access ignore other popup boxes (Enter parameter values).

Is there a way to do this?

The other issue, which I have no control over, is the data from excel has ever-changing column headings, so I can't rid access of the enter parameter pop up boxes by deleting the column that doesn't exist on a given day because it could very well show up the following day.

Any help is greatly appreciated!

Boochy

 

[BeerSnob]

I don't know if this will help or not, but...
Have you thought about saving the data from the spreadsheet, rather than trying to import it?

In the workbooks OnSave event, you could build an execute a query that sends data to your database. This would run automatically every time somebody saved the workbook. You could validate column headings long before executing the query - and your parameters issue goes away.

 

[Boochy]

This may indeed work, but I want to get away from having to validate or even look at the query's results.

Thanks for the response.

 

[BeerSnob]

If you're going to let anyone -other than yourself - do input into the spreadsheet, you're going to have to validate their inputs. Users can
be pesky about inserting rows/columns in the wrong place, or putting text in a cell that should be numeric.

There's no reason you should have to view the query's results before importing it. If the data is valid, it just executes the query.

Not sure I follow how you expect to import an unpredictable number of columns into a database without encountering a few problems.

 

[Boochy]

Actually no one inputs data into the main spreadsheet. The data is compiled automatically. I just query the data and arrange it into excel first.

Thanks,