VBA to SQL DB with user input and results to worksheet

[chansing5]

I want to pull data from SQL server by Excel using vba and ado
connection.

I have created a inputbox to get to parameter from user and pass the
input data to SQL

My VBA script as follow:

Public Sub ConnectToDatabase()


Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"

Dim InP As String
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open


InP = Application.InputBox(prompt:="Country", Type:=2)



Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordset)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub

I get error message as compile error: constant expression required.
the Cuspo is pointing to the input variable InP.

If I replace InP with a contant say 'USA', then I got the correct
result.

I don't know why I can't get the result by a input box parameter.

Please help me out

 

[RB Smissaert]

SQL should be a variable, as I don't think you can add a variable to a
constant, so do:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

RBS

 

[chansing5]

SQL should be a variable, as I don't think you can add a variable to a
constant, so do:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

RBS






















- Show quoted text -

I still got the same error message after using the following:

Dim SQL As String

SQL = "SELECT * FROM Customers WHERE Country = '" & InP & "'"

 

[RB Smissaert]

It works fine here and I had the same compile error with
your old code.
Could you post the whole Sub again?

RBS

 

[chansing5]

Here is my old code:

Public Sub ConnectToDatabase()


Const ConnectionString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=paul;Initial Catalog=Northwind;Data
Source=HP-95097A19B77A"
Dim InP As String

Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open


InP = Application.InputBox(prompt:="Country", Type:=2)



Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
"SELECT * FROM Customers WHERE Country = '" & InP & "'"

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected,
CommandTypeEnum.adCmdText)

Debug.Print "Rows affected " & RowsAffected
Call ActiveSheet.Range("A3").CopyFromRecordset(Recordset)

If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
End If

End Sub