Simple (?) runsql problem

[Ian Turk]

I have spent ages trying to find the problem with this simple coding - and I
just cant see it.

I have Access 2002

The below is coded to a command button, but I keep getting a message "A
RunSQL action requires an argument consisting of an SQL statement"

I have reduced this right down to the simplest SQL statement - but still
cant work out the problem - put the statement into the query design window
and it runs fine.

Any help before all my hair goes would be appreciated!!

Many thanks
Ian

Private Sub Co_Searchtext_Click()
Dim Querysql As String

On Error GoTo Err_Co_Searchtext_Click

Querysql = "SELECT T_PersExp.CompanyID, T_PersExp.ContactLast FROM
T_PersExp;"


DoCmd.RunSQL Querysql

Exit_Co_Searchtext_Click:
Exit Sub


Err_Co_Searchtext_Click:
MsgBox Err.Description
Resume Exit_Co_Searchtext_Click

End Sub

 

[Dirk Goldgar]

I have spent ages trying to find the problem with this simple coding
- and I just cant see it.

I have Access 2002

The below is coded to a command button, but I keep getting a message
"A RunSQL action requires an argument consisting of an SQL statement"

I have reduced this right down to the simplest SQL statement - but
still cant work out the problem - put the statement into the query
design window and it runs fine.

Any help before all my hair goes would be appreciated!!

Many thanks
Ian

Private Sub Co_Searchtext_Click()
Dim Querysql As String

On Error GoTo Err_Co_Searchtext_Click

Querysql = "SELECT T_PersExp.CompanyID, T_PersExp.ContactLast FROM
T_PersExp;"


DoCmd.RunSQL Querysql

Exit_Co_Searchtext_Click:
Exit Sub


Err_Co_Searchtext_Click:
MsgBox Err.Description
Resume Exit_Co_Searchtext_Click

End Sub

Rather a misleading error message, isn't it? What it's really trying to
tell you is that RunSQL can only run *action* queries -- e.g. append,
delete, make-table queries -- not SELECT queries.

 

[Sandra Daigle]

Hi Ian,

Docmd.RunSQL is for an action query or a data definition query - not for a
Select query.

Are you trying to open a query in datasheet view? To do this with inline SQL
you would first have to create a new querydef and append it to the querydefs
collection. Following is some sample code that will work.

Personally, I don't think opening a query in datasheet view is the best way
to present data - too many things can go wrong and you have very little
control over what the user does. A form is normally a better solution. Post
back if you need more help working out a safer solution.

Here's the code -

Public Sub testQuery()
Dim qdf As dao.QueryDef
Dim db As dao.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "Select * from cust"
Set qdf = New QueryDef
qdf.SQL = strSQL
qdf.Name = "tmpQuery"
db.QueryDefs.Append qdf
DoCmd.OpenQuery "tmpquery"
'delete it if you really want it to be temporary
db.QueryDefs.Delete "tmpQuery"
set qdf=nothing
set db=nothing
End Sub

 

[Ian Turk]

Thanks to Dirk and Sandra,

Dirk - my apologies, I think I just sent a reply back to you by mistake,
rather than the group.

Sandra - Originally this was a make table query, which I progressively
simplified to find a syntax error, then of course ran into the problem of
trying RunSQL, on a select query.

Many thanks

Ian

 

[Dirk Goldgar]

Thanks to Dirk and Sandra,

Dirk - my apologies, I think I just sent a reply back to you by
mistake, rather than the group.

Not to worry. It probably bounced anyway, unless you took the trouble
to de-anti-spam the e-mail address.

I trust this means you've worked out the syntax errors in your original
query. Very good.

 

[Ian Turk]

Yes thanks - syntax worked out quickly once the diversion was cleared.

And yes your email bounced.

Thanks again

Ian

 

[DanK]

Try leaving off the quotation marks.

 

[DanK]

Sorry, I missed that last line of RunSQL. Sounds like you
got it figured out tho.

 

[Ian Turk]

Hi DanK,

Yes figured it out in the end.

Thanks

Ian