How To Execute A Select Query With VBA In Excel 2003

I have the following query in MS Excel VBA 2003.  I keep getting an error message saying cannot execute a SELECT query.  How can I get around this?
Sql = "SELECT Username, Password, ReleaserStatus " & _
             "FROM UserTable " & _
             "WHERE Username = ' " & Sheets("MainPage").txtUName.Value & " ' "
 
db.Execute Sql

Open in new window

rgrguricAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Are you trying to get the query results into a recordset?

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(Sql)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
You can't execute a Select query that way because there is nowhere for the output to go.  You need to put the select query inside some sort of object that "knows" how to manage the output.  

What do you want to do with the result set from the select?

0
Saurabh Singh TeotiaCommented:
Did you meant this..?
Saurabh...

Sql = "SELECT Username, Password, ReleaserStatus " & _
             "FROM UserTable " & _
             "WHERE Username =  """ & Sheets("MainPage").txtUName.Value & """;"
             
 
Db.Execute Sql

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rgrguricAuthor Commented:
Here is the rest of the code.
    Dim db As DAO.Database
    Dim strSQL As String, dbPath As String, var, td As DAO.tabledef
    Sheets("MainPage").Activate
    
    dbPath = "C:\DB.mdb"
    Set db = OpenDatabase(dbPath)
    Sql = "SELECT Username, Password, ReleaserStatus " & _
             "FROM UserTable " & _
             "WHERE Username = ' " & Sheets("MainPage").txtUName.Value & " '"
 
    db.Execute Sql

Open in new window

0
Eric ShermanAccountant/DeveloperCommented:
Where is UserTable stored, is it in an external database???

Try using the   Data ----> Get External Data ----> New Database Query   from the menu in Excel.

ET
0
rgrguricAuthor Commented:
The UserTable is stored in an MS Access Database


0
Eric ShermanAccountant/DeveloperCommented:
>>>>The UserTable is stored in an MS Access Database<<<<<<<

From the top menu in Excel ...

1.) Data ----> Get External Data ----> New Database Query
2.) When the Choose Data Source window opens, select MS Access Database*
3.) Navigate to your database and follow the remaining prompts.


ET  
0
dqmqCommented:
Even if that code SELECT were to run, it doesn't accomplish anything because you are not doing anything with the result set.  I infer it is intended to return a password and release status, but then what?

What action do you want if 0 rows are returned?
What action do you want if 1 row is returned?
What action do  you want if >1 row are returned?

Please explain what you want to do with the result set from the select.
0
rgrguricAuthor Commented:
Right now I'm just trying to open the connection to the database and run a query on the table UserTable to ensure it works.  Setting it up as a recordset appears to be the way to go.
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Sql As String, dbPath As String
    
    dbPath = "C:\DB.mdbdb"
    Sheets("MainPage").Activate
    
    Set db = OpenDatabase(dbPath)
    
    Sql = "SELECT Username, Password, ReleaserStatus " & _
             "FROM UserTable"
    
    Set rs = db.OpenRecordset(Sql)
    rs.MoveFirst
        
    MsgBox (rs![UserName] & " " & rs![Password] & " " & rs![ReleaserStatus])

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.