Using String value in an sql statement.

[abhay_547]

Hi All,

I have below sql statement in my excel macro I want to define string for mydatabase name instead of hardcoding it's name in the below sql statement.

My database name is : mydata
I am trying get the above name from a combobox selection.

Dim mydata As String
mydata = Userform1.Combobox4.Value

Can you guys tell me how to use the above string in the below sql statement so that it accepts the value selected by user in a combobox as a database table name.

cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code FROM Data_SAP.dbo.mydata mydata INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM  ON (mydata.[Company Code] = CRM.[Company Code])INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM  ON (mydata.[Cost Center] = CCM.[Cost Center])INNER JOIN Data_SAP.dbo.[Cost Element Mapping] CEM  ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)WHERE CRM.Country IN (" & selection1 & ") AND CCM.[Sub Product UBR Code] IN (" & selection & ") AND CEM.FSI_LINE3_code IN (" & selection2 & ")AND mydata.year = '" & ComboBox4.Value & "' AND mydata.period = '" & ComboBox3.Value & "'AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'

Thanks a lot for your help in advance.

[si_the_geek]

The same way that you are using other variables - end the string (with " ), append the variable to it (with & ), then re-start the string (with " ).

[abhay_547]

Hi Geek,

Thanks a lot for your reply, Is the below way the correct way to write it ?.

Existing line of code :

cmd1.CommandText = "SELECT mydata.*

Modified Line of Code :

cmd1.CommandText = "SELECT " & mydata & ".*

Thanks a lot for your help in advance.

[si_the_geek]

That is correct.