Accees2000 queries in Module 1

[bwmarents]

How can I - excuse my ignorance - run a select query from a module?

I want to run the query a couple of times on different database libraries (on a AS400 through ODBC). It works as a seprate query, but I want to run it from a module on different tables.

query:
SELECT ANP01D_ANVS.VSNR, ANP01D_ANVS.VSRCSTCD, (SELECT COUNT(UNNR) AS Aantal FROM ANP01D_ANUN where ANP01D_ANUN.VSNR=ANP01D_ANVS.VSNR) AS [Aantal Units]
FROM ANP01D_ANVS, ANP01D_ANUN
WHERE (((ANP01D_ANVS.VSRCSTCD)=1) AND (((SELECT COUNT(UNNR) AS Aantal FROM ANP01D_ANUN where ANP01D_ANUN.VSNR=ANP01D_ANVS.VSNR))>0));

Where 01, could also be 45 or 95, depending on the library I am accessing.

Thanks in advance.
Bernd

 

[Cagliostro]

docmd.runsql("your sql statement here&quot John Fill

ivfmd@mail.md

 

[bwmarents]

Thanks. I tried that, but the helpfile says it only can be used for action or definition query....not for a selection query. Is that correct?

 

[Cagliostro]

Yes.
I do not know exactly, but I think you could in a query specify the path to source database like
select * from [c:\xx.mbd].OneTable John Fill

ivfmd@mail.md

 

[bwmarents]

Hwen I do this:

DoCmd.RunSQL "SELECT ANP01D_ANRG.RGNR, ANP01D_ANRG.RGRCSTCD, ANP01D_ANRG.RGNM, ANP01D_ANRG.VSNR, ANP01D_ANRG.RGTLNR, ANP01D_ANRG.RGMGNM, ANP01D_ANRG.RGMGNMVN, ANP01D_ANRG.RGMGNMAN FROM ANP01D_ANRG WHERE (((ANP01D_ANRG.RGRCSTCD)=1));"

It gives an error "SQL statement expected"

Bernd

 

[Cagliostro]

What I wrote last is to write in a different SQL, not in a module John Fill

ivfmd@mail.md

 

[bwmarents]

I think it will not work out for me.....

 

[tlbroadbent]

RunSQL can only be used for action queries because it does not provide for the return of a recordset, only a return code.

You will want to create open a recordset (cursor) in your module. You can read about recordsets in Visual Basic Help. Look for articles on Recordset Object and Open Method (ADO Recordset), among others.
Terry

http://members.home.net/tlbroadbent/index.htm