How to refresh SQL

[Helge V. Larsen]

I have written some VBA that (among other things) writes the SQL statements
associated with all queries in an Access database to a text file. The SQL is
retrieved in this way:
For Each aQueDef In CurrentDb.QueryDefs
aSQL = aQueDef.SQL
' Write SQL to text file
Next aQueDef

However, there is sometimes an error in the reported SQL:
If a field name in a table is changed, then the SQL (as reported by my VBA)
associated with a query that draws on this table is NOT updated accordingly.
The old field name is still used!

If I open the query (in design mode or in 'data' mode) and close it again
without being asked weather to save it or not, then the SQL of the query is
reported correctly by my VBA.

How can I in VBA or in another way do this update/refresh of the SQL ?

 

[strive4peace]

Hi Helge,

after you replace the SQL, try

currentdb.querydefs.refresh


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Helge V. Larsen]

CurrentDb.QueryDefs.Refresh does not help.
Please have a look at my communication with John Spencer.

Helge
__________________

 

[strive4peace]

Hi Helge,

I'd love to, John always has good knowledge. Please tell me:

newsgroup
subject
date/time of original post (and time zone)



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Helge V. Larsen]

Hi Crystal

I thought it was easily found since it is within the same main thread as
your posting.

Newsgroup : microsoft.public.office.developer.vba
Subject : How to refresh SQL
Date : Mon, 23 Apr 2007 14:32:46 +0200
NNTP-Posting-Date : Mon, 23 Apr 2007 12:32:47 +0000 (UTC)

Kind regards,
Helge
________________________

 

[strive4peace]

thanks, Helge -- but I do not see John's posts with T-Bird

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Aaron Kempf]

yeah I don't see Johns' post either

maybe if MS would stop CENSORING THE PUBLIC NEWSGROUPS then this wouldn't be
so difficult

 

[Steve Rindsberg]

yeah I don't see Johns' post either

maybe if MS would stop CENSORING THE PUBLIC NEWSGROUPS then this wouldn't be
so difficult

Before making accusations, you might want to try some other tool to read the
newsgroups, or if you're using an alternate news server try going directly to
msnews.microsoft.com instead.

Here's the post I think you're after. I've left the rest of the quoted stuff
deliberately, in case you're unable to find the precedent posts. Hope this
helps ...

====================================================

Ok, as I said I don't use those options.

The following is SHEER SPECULATION on my part.

You shouldn't have to run the queries, just open and close them.

You can do one query with code like

DoCmd.OpenQuery "Query1",acViewDesign
DoCmd.Close acQuery,"Query1",acSaveYes

You could surround that with DoCmd.Echo False then True to prevent the
screen flash. Be careful with DoCmd.Echo. If you have it set to false and
your code errors and you don't have an error handler that handles the error
and turns DoCmd.Echo True you could be stuck with an unresponsive screen.

DoCmd.Echo False
DoCmd.OpenQuery "Query1",acViewDesign
DoCmd.Close acQuery,"Query1",acSaveYes
DoCmd.Echo True

You could loop through all the queries

Dim QName as String
For iLoop = 0 to CurrentDB().queryDefs.Count -1
QName = currentdb().querydefs(iLoop).Name
If Left(qName,1) <> "~" then
DoCmd.Echo False
DoCmd.OpenQuery qName, acViewDesign
DoCmd.Close acQuery,qName,acSaveYes
DoCmd.Echo True
End if
Next iLoop

Also, I would GUESS that there is no way to avoid changing the Last Updated
date.

Hope this helps
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Robert Morley]

MS was in fact censoring his posts. He'd been blacklisted, presumably
because of his continual rants, abusive posts, and misinformation
(disinformation?) about various MS products. Other newsgroups, however,
would reflect his posts, so you'd get things where people like me, who use
the msnews server, who would never see his original posts, only the replies
to it.

Noticeably, he's since acquired a new e-mail address, which MS is not
currently censoring (though I suspect it won't be long, the way he's going).


Rob

Before making accusations, you might want to try some other tool to read
the
newsgroups, or if you're using an alternate news server try going directly
to
msnews.microsoft.com instead.

<remainder snipped>

 

[Robert Morley]

Other newsgroups, however,

Woops, I meant "Other news servers"...as in non-MS servers.


Rob

 

[Carl Rapson]

These aren't public newsgroups, they're owned by Microsoft. Microsoft can do
whatever it wishes with these newsgroups and the posts in them.

Carl Rapson

 

[Steve Rindsberg]

Woops, I meant "Other news servers"...as in non-MS servers.

In context, that's what I read it as. Thanks ...

 

[Steve Rindsberg]

MS was in fact censoring his posts.

But apparently not the post in question, so the accusation in this case appears
unfounded. Unless it's in my offline reader's cache but no longer on the
server.

He'd been blacklisted, presumably
because of his continual rants, abusive posts, and misinformation
(disinformation?) about various MS products. Other newsgroups, however,
would reflect his posts, so you'd get things where people like me, who use
the msnews server, who would never see his original posts, only the replies
to it.

There are several web based forums/newsgroups that slurp posts from this server
but don't necessarily feed back posts made to their own servers. It's always
possible that the posts seen elsewhere but not here were of that type.

On the other hand, if the posts are abusive, I'd hope that they'd be removed,
though on a case by case basis.

IAC, thanks for the add'l information.

 

[Aaron Kempf]

steve

stfu microsoft censors me.
if you disagree then come down and call me a liar TO MY FACE kid

 

[Aaron Kempf]

dipshits in India.. that get this-- THEY WORSHIP COWS!!~~!!


I mean.. do they really think that they make a better developer than ME?

ROFL

 

[Susie Johnson]

you might not see posts-- because MIcrosoft needlessly censors these PUBLIC
newsgroups

 

[Robert Morley]

Or it might be that Microsoft had a few problems with the server a week or
two ago, and several posts went missing, even though others by the same
poster would show up.

I think it's only you they're censoring, "Susie", nobody else posts that
kind of vitriol, so they don't need to be censored.


Rob