Re: Translation of SQL for VBA

Not knowing what the criteria are in the SQL string, my first thought is
that you would modify the criteria similarly to how your posted code
modifies the tablename in the string.
--

Ken Snell
<MS ACCESS MVP>



"Kou Vang" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1E90443-CDA1-4164-B3EE-63B72942308B@xxxxxxxxxxxxxxxx
> Thanks for the help Ken! I think I'm almost there, this is getting closer
> to
> what I wanted. If I wanted to get into modifying the criteria of the
> query,
> would I then have to write out my SQL line and modify it in my string?
> I'm
> using a form with text and combo boxes as the values in the criteria of
> the
> Append Query. The code you have given me only changes the target table
> correct? How do I access the criteria of the query now?
>
> "Ken Snell (MVP)" wrote:
>
>> Ahhhhhh... ok, what I gave you isn't going to work directly. Instead,
>> you'll
>> need to create a new query, evaluate the parameters in the "new" query,
>> and
>> then execute it.
>>
>> Let's change your code to this (I've also corrected the code, as
>> TableDefs
>> do not contain queries):
>>
>>
>> Dim strSQL as String
>> Dim dbf as Database
>> Dim par as DAO.Parameter
>> Dim qdf As DAO.QueryDef
>>
>> 'Set up object variables
>> Set dbf = CurrentDb
>>
>> 'Get the SQL string for the query
>> strSQL = dbf.QueryDefs("MyQueryName").SQL
>>
>> 'Replace the old name with the new name in the SQL
>> strSQL = Replace(strSQL, "OldTableName", "NewTableName")
>>
>> 'Create temporary query
>> Set qdf = dbf.CreateQueryDef("", strSQL)
>>
>> 'Evaluate the parameters in the new query
>> For Each par in qdf.Parameters
>> par.Value = Eval(par.Name)
>> Next par
>>
>> 'Runs the append query with the selected table name
>> qdf.Execute
>>
>> 'Clear object variables
>> qdf.Close
>> Set qdf = Nothing
>> dbf.Close
>> Set dbf = Nothing
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Kou Vang" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5C13B9B1-F1FA-4928-B183-D6F1A7B0FD3D@xxxxxxxxxxxxxxxx
>> > I'm trying to do it in VBA, so I can change the target table in my
>> > append
>> > query, and not have to make 4 or 5 different queries. Instead, I just
>> > want
>> > one query, while modifying the criteria and the target table of the
>> > Append
>> > Query. Someone gave me a start with:
>> >
>> > Dim strSQL as String
>> > Dim dbf as Database
>> > Dim tdfs as TableDefs
>> > Dim tdf as TableDef
>> >
>> > 'Set up object variables
>> > Set dbf = CurrentDb
>> > Set tdfs = dbf.TableDefs
>> > Set tdf = tdfs("MyQueryName")
>> >
>> > 'Get the SQL string for the query
>> > strSQL = tdf.SQL
>> > 'Replace the old name with the new name in the SQL
>> > strSQL = Replace(strSQL, "OldTableName", "NewTableName")
>> >
>> > 'Runs the append query with the selected table name
>> > dbf.Execute(strSQL)
>> >
>> > 'Note, we don't change the name in the original saved query so we don't
>> > know
>> > what it is next time we need it. The Execute method above is the same
>> > as
>> > running the query any other way and is about 5 times faster than then
>> > RunSql
>> > method.
>> >
>> > 'Destroy object variables
>> > Set tdf = Nothing
>> > Set tdfs = Nothing
>> > Set dbf = Nothing
>> >
>> > So I was just trying to put my SQL string together and was having
>> > problems.
>> >
>> > "Ken Snell (MVP)" wrote:
>> >
>> >> What are you doing with the SQL string when it's built? That can
>> >> affect
>> >> how
>> >> you would handle this.
>> >>
>> >> In general, you would concatenate the actual value from the parameter
>> >> into
>> >> the string:
>> >>
>> >> MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
>> >> " AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]
>> >>
>> >> (above assumes that both fields are numeric).
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >> "Kou Vang" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:AAA8A202-0E7F-4CE1-864D-ACFAB3317B8F@xxxxxxxxxxxxxxxx
>> >> >I have not been able to find a translation of 2 conditions of a query
>> >> >to
>> >> >be
>> >> > properly coded in VBA. I know this is pretty basic, but I'm
>> >> > struggling.
>> >> > How
>> >> > do I say this in my strSQL string in VBA? Thanks.
>> >> >
>> >> > WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
>> >> > ((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • RE: Counting of Records in a Before opening a Data Form
    ... First make sure that the query returns records. ... select the criteria. ... Dim rstCriteria As DAO.Recordset ... Dim stDocName As String ...
    (microsoft.public.access.formscoding)
  • RE: Counting of Records in a Before opening a Data Form
    ... the form "Criteria Building Form" MUST be open. ... Would you post the SQL of the query "Criteria"? ... Dim rstCriteria As DAO.Recordset ... Dim stDocName As String ...
    (microsoft.public.access.formscoding)
  • RE: Complex query criteria - desperate appeal
    ... The following will search for query by form: ... > appropriate SQL string if it is not. ... > Dim qdf as DAO.QueryDef ... > criteria in text fields, you would have to modify these somewhat to give the ...
    (microsoft.public.access.queries)
  • Re: Translation of SQL for VBA
    ... If I wanted to get into modifying the criteria of the query, ... > Dim strSQL as String ... >> 'Get the SQL string for the query ...
    (microsoft.public.access.modulesdaovba)