C
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
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