Running SELECT query in visual basic

[mauni]

Hi

I found out i can't run SELECT query with DoCmd.RunSQL

The problem is that i can't seem to find any help for this problem. I searched the net and examples i found are confusing. I don't understand the resultset concept at all, is that supposed to be used?

Can somebody post a simple way to show how to run a SELECT query in visual basic code? I have ODBC connection to a database.

 

[Rabbie]

DoCmd.RunSQL can only run Action Queries(Append,Update,MakeTable etc). Select Queries just define and open a recordset so it doesn't make a lot of sense to run one in VBA.

Why do you want to run the query? What are you trying to do by running it. Let us know and we may be able to help you

 

[mauni]

When user inputs a name and presses "search" button, i want the visual basic code to first check the name field, and then run the SQL search.

If i run the SQL search as a macro, it opens up in a new empty sheet and gives the results there. That's what i want to accomplish with this code also. But i have to use visual basic since i can't put if-sentences in the SQL macro.

Here is the code:



Private Sub searchbutton_Click()

Dim userString As String
Dim sql_suser As String

' if name field is empty it will be given default value of abc

If IsNull(Me.us1) Then
userString = "abc"
Else: userString = Me.us1

sql_suser = "SELECT * from names where name like userString"
DoCmd.RunSQL (sql_suser)

End sub

 

[Pat Hartman]

Check the name field for what? Isn't that what the search will do?

You shouldn't be opening recordsets directly for the users. All user interaction should be via forms where you can control access and ensure that the saved data is valid.

 

[mauni]

Hi

Check the field whether it is empty or not.

If it is empty = search will be committed with default value (string "abc").

If it is not empty = search will be committed what user inputs.

Please help me with this. I don't know how to do the if-sentence without visual basic code. The field has to be checked.

 

[mauni]

I found out the solution. For anybody else looking for similar code here's how you do it:

1) create a query

2) after first run you have to comment that block out. And only keep calling that same query (you can also modify it by entering a new string in strSQL, that will be saved).


' CREATE NEW QUERY
'
'Dim db As dao.Database
'Dim qdef As dao.QueryDef
'Dim strSQL As String
'Set db = CurrentDb
'strSQL = "SELECT * from users;"
'Set qdef = db.CreateQueryDef("User query results", strSQL)
'qdef.Close
'Set qdef = Nothing
'Set db = Nothing
'DoCmd.OpenQuery "User query results", acViewNormal


' CALL / MODIFY EXISTING QUERY
'
Dim db As dao.Database
Dim qdef As dao.QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * from users;"
Set qdef = db.QueryDefs("User query results")
qdef.SQL = strSQL
qdef.Close
Set qdef = Nothing
Set db = Nothing
DoCmd.OpenQuery "User query results", acViewNormal