How can we create variabel name in VBA for SQL querying MS Access

[castlebuilder]

Here is the code:

Private Sub OK_select_Click()


If ListBox1.ListIndex = -1 Then
bts_name = "Nothing"
Else
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then _
bts_name = ListBox1.List(i)
Next i
End If
MsgBox "You selected: " & vbCrLf & bts_name

Workbooks.Open Filename:= _
"C:\castlebuilder\Database\NDbase_excel\BTS Daily Performance.xls"
Range("A1&quot.CurrentRegion.Copy
Sheets("RAW DATA&quot.Select
' delete all data in shee RAW DATA including the filter
Cells.Select
Selection.ClearContents
Selection.ClearContents
Selection.ClearContents
' ends here
Range("A1&quot.Select

' ****** manual 1

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=c:\castlebuilder\Database\New_Access_Database\KPI Database.mdb;DefaultDir=c:\castlebuilder\Database\" _
), Array( _
"New_Access_Database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Sheets("RAW DATA&quot.Range("A1&quot)
.CommandText = Array( _
"SELECT bts_kpi.date, bts_kpi.BSC, bts_kpi.BTS_NAME, bts_kpi.nqi, bts_kpi.cssr, bts_kpi.hosr, bts_kpi.tdropp_old, bts_kpi.`ulqual5 %`, bts_kpi.`dlqual5 %`, bts_kpi.sdcch_access, bts_kpi.sdcch_succ, bts" _
, _
"_kpi.tch_access, bts_kpi.`sssr %`, bts_kpi.`TCH Traffic`, bts_kpi.`TCH Seiz_ Attempts`, bts_kpi.`SDCCH Traffic`, bts_kpi.`SDCCH Seiz_Attempts`, bts_kpi.`sdcch_util_1%GOS`, bts_kpi.`tch_util_2%GOS`, bt" _
, _
"s_kpi.bhttraf, bts_kpi.bhstraf, bts_kpi.bhour, bts_kpi.ttraffic_erlmin, bts_kpi.sms_traf, bts_kpi.`sms_suc %`, bts_kpi.ave_call_length_old, bts_kpi.ave_call_length_old2, bts_kpi.ave_call_length_new, b" _
, _
"ts_kpi.ave_call_length_conv, bts_kpi.`%_MOC`" & Chr(13) & "" & Chr(10) & "FROM `c:\castlebuilder\Database\New_Access_Database\KPI Database`.bts_kpi bts_kpi" & Chr(13) & "" & Chr(10) & "WHERE (bts_kpi.BSC=bts_name)" & Chr(13) & "" & Chr(10) & "ORDER BY bts_kpi.date" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


' my problem is that when assign a value for bts_name
'with respect to listbox, the value in it wont assign. It is
'still read literally as bts_name not btsa_name as
'changing variable.

'hope you can help me

 

[Zathras]

Instead of

"WHERE (bts_kpi.BSC=bts_name)" & Chr(13) & "" & Chr(10) & "ORDER BY bts_kpi.date" _

try

"WHERE (bts_kpi.BSC=

'" & bts_name & "'

)" & Chr(13) & "" & Chr(10) & "ORDER BY bts_kpi.date" _

But if the value in bts_name contains quote characters, further handling will be required.

 

[castlebuilder]

Thanks Zathras, it really worked...

Have a nice Day!

 

[Zathras]

You are welcome.

It is probably not a good idea to have a variable name (e.g. bts_name) the same as a table column name (bts_kpi.BTS_NAME). At the very least, it makes the code harder to read. I recommend that you consider using the Microsoft standard "Hungarian" notation which would suggest a variable name of "sBtsName" or similar.