Re: a guide/reference to queries via VBA

From: Alp Bekisoglu (someone_at_somewhere.com)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 10:47:37 +0800

Thanks Doug,

I got my SQL's via the code you've suggested. Learned one more!
What I'm planning to do is to re-write some of my queries in VBA as modules
and would like to find out enough information on the syntax (I've seen some
alien looking ones, at least to me) to save both the group's time & my
headache.
VBA help hasn't been too much of use due to lack of a bit more complex
examples, i.e. selecting many fields, including conditions, etc... The one I
found doesn't tell me much....
Public Sub DoSQL()

    Dim SQL As String

    SQL = "UPDATE Employees " & _
          "SET Employees.Title = 'Regional Sales Manager' " & _
          "WHERE Employees.Title = 'Sales Manager'"

    DoCmd.RunSQL SQL

End Sub

I need to "correctly" code things like this (that is if such is advisable)
which is one of the very simple ones:
SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year, [2002a].from,
[2002a].to, [2002a].first, [2002a].next, [2002a].tax, [2002a].rate
FROM 2002a, q_txable_s
WHERE ((([2002a].from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=[2002a]![to]));

Fon instance the 2002a (a table) will be altered to a variable that is to be
determined dynamically on call. This is one reason why I want to convert my
queries into code.

Thanks again.

Alp

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:u6K%23isb8DHA.1936@TK2MSFTNGP12.phx.gbl...
> Assuming you've set a reference to DAO, try something like:
>
> Dim dbCurr As Database
> Dim qdfCurr As QueryDef
>
> Set dbCurr = CurrentDb()
> For Each qdfCurr In dbCurr.QueryDefs
> Debug.Print qdfCurr.Name & ": " & qdfCurr.SQL
> Next qdfCurr
>
> Set dbCurr = Nothing
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (No private e-mails, please)
>
>
>
> "Alp Bekisoglu" <someone@somewhere.com> wrote in message
> news:eMtvRgW8DHA.3012@TK2MSFTNGP09.phx.gbl...
> > Thanks for the advice but somehow my copy of Access seems to hate DAO an
d
> > other things since I am unable to see any details on such items! Wizard
> > lists the topics but clicking on any leads no where!
> >
> > Any other alternatives in your mind? Usually Microsoft's any help/FAQ
is
> > next to useless or nonsense anyway, but that's beyond the point.
> >
> > Sincerely,
> >
> > Alp
> >
> > P.S.: No offences to any of the valuable MS people, please. I'm just fed
> up
> > with answers like "Have you turned your PC on?" kind of answers while
> trying
> > to get something out of things like the "TroubleShooter"!
> >
> > "Van T. Dinh" <VanT.Dinh@discussions.microsoft.com> wrote in message
> > news:u2O4nIW8DHA.360@TK2MSFTNGP12.phx.gbl...
> > > I normally use the QueryDef Object of the DAO Library to access the
> Query
> > by
> > > code.
> > >
> > > Check Access VB Help on the QueryDefs Collection and QueryDef Object.
> > >
> > > --
> > > HTH
> > > Van T. Dinh
> > > MVP (Access)
> > >
> > >
> > >
> > > "Alp Bekisoglu" <someone@somewhere.com> wrote in message
> > > news:esTw7cU8DHA.2168@TK2MSFTNGP12.phx.gbl...
> > > > Is there a place I can refer to/check to gain info on query via
code?
> > > (i.e.
> > > > define/create, run, alter, ect...)
> > > >
> > > > Thanks in advance,
> > > >
> > > > Alp
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Inserting data from 2 tables into 1
    ... been busy with Access and VBA for about 6 ... I would make all the queries and then use a set of macros to ... > Do you perhaps mean look for duplicates in tbResultsTemp? ... > The first is easier and lets you use all the nice interfaces like query ...
    (microsoft.public.access.modulesdaovba)
  • Re: Data Changing Issue
    ... *if* you are in VBA. ... think you were using either VBA code, either a query.) ... is very unlikely to print some value of myid present in table1... ... I am running some queries using a form I created and for some strange ...
    (microsoft.public.access.queries)
  • Re: Tough One: displayalerts = false while OUTSIDE VBA
    ... AFAIK you can't set this property to False outside VBA. ... Can anyone think of a way to disable alerts on background queries ... > when the Excel application is in control. ... > I have a vba routine that generates potentialy thousands of web ...
    (microsoft.public.excel.programming)
  • RE: VBA Syntex Question
    ... With out being able to see the data and the structure of the database or the ... I sounds to me as if there are problems with the criteria in you queries ... peticular attention to how you refer to these values. ... I know this can be done via VBA. ...
    (microsoft.public.access.gettingstarted)
  • Re: DAO qdfs does not delete my queries
    ... Dim qdfCurr As DAO.QueryDef ... The reason for this is when you delete a particular QueryDef from the ... I have 3 queries I want to delete from my list of queries. ...
    (microsoft.public.access.modulesdaovba)