I have a problem regarding passing parameter from excel spreadsheet to a
backend sql
database to refresh data.

For example, I already add a text box in an excel spreadsheet to let me
pass in whatever production year I needed e.g. 1998, 1999, 2000, 2001,
....... etc. as a parameter.
but without success. Can anyone suggest a code from excel text box sending
any of the
year parameter to the database to refresh the data in excel according to the
year entered
by the user.
Sample of my code is shown below.


' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * FROM STOCKMASTER WHERE PRODUCTION_YEAR = 2002;"

.Open Source:=Src, ActiveConnection:=Connect
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connect.Close
Set Connect = Nothing

Can someone help me to solve this problem and any solution provided will be
highly appreciated.

If I understand you correctly... Where you currently have a hard coded year
in your SQL statement you want to use the value of a cell...

Src = "SELECT * FROM STOCKMASTER WHERE PRODUCTION_YEAR = " &
sheets("Sheet1").range("A1") & ";"

HTH

"alvin" wrote:

I have a problem regarding passing parameter from excel spreadsheet to a
backend sql
database to refresh data.

For example, I already add a text box in an excel spreadsheet to let me
pass in whatever production year I needed e.g. 1998, 1999, 2000, 2001,
....... etc. as a parameter.
but without success. Can anyone suggest a code from excel text box sending
any of the
year parameter to the database to refresh the data in excel according to the
year entered
by the user.
Sample of my code is shown below.


' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * FROM STOCKMASTER WHERE PRODUCTION_YEAR = 2002;"

.Open Source:=Src, ActiveConnection:=Connect
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connect.Close
Set Connect = Nothing

Can someone help me to solve this problem and any solution provided will be
highly appreciated.