Refresh A Table or Stored Procedure in Access ADP

[Alibaba]

hi,
I am creating a stored proc (or a make table) with the same name but
different structure depending on which button is pressed in Access ADP. for
example if i press
button 1 the statement would be:
"SELECT * INTO mytempreportstable FROM table1"
then on a button2:
"SELECT * INTO mytempreportstable FROM table2"
Etc...
Then on the menu bar i have a button that opens >> mytempreportstable <<

of course mytempreportstable likely to have different structure each time
you open it.

Each time you do a "select * from mytempreportstable " in query analyzer you
get the correct results.

You get the right result the first time on the menu bar button. Afterwards
when you press button1 or button2 to button263 you get blank datasheet with
either the correct structure or the incorrect structure.

each time you refresh the connection by going File>Connection>Refresh
you get the right output.

I am trying to automate the refresh process but i dont know how?!

P.S. this painful task came about after microsoft turned off exporting
reports to excel in Office 2007. Most of our users do analysis in Excel. So
the only work around is to try to view the record source of the report in
datasheet view. you have the rest of the story now.

Thanks for your input
Regards
-- Alibaba ..

 

[Sylvain Lafontaine]

It's a common knowledge that ADP doesn't work well when the structure change
from one call to another. In your case, I would suggest that you use
aliases to hide this from ADP:

Select table1.A1 as A, table1.B1 as B, .... into mytempreportstable from
Table1

in order to have the exact same structure for mytempreportstable from call
to call. You could also refresh the metadata by closing and reopening the
connection or by calling the application.refreshdatabase method but in my
opinion, simply making sure that mytempreportstable keeps the same structure
would be better.