Save SQL to An Existing Query using ADO?

[SamDeMan]

Hi

my first post to google groups!

Can i save an SQL statement to an existing Query. what i would like to
do is have a crosstab on a field that will be determined at runtime.
this way my user can select a field that he would like pivot. I am
using VBA that will generate the correct SQL, i am just lost on how to
save it to an existing query. I was told that a QueryDef would work.
but i am only comfortable with ADO.

thanks,


sam

 

[Ron Weiner]

Sam

Don't know what this question has to do with Access Reports but:

I am also a ADO kind of Access guy, but some things are SOooo much easier to
do with DAO (like what you want to do) you need to get familiar with at
least some aspects of it. Make sure you have a reference set to DAO and try
code like this.

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("qryNameOfYourQuery")
qdf.SQL = "The Sql for your Xtab"
qdf.Close
Set qdf = Nothing

 

[SamDeMan]

thanx ron,

first, i didn't realize that this was an access reports forum until
after i posted. thanx for pointing it out, and i appologize to rest of
you.

second, i would like to point out, that this part of a report. i didn't
post here my whole explanation why i need this code. i did so in a
different forum, but here i was trying to keep it simple. i figure that
the reason i didn't get responses was that it was too complicated.

here is a copy of my explanation:

i have a report which is uses a cross tab via VBA. so i need to set the
recordsource of the report with a query. if i assign
report.recordsource = SQL i get a message that a crosstab can't be
attached to the report. but if i use a saved crosstab and i write
report.recordsource = me.myCrosstabQry then it works. don't know why,
but it does. now the problem is the saved crosstab needs to be updated
at runtime, based on the users response. i would like to use different
Fields as values. i already designed a similar report which takes uses
two colums as values. what i did was i used CTquery1 FieldTogether:
Table1!Field1 & " " Table1!Field2 and then i had some code that
seperated it out for me.

Regarding your answer, i would rather use ADO. i think the correct way
to this (probably the only way) is to use ADOX. i am never used ADOX

sam

 

[Ron Weiner]

If that's what you want to do, knock yourself out.

I think you can find what you are looking for here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp
You will have to set a reference to the ADOX library.

 

[Guest]

Hi Sam,

You are right you can use ADOX for this purpose:

Sub create_view()
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "SELECT * FROM ..."

cat.Views.Append new_query_name, cmd

Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing

End Sub