rgrguric
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you meant this..?
Saurabh...
Saurabh...
Sql = "SELECT Username, Password, ReleaserStatus " & _
"FROM UserTable " & _
"WHERE Username = """ & Sheets("MainPage").txtUName.Value & """;"
Db.Execute Sql
ASKER
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
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
Try using the Data ----> Get External Data ----> New Database Query from the menu in Excel.
ET
ASKER
The UserTable is stored in an MS Access Database
>>>>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
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
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.
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.
ASKER
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])
What do you want to do with the result set from the select?