ListObjects.Add Question

[Steve Haack]

I am building a workbook to automate reporrt generation for data in Access. I
have 40+ locations that I have to generate reports for, so I am creating
worksheets for each site, and quereying Access for the specific data.

What I would like to do, is on the worksheet for each site, have the data
that I need for that site and the specific charts that I need to generate.

I am using ListObjects.Add to add a QueryTable object to the worksheet. It
query's the access db and gets the data that I need. This part works fine. It
gets the data, and creates an xl table on the sheet.

My question, is how do I make changes to that query. Let's say that the user
wants to change the dates for the data being charted. How do I change that
query and bring the new data back into the existing xl table (so that the
chart updates itself)?

I haven't been able to figure out how to do that. I need to make sure that I
can change the query, re-run it and put the data into the previously defined
xl table. From what I can tell, the QueryTable object will not put the data
into a previously defined table.

Any guidance or pointers are greatly appreciated.

Steve

 

[Joel]

You can't change the connection to a query table but you can change the other
properties.

I usually record a macro when I start a query and then use then modify the
recorded macro as required. Yo need to modify the command text portion of
the query like the code below

With Selection.QueryTable

.CommandText = Array( _
"SELECT `0215 & 0064 parts received`.ID, & "
"WHERE (`0215 & 0064 parts received`.`Date Received`<{ts
'2009-03-23 00:00:00'})"
End With


Below I modified the above code to use a variable date.

todayDate = format(Date,"'YYYY-MM-DD) & " 00:00:00'"
With Selection.QueryTable

.CommandText = Array( _
"SELECT `0215 & 0064 parts received`.ID, & "
"WHERE (`0215 & 0064 parts received`.`Date Received`<{ts " &
todaydate & "})"
End With

 

[Steve Haack]

Joel,
I think this is right on with what I need to do. Do you have a larger code
sample that you can point me to so that I can take all this into context
better (I'm new at a lot of this).

Basically, I need to create the table the first time, then when users choose
different dates, I need to re-query the data and update the charts.

Thanks for the help,
Steve

 

[Joel]

It will be easier for you to see what is happening by strating the macro
recorder (tools - macro - start recording) and then perform the Query from
the menu Data - Import External Data