VBA to SQL DB with user input and results to worksheet

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
 
R

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
 
C

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

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top