Query Access from Excel

[austenr]

I would like to use the code below in an EXCEL sub but want to let the user select what they want to pull from the DB. Is there a way to do it? In other words, the user would input their SELECT, FROM, WHERE and ORDER BY.

[VBA] 'creates the SQL statement
szSQL = "SELECT GroupName, GroupContactName " & _
"FROM Groups " & _
"WHERE Group = 'UK' " & _
"ORDER BY GroupName" [/VBA]

[Norie]

Do you mean you want the user to specify table, field and criteria?

How would you be getting user input?

[malik641]

I would suggest a userform. Tons of options you could choose to go about doing this.

[austenr]

Yes I want the user to specify the table, fields, and criteria.

[matthewspatrick]

Yes I want the user to specify the table, fields, and criteria.

OK, but it probably will not be an improvement over using MS Query...

[Rembo]

I would like to use the code below in an EXCEL sub but want to let the user select what they want to pull from the DB. Is there a way to do it? In other words, the user would input their SELECT, FROM, WHERE and ORDER BY.

[VBA] 'creates the SQL statement
szSQL = "SELECT GroupName, GroupContactName " & _
"FROM Groups " & _
"WHERE Group = 'UK' " & _
"ORDER BY GroupName" [/VBA]

Like malik641 says, a form is the best way to go but it involves a little more work then this:

[VBA]Dim sGroupName As String
Dim sGroupContactName As String
Dim sCountryCode As String

sGroupName = InputBox("Select Group Name")
sGroupContactName = InputBox("Select Group Contact Name")
sCountryCode = InputBox("Select Country Code")

'creates the SQL statement
szSQL = "SELECT " & sGroupName & ", " & sGroupContactName & " " & _
"FROM Groups " & _
"WHERE Group = '" & sCountryCode & "' " & _
"ORDER BY " & sGroupName[/VBA]

Or if you like shorter code:

[VBA]Dim sGroupName As String
sGroupName = InputBox("Select Group Name")

'creates the SQL statement
szSQL = "SELECT " & sGroupName & ", " & InputBox("Select Group Contact Name") & " " & _
"FROM Groups " & _
"WHERE Group = '" & InputBox("Select Country Code") & "' " & _
"ORDER BY " & sGroupName[/VBA]

Rembo

[malik641]

I don't really like input boxes...it just seems sloppy to me

Like I said (and Rembo agreed with ) I would go with a userform. There's more versatility. What if a user doesn't want to filter the country or order by group name?

I would have a userform with check boxes and text boxes to create the string using If statements based on the check boxes they checked (not to mention you'd have to make sure if a check box is checked that the user entered something in that text box).

If they select all the check boxes for the code you gave us, it could look something like:
[vba]szSQL = "SELECT " & txtGroupName.Text & ", " & txtGroupContactName.Text & _
" FROM Groups " & _
"WHERE Group = '" & txtCountry.Text & "' " & _
"ORDER BY " & txtGroupName.Text[/vba]
I like this better than a bunch of pop up windows asking for input.

[XL-Dennis]

I don't really like input boxes...it just seems sloppy to me

I totally disagree and they can achieve the tasks quite simple as long as we know what we do.

[vba]
Option Explicit
Sub InputBox_Not_Sloopy()
Dim vaInput1 As Variant
Do
vaInput1 = Application.InputBox(Prompt:="Add a string value", _
Title:="Not sloopy", _
Type:=2)

Loop While vaInput1 = ""
'The user canceled the operation
If vaInput1 = False Then Exit Sub
'Additional control for the received value.
'
'
'Print the value in the immediate window
Debug.Print vaInput1
End Sub
[/vba]

Rembo's second shorter solution is not to recommend as it does not allow any control of the retrieved values. A more structure approach is to retrieve the values from the user, check them and then finally send them to the SQL-expression. In that way we always make sure that the values in the expression are valid.

Kind regards,
Dennis

[malik641]

I guess I should have been more clear when I said "sloppy" (not sloopy [even though I found this kinda funny, I have a strange feeling you meant to write it like that] ). What I meant was sloppy for the user, not the code. I will agree that the code could be more neat and tidy. I just think that users might find input boxes a little tedious, and that they would find a userform a little more elegant IMHO. But then again, that's just me.