Welcome to SPN

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

Sign Up Now!

build a query via vba

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

Tags:
  1. yaniv d

    yaniv d
    Expand Collapse
    Guest

    i wonder if there is an option to enter SQL sentence in order to change
    its structure and data via vba code
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News 15 Years After 9/11 Founding, The Sikh Coalition Builds A 'Path Forward' Breaking News Sep 10, 2016
    SevaTruck Foundation - Combat Hunger, Serving Humanity, Build Unity Community Out-Reach Jun 9, 2016
    Sikh Some Bridge Building Sikh Sikhi Sikhism May 10, 2016
    Interfaith Building A Sikh Paradigm For Interfaith Work: Part 2 Interfaith Dialogues Apr 29, 2016
    Interfaith Shades Of The Old Punjab - Sikhs, Hindus helping rebuild Mosques in Punjab Interfaith Dialogues Jul 17, 2015

  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    Yaniv,

    Most likely yes, but you have to be more specific in order to get a
    specific reply! What exactly do you want to do?

    Nikos
     
  4. yaniv d

    yaniv d
    Expand Collapse
    Guest

    ok,thanks for your replay.
    my issue is as follow.
    i want to build maketable query that will be dinamic,that means that
    through vba,i will be able to change everytime(depending on my groups)
    the structure of the make table,so it will be dinamicly make table from
    diffrent groups
    for example:
    one form will automatic create a table of animals
    second form will automatic create a table of flowers
    and ctr..

    but of course all of them should be working from one query make-table

    Nikos Yannacopoulos wrote:
    > Yaniv,
    >
    > Most likely yes, but you have to be more specific in order to get a
    > specific reply! What exactly do you want to do?
    >
    > Nikos
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Dim dbCurr As DAO.Database
    Dim qdfCurr As DAO.QueryDef
    Dim strSQL As String

    strSQL = "SELECT ...."
    Set dbCurr = CurrentDb()
    Set qdfCurr = dbCurr.QueryDefs("MyQuery")
    qdfCurr.SQL = strSQL

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "yaniv d" <yaniv.dg@gmail.com> wrote in message
    news:1149663498.822467.54570@g10g2000cwb.googlegroups.com...
    > i wonder if there is an option to enter SQL sentence in order to change
    > its structure and data via vba code
    >
     
  6. yaniv d

    yaniv d
    Expand Collapse
    Guest

    hi thanks for your help,
    i'm woring on ADO,can you give me a code for it
    Douglas J Steele wrote:
    > Dim dbCurr As DAO.Database
    > Dim qdfCurr As DAO.QueryDef
    > Dim strSQL As String
    >
    > strSQL = "SELECT ...."
    > Set dbCurr = CurrentDb()
    > Set qdfCurr = dbCurr.QueryDefs("MyQuery")
    > qdfCurr.SQL = strSQL
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "yaniv d" <yaniv.dg@gmail.com> wrote in message
    > news:1149663498.822467.54570@g10g2000cwb.googlegroups.com...
    > > i wonder if there is an option to enter SQL sentence in order to change
    > > its structure and data via vba code
    > >
     
  7. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    Yaniv,

    You don't even need a (saved) query at all in this case... you can just
    construct your SQL statement in code and execute it without saving it at
    all.
    As a simple example, suppose there is a table called tblSpecies, and you
    want to filter on field SpeciesType (animal, flower etc) and create a
    new table called tblSpeciesOfType; filter selection is made by means of
    a combo box (cboSpeciesType) on a form, and the make-table query is run
    by clicking a command button on the same form. The code behind the
    command button would look something like:

    Dim strSQL As String
    If IsNull(Me.cboSpeciesType) Then
    MsgBox "No species type selected.", vbExclamation, _
    "Error Creating Table"
    Exit Sub
    End If
    strSQL = "SELECT * INTO tblSpeciesOfType FROM tblSpecies " & _
    "WHERE SpeciesType = '" & Me.cboSpeciesType & "'"
    CurrentDb.Execute strSQL, dbFailOnError

    This will overwrite the table if it already exists.

    That said, are you positive you need to create a new table? Why don't
    you just filter on the existing one(s) by means of a Select query?
    Duplication of data is not advisable for a number of reasons, having to
    do with data integrity and maintenance, storage space etc. Elaborate if
    you need further help.

    HTH,
    Nikos
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    As NIkos points out, you may not require anything.

    While it is possible to use ADOX to accomplish the same, since you're
    dealing with QueryDef objects in a Jet database, you're better off using
    DAO. There's no problem using both ADO and DAO in the same application: I do
    it all the time.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "yaniv d" <yaniv.dg@gmail.com> wrote in message
    news:1149682431.357014.200250@f6g2000cwb.googlegroups.com...
    > hi thanks for your help,
    > i'm woring on ADO,can you give me a code for it
    > Douglas J Steele wrote:
    > > Dim dbCurr As DAO.Database
    > > Dim qdfCurr As DAO.QueryDef
    > > Dim strSQL As String
    > >
    > > strSQL = "SELECT ...."
    > > Set dbCurr = CurrentDb()
    > > Set qdfCurr = dbCurr.QueryDefs("MyQuery")
    > > qdfCurr.SQL = strSQL
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "yaniv d" <yaniv.dg@gmail.com> wrote in message
    > > news:1149663498.822467.54570@g10g2000cwb.googlegroups.com...
    > > > i wonder if there is an option to enter SQL sentence in order to

    change
    > > > its structure and data via vba code
    > > >

    >
     
  9. yaniv d

    yaniv d
    Expand Collapse
    Guest

    hi douglas,
    thanks for your review.
    my goal is to make my tool as dinamic as possible,one form and one
    query modular for all the group(47 of them) so it will be easy to
    maintain for later on and small in the size for the local hard drive.
    i believe the direction that you and nikos gave me will give me some
    path.
    the issue is that i dont want to add any referenced object to my tool
    that when i dont know if i dont know how it will react in the client
    desktop.(means i prefer to use the current object rather then to worry
    if the activeX is istalled or not,please correct me if im wrong).

    thanks again for both of you

    Douglas J Steele wrote:
    > As NIkos points out, you may not require anything.
    >
    > While it is possible to use ADOX to accomplish the same, since you're
    > dealing with QueryDef objects in a Jet database, you're better off using
    > DAO. There's no problem using both ADO and DAO in the same application: I do
    > it all the time.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "yaniv d" <yaniv.dg@gmail.com> wrote in message
    > news:1149682431.357014.200250@f6g2000cwb.googlegroups.com...
    > > hi thanks for your help,
    > > i'm woring on ADO,can you give me a code for it
    > > Douglas J Steele wrote:
    > > > Dim dbCurr As DAO.Database
    > > > Dim qdfCurr As DAO.QueryDef
    > > > Dim strSQL As String
    > > >
    > > > strSQL = "SELECT ...."
    > > > Set dbCurr = CurrentDb()
    > > > Set qdfCurr = dbCurr.QueryDefs("MyQuery")
    > > > qdfCurr.SQL = strSQL
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "yaniv d" <yaniv.dg@gmail.com> wrote in message
    > > > news:1149663498.822467.54570@g10g2000cwb.googlegroups.com...
    > > > > i wonder if there is an option to enter SQL sentence in order to

    > change
    > > > > its structure and data via vba code
    > > > >

    > >
     

Share This Page