Re: Translation of SQL for VBA

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

  • Re: Default Text for an Empty returned recordset
    ... Dim rst As Recordset ... Set dbf = Currentdb ... Set rst = qdf.OpenRecordset ... 'Do whatever you want with the query ...
    (microsoft.public.access.forms)
  • Re: Translation of SQL for VBA
    ... Not knowing what the criteria are in the SQL string, ... > Append Query. ... >> Dim strSQL as String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Default Text for an Empty returned recordset
    ... Dim rst As Recordset ... Set dbf = Currentdb ... Set rst = qdf.OpenRecordset ... 'Do whatever you want with the query ...
    (microsoft.public.access.forms)
  • RE: Breaking down imported information
    ... Single-record append query: ... ' Check if at EOF of Recordset (rsDataViaCode) ... ' if at EOF Exit Do. ... Dim rsDataViaCode As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: LDAP query information
    ... Copyright 1985-2001 Microsoft Corp. ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)