Passing Excel Data into Access queries

  • Thread starter Thread starter samirpx
  • Start date Start date
S

samirpx

I'm having a hard time with this, and I don't know if it's possible.

1)I have an excel file that queries a SQL table, then proces it. <--no
probs
2)I want the process data to go into a premade Access query as a
criteia for the query.

for example I pass a certain date from excel to the access query where
all records are less than that date.

Currently i'm manually typing in the date from the excel into Access,
but I need to make it automated where I can just open the excel file
and it automaticly runs all the processes.

Is this possible??
 
Not sure if this will help at all, or whether you've tried it already.

You could perhaps do it all from within ACCESS by adding Excel to your
access VBA references.
I have an access job that opens a spreadsheet, and uses data on it to
process information from other worksheets into access tables, allocate next
numbers in the control section the excel workbook, and save them.
Alternatively, you can add ACCESS as a reference to the Excel VBA

If you're only collecting a single date, 'my' technique for using unlinked
single record tables in a query might help.

Your main query has all your 'column' criteria already. Get Excel to update
a single record access table with a field called "FromDate".
Add your unlinked single record table, and to your selection criteria put in
under the appropriate field
= [fromDate].
Click to expand...

In Excel. open the access single record table and put your from date into it.

Then in Access just run the query. (no updating or messing with paramter
queries, just run it.

Hey presto. No parameter queries, your code is easy since it always runs the
same query PLUS you can always tell which date you last ran it for.

Any use???
 
Back
Top