runSQL ActionQuery

  • Thread starter Thread starter Ken Ivins
  • Start date Start date
K

Ken Ivins

I am trying to run an action query called "qryDeleteBlankPayableTo" using
VBA. I see I need to do it with the DoCmd.RunSQL.

My problem is that I know very little (almost none) SQL.

So I could use help on the proper format and text to do this.

Thanks,

Ken
 
Hint - You can build your query using the query builder, then change it to
SQL view and copy the code and paste it into your VBA editor. You will have
to add quote marks and such to get it correct, but you would be suprised at
how many of us do this on a regular basis. So first type in:
strSQL = "
Then paste in your SQL code you copied. Add the " at the end.
You will also have to take out any references to controls on your form out
of the quotes. For example, it may come over as
"WHERE (((tblYTDDetail.acctgunit) Like
[forms]![frmYTDLaborSelection]![acctgunit] And (tblYTDDetail.acctgunit) Like
[forms]![frmYTDLaborSelection]![acctgunit]) AND ((tblYTDDetail.activity) Like
[forms]![frmYTDLaborSelection]![activity]))"

You will need to change like this:
"WHERE (((tblYTDDetail.acctgunit) Like " &
[forms]![frmYTDLaborSelection]![acctgunit] & " And (tblYTDDetail.acctgunit)
Like " & [forms]![frmYTDLaborSelection]![acctgunit]) & " AND
((tblYTDDetail.activity) Like " & [forms]![frmYTDLaborSelection]![activity]))
'"
That is so the values of the controls are included in strSQL instead of the
name of the form.

One last thing. Don't use DoCmd.RunSQL. It is very slow. The better way is:
CurrentDB.Execute(strSQL)
Good Luck!
 
Try this http://www.mvps.org/access/queries/qry0003.htm

HTH;


Amy

Klatuu said:
Hint - You can build your query using the query builder, then change it to
SQL view and copy the code and paste it into your VBA editor. You will
have
to add quote marks and such to get it correct, but you would be suprised
at
how many of us do this on a regular basis. So first type in:
strSQL = "
Then paste in your SQL code you copied. Add the " at the end.
You will also have to take out any references to controls on your form out
of the quotes. For example, it may come over as
"WHERE (((tblYTDDetail.acctgunit) Like
[forms]![frmYTDLaborSelection]![acctgunit] And (tblYTDDetail.acctgunit)
Like
[forms]![frmYTDLaborSelection]![acctgunit]) AND ((tblYTDDetail.activity)
Like
[forms]![frmYTDLaborSelection]![activity]))"

You will need to change like this:
"WHERE (((tblYTDDetail.acctgunit) Like " &
[forms]![frmYTDLaborSelection]![acctgunit] & " And
(tblYTDDetail.acctgunit)
Like " & [forms]![frmYTDLaborSelection]![acctgunit]) & " AND
((tblYTDDetail.activity) Like " &
[forms]![frmYTDLaborSelection]![activity]))
'"
That is so the values of the controls are included in strSQL instead of
the
name of the form.

One last thing. Don't use DoCmd.RunSQL. It is very slow. The better way
is:
CurrentDB.Execute(strSQL)
Good Luck!


Ken Ivins said:
I am trying to run an action query called "qryDeleteBlankPayableTo" using
VBA. I see I need to do it with the DoCmd.RunSQL.

My problem is that I know very little (almost none) SQL.

So I could use help on the proper format and text to do this.

Thanks,

Ken
Click to expand...
Click to expand...
 
Thanks, this is a big help. I am close but think I am missing something
because I keep getting errors.

Here is what I have:

Dim strSQL As String
Dim Db As Database


DoCmd.SetWarnings False

strSQL = "Delete [tblPayableTo-single].ptID,
[tblPayableTo-single].ptPayableTo" & "FROM [tblPayableTo-single]" & "WHERE
((([tblPayableTo-single].ptPayableTo) Is Null))"

Set Db = CurrentDb()
Db.Execute (strSQL)

DoCmd.SetWarnings True

Ken


Klatuu said:
Hint - You can build your query using the query builder, then change it to
SQL view and copy the code and paste it into your VBA editor. You will
have
to add quote marks and such to get it correct, but you would be suprised
at
how many of us do this on a regular basis. So first type in:
strSQL = "
Then paste in your SQL code you copied. Add the " at the end.
You will also have to take out any references to controls on your form out
of the quotes. For example, it may come over as
"WHERE (((tblYTDDetail.acctgunit) Like
[forms]![frmYTDLaborSelection]![acctgunit] And (tblYTDDetail.acctgunit)
Like
[forms]![frmYTDLaborSelection]![acctgunit]) AND ((tblYTDDetail.activity)
Like
[forms]![frmYTDLaborSelection]![activity]))"

You will need to change like this:
"WHERE (((tblYTDDetail.acctgunit) Like " &
[forms]![frmYTDLaborSelection]![acctgunit] & " And
(tblYTDDetail.acctgunit)
Like " & [forms]![frmYTDLaborSelection]![acctgunit]) & " AND
((tblYTDDetail.activity) Like " &
[forms]![frmYTDLaborSelection]![activity]))
'"
That is so the values of the controls are included in strSQL instead of
the
name of the form.

One last thing. Don't use DoCmd.RunSQL. It is very slow. The better way
is:
CurrentDB.Execute(strSQL)
Good Luck!


Ken Ivins said:
I am trying to run an action query called "qryDeleteBlankPayableTo" using
VBA. I see I need to do it with the DoCmd.RunSQL.

My problem is that I know very little (almost none) SQL.

So I could use help on the proper format and text to do this.

Thanks,

Ken
Click to expand...
Click to expand...
 
What errors are you getting? I don't think you can delete partial records,
so you should probably just say
DELETE FROM [tblPayableTo-single] WHERE...

HTH;

Amy

Ken Ivins said:
Thanks, this is a big help. I am close but think I am missing something
because I keep getting errors.

Here is what I have:

Dim strSQL As String
Dim Db As Database


DoCmd.SetWarnings False

strSQL = "Delete [tblPayableTo-single].ptID,
[tblPayableTo-single].ptPayableTo" & "FROM [tblPayableTo-single]" & "WHERE
((([tblPayableTo-single].ptPayableTo) Is Null))"

Set Db = CurrentDb()
Db.Execute (strSQL)

DoCmd.SetWarnings True

Ken


Klatuu said:
Hint - You can build your query using the query builder, then change it
to
SQL view and copy the code and paste it into your VBA editor. You will
have
to add quote marks and such to get it correct, but you would be suprised
at
how many of us do this on a regular basis. So first type in:
strSQL = "
Then paste in your SQL code you copied. Add the " at the end.
You will also have to take out any references to controls on your form
out
of the quotes. For example, it may come over as
"WHERE (((tblYTDDetail.acctgunit) Like
[forms]![frmYTDLaborSelection]![acctgunit] And (tblYTDDetail.acctgunit)
Like
[forms]![frmYTDLaborSelection]![acctgunit]) AND ((tblYTDDetail.activity)
Like
[forms]![frmYTDLaborSelection]![activity]))"

You will need to change like this:
"WHERE (((tblYTDDetail.acctgunit) Like " &
[forms]![frmYTDLaborSelection]![acctgunit] & " And
(tblYTDDetail.acctgunit)
Like " & [forms]![frmYTDLaborSelection]![acctgunit]) & " AND
((tblYTDDetail.activity) Like " &
[forms]![frmYTDLaborSelection]![activity]))
'"
That is so the values of the controls are included in strSQL instead of
the
name of the form.

One last thing. Don't use DoCmd.RunSQL. It is very slow. The better
way is:
CurrentDB.Execute(strSQL)
Good Luck!


Ken Ivins said:
I am trying to run an action query called "qryDeleteBlankPayableTo"
using
VBA. I see I need to do it with the DoCmd.RunSQL.

My problem is that I know very little (almost none) SQL.

So I could use help on the proper format and text to do this.

Thanks,

Ken
Click to expand...
Click to expand...
Click to expand...
 
Ken Ivins said:
I am trying to run an action query called "qryDeleteBlankPayableTo"
using VBA. I see I need to do it with the DoCmd.RunSQL.
Click to expand...

If you've already got the query stored, you don't need RunSQL to run it.
Judging from the SQL you posted later in this thread, you can just
write:

CurrentDb.Execute "qryDeleteBlankPayableTo", dbFailOnError

If your query contained references to controls on Forms, it wouldn't be
quite that simple, but it doesn't look as though it does.
 
Amy,

Thanks for your help.

The error I am getting is a Run Time Error 3075 Snytax error (missing
Operator) in query Expression '[tblPayableTo-single].ptID,
[tblPayableTo-single].ptPayableToFROM [tblPayableTo-single]WHERE
((([tblPayableTo-single].ptPayableTo) Is Null))'

Yes I am trying to delete records. I want to run a delete query that looks
at the table tblPayableTo-single and finds all the records in which the
field ptPayableTo is null and the delete them.

The delete query works great if I could just get it to run using code.Any
help you can give me would be great.

Thanks,
Ken

Amy Blankenship said:
What errors are you getting? I don't think you can delete partial
records, so you should probably just say
DELETE FROM [tblPayableTo-single] WHERE...

HTH;

Amy

Ken Ivins said:
Thanks, this is a big help. I am close but think I am missing something
because I keep getting errors.

Here is what I have:

Dim strSQL As String
Dim Db As Database


DoCmd.SetWarnings False

strSQL = "Delete [tblPayableTo-single].ptID,
[tblPayableTo-single].ptPayableTo" & "FROM [tblPayableTo-single]" &
"WHERE ((([tblPayableTo-single].ptPayableTo) Is Null))"

Set Db = CurrentDb()
Db.Execute (strSQL)

DoCmd.SetWarnings True

Ken


Klatuu said:
Hint - You can build your query using the query builder, then change it
to
SQL view and copy the code and paste it into your VBA editor. You will
have
to add quote marks and such to get it correct, but you would be suprised
at
how many of us do this on a regular basis. So first type in:
strSQL = "
Then paste in your SQL code you copied. Add the " at the end.
You will also have to take out any references to controls on your form
out
of the quotes. For example, it may come over as
"WHERE (((tblYTDDetail.acctgunit) Like
[forms]![frmYTDLaborSelection]![acctgunit] And (tblYTDDetail.acctgunit)
Like
[forms]![frmYTDLaborSelection]![acctgunit]) AND ((tblYTDDetail.activity)
Like
[forms]![frmYTDLaborSelection]![activity]))"

You will need to change like this:
"WHERE (((tblYTDDetail.acctgunit) Like " &
[forms]![frmYTDLaborSelection]![acctgunit] & " And
(tblYTDDetail.acctgunit)
Like " & [forms]![frmYTDLaborSelection]![acctgunit]) & " AND
((tblYTDDetail.activity) Like " &
[forms]![frmYTDLaborSelection]![activity]))
'"
That is so the values of the controls are included in strSQL instead of
the
name of the form.

One last thing. Don't use DoCmd.RunSQL. It is very slow. The better
way is:
CurrentDB.Execute(strSQL)
Good Luck!


:

I am trying to run an action query called "qryDeleteBlankPayableTo"
using
VBA. I see I need to do it with the DoCmd.RunSQL.

My problem is that I know very little (almost none) SQL.

So I could use help on the proper format and text to do this.

Thanks,

Ken
Click to expand...
Click to expand...
Click to expand...
 
Ken Ivins said:
Amy,

Thanks for your help.

The error I am getting is a Run Time Error 3075 Snytax error
(missing Operator) in query Expression '[tblPayableTo-single].ptID,
[tblPayableTo-single].ptPayableToFROM [tblPayableTo-single]WHERE
((([tblPayableTo-single].ptPayableTo) Is Null))'

Yes I am trying to delete records. I want to run a delete query that
looks at the table tblPayableTo-single and finds all the records in
which the field ptPayableTo is null and the delete them.

The delete query works great if I could just get it to run using
code.Any help you can give me would be great.
Click to expand...

PMFJI. If you look at the SQL text in the error message, you'll see
you're missing some required spaces between field names and keywords.
Change this:
strSQL = "Delete [tblPayableTo-single].ptID,
[tblPayableTo-single].ptPayableTo" & "FROM [tblPayableTo-single]" &
"WHERE ((([tblPayableTo-single].ptPayableTo) Is Null))"
Click to expand...
Click to expand...
Click to expand...

to this:

strSQL = _
"Delete [tblPayableTo-single].ptID, " & _
"[tblPayableTo-single].ptPayableTo " & _
"FROM [tblPayableTo-single] " & _
"WHERE ((([tblPayableTo-single].ptPayableTo) Is Null))"

But Amy's right: you really have no need to list the fields. This is
good enough:

strSQL = _
"DELETE FROM [tblPayableTo-single] " & _
"WHERE ptPayableTo Is Null"
 
Dirk,

Thanks That did it.

Ken


Dirk Goldgar said:
If you've already got the query stored, you don't need RunSQL to run it.
Judging from the SQL you posted later in this thread, you can just
write:

CurrentDb.Execute "qryDeleteBlankPayableTo", dbFailOnError

If your query contained references to controls on Forms, it wouldn't be
quite that simple, but it doesn't look as though it does.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top