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

RBS


wrote in message
oups.com...
On Apr 15, 4:08 pm, "RB Smissaert"
wrote:
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

wrote in message

ups.com...



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(Recordse t)

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- Hide quoted text -

- 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 & "'"