Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Save SQL to An Existing Query?

Discussion in 'Information Technology' started by SAm, Jul 28, 2006.

  1. SAm

    SAm
    Expand Collapse
    Guest

    Hi

    Can i save an SQL statement to an existing Query. what i would like to do is
    have a crosstab on fields that will be determined at runtime. so i would have
    the pivot on a field that is chosen at runtime. I can run a sub that will
    generate the correct SQL, i am just lost on how to save it to an existing
    query.

    the full blown explanation of what i am try to do, is the following. i have
    a report which is going to use a cross tab. so i need to attach a query to
    the report. 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.

    thanks in advance for your help,

    sam
     
  2. Loading...

    Similar Threads Forum Date
    Convinced By Sikh, Muslim Family Saves Hindu Lives Interfaith Dialogues Jun 24, 2016
    S Asia Sikhs want Nawaz Sharif to help save Pakistan Gurdwaras Breaking News Jan 23, 2014
    Save Your Sanity for Less (Financial Fix Video) Business, Lifestyle & Leisure Jan 15, 2014
    Sikhi is in Ruins. How Can We Save It? Sikh Sikhi Sikhism Jan 4, 2013
    Save the Historical Gurdwaras in Pakistan Sikh Gurdwaras Dec 21, 2012

  3. schasteen

    schasteen
    Expand Collapse
    Guest

    Dim strSQL As String
    Dim dbcurr As Database
    Dim qdfCurr




    strSQL = 'Put your SQL string here
    Set dbcurr = CurrentDb
    Set qdfCurr = dbcurr.QueryDefs("SQLSummary")
    qdfCurr.SQL = strSQL

    "SAm" wrote:

    > Hi
    >
    > Can i save an SQL statement to an existing Query. what i would like to do is
    > have a crosstab on fields that will be determined at runtime. so i would have
    > the pivot on a field that is chosen at runtime. I can run a sub that will
    > generate the correct SQL, i am just lost on how to save it to an existing
    > query.
    >
    > the full blown explanation of what i am try to do, is the following. i have
    > a report which is going to use a cross tab. so i need to attach a query to
    > the report. 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.
    >
    > thanks in advance for your help,
    >
    > sam
    >
     
  4. schasteen

    schasteen
    Expand Collapse
    Guest

    Sorry, did not full clarify
    Dim strSQL As String
    Dim dbcurr As Database
    Dim qdfCurr

    strSQL = 'Put your SQL string here
    Set dbcurr = CurrentDb
    Set qdfCurr = dbcurr.QueryDefs("YourQueryName")
    qdfCurr.SQL = strSQL


    "schasteen" wrote:

    > >

    > "SAm" wrote:
    >
    > > Hi
    > >
    > > Can i save an SQL statement to an existing Query. what i would like to do is
    > > have a crosstab on fields that will be determined at runtime. so i would have
    > > the pivot on a field that is chosen at runtime. I can run a sub that will
    > > generate the correct SQL, i am just lost on how to save it to an existing
    > > query.
    > >
    > > the full blown explanation of what i am try to do, is the following. i have
    > > a report which is going to use a cross tab. so i need to attach a query to
    > > the report. 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.
    > >
    > > thanks in advance for your help,
    > >
    > > sam
    > >
     
  5. SAm

    SAm
    Expand Collapse
    Guest

    how do i do this using ADO

    "schasteen" wrote:

    > Sorry, did not full clarify
    > Dim strSQL As String
    > Dim dbcurr As Database
    > Dim qdfCurr
    >
    > strSQL = 'Put your SQL string here
    > Set dbcurr = CurrentDb
    > Set qdfCurr = dbcurr.QueryDefs("YourQueryName")
    > qdfCurr.SQL = strSQL
    >
    >
    > "schasteen" wrote:
    >
    > > >

    > > "SAm" wrote:
    > >
    > > > Hi
    > > >
    > > > Can i save an SQL statement to an existing Query. what i would like to do is
    > > > have a crosstab on fields that will be determined at runtime. so i would have
    > > > the pivot on a field that is chosen at runtime. I can run a sub that will
    > > > generate the correct SQL, i am just lost on how to save it to an existing
    > > > query.
    > > >
    > > > the full blown explanation of what i am try to do, is the following. i have
    > > > a report which is going to use a cross tab. so i need to attach a query to
    > > > the report. 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.
    > > >
    > > > thanks in advance for your help,
    > > >
    > > > sam
    > > >
     
  6. schasteen

    schasteen
    Expand Collapse
    Guest

    This is from the help menu on how to create a query using ADO. I have not
    tried it, but it should get you on the right path.

    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command

    Set cat.ActiveConnection = CurrentProject.Connection
    cmd.CommandText “Select * From Employeesâ€
    Cat.Views.Append "NewQuery", cmd

    "SAm" wrote:

    > how do i do this using ADO
    >
    > "schasteen" wrote:
    >
    > > Sorry, did not full clarify
    > > Dim strSQL As String
    > > Dim dbcurr As Database
    > > Dim qdfCurr
    > >
    > > strSQL = 'Put your SQL string here
    > > Set dbcurr = CurrentDb
    > > Set qdfCurr = dbcurr.QueryDefs("YourQueryName")
    > > qdfCurr.SQL = strSQL
    > >
    > >
    > > "schasteen" wrote:
    > >
    > > > >
    > > > "SAm" wrote:
    > > >
    > > > > Hi
    > > > >
    > > > > Can i save an SQL statement to an existing Query. what i would like to do is
    > > > > have a crosstab on fields that will be determined at runtime. so i would have
    > > > > the pivot on a field that is chosen at runtime. I can run a sub that will
    > > > > generate the correct SQL, i am just lost on how to save it to an existing
    > > > > query.
    > > > >
    > > > > the full blown explanation of what i am try to do, is the following. i have
    > > > > a report which is going to use a cross tab. so i need to attach a query to
    > > > > the report. 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.
    > > > >
    > > > > thanks in advance for your help,
    > > > >
    > > > > sam
    > > > >
     
  7. SAm

    SAm
    Expand Collapse
    Guest

    Thanx a lot. i was reading about this. and i wasn't sure, i have never used
    ADOX. i tried it, but for ADOX a new reference is required. i am always
    afraid of those references because of version upgrades. my job is hard enough
    designing complicated forms and queries, i don't have enough time read up on
    writing code which validates versions.

    again, thanks a lot, i will try to implement the code.

    sam

    "schasteen" wrote:

    > This is from the help menu on how to create a query using ADO. I have not
    > tried it, but it should get you on the right path.
    >
    > Dim cat As New ADOX.Catalog
    > Dim cmd As New ADODB.Command
    >
    > Set cat.ActiveConnection = CurrentProject.Connection
    > cmd.CommandText “Select * From Employeesâ€
    > Cat.Views.Append "NewQuery", cmd
    >
    > "SAm" wrote:
    >
    > > how do i do this using ADO
    > >
    > > "schasteen" wrote:
    > >
    > > > Sorry, did not full clarify
    > > > Dim strSQL As String
    > > > Dim dbcurr As Database
    > > > Dim qdfCurr
    > > >
    > > > strSQL = 'Put your SQL string here
    > > > Set dbcurr = CurrentDb
    > > > Set qdfCurr = dbcurr.QueryDefs("YourQueryName")
    > > > qdfCurr.SQL = strSQL
    > > >
    > > >
    > > > "schasteen" wrote:
    > > >
    > > > > >
    > > > > "SAm" wrote:
    > > > >
    > > > > > Hi
    > > > > >
    > > > > > Can i save an SQL statement to an existing Query. what i would like to do is
    > > > > > have a crosstab on fields that will be determined at runtime. so i would have
    > > > > > the pivot on a field that is chosen at runtime. I can run a sub that will
    > > > > > generate the correct SQL, i am just lost on how to save it to an existing
    > > > > > query.
    > > > > >
    > > > > > the full blown explanation of what i am try to do, is the following. i have
    > > > > > a report which is going to use a cross tab. so i need to attach a query to
    > > > > > the report. 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.
    > > > > >
    > > > > > thanks in advance for your help,
    > > > > >
    > > > > > sam
    > > > > >
     

Share This Page