QueryTables.Add(Connection:= (2003 SP3)

Topic

New Reply

What is strValue?

Reply | Quote

Replies

My mistake. It should say strChain which is the value passed in. Sorry about that.

Reply | Quote

What happens if you use

.CommandText = "SELECT DISTINCT CM_CLIENT.CHAIN, " & _
"CM_Service_Line_Master.ServiceLineDescription, CM_CLIENT.LINE_OF_BUSINES " & _
"FROM (CM_DEBTOR INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client) " & _
"INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID " & _
"WHERE CM_CLIENT.CHAIN = " & Chr(34) & strChain & Chr(34)

Reply | Quote

Hans,
I did two things.
1. I defined a variable called strSQL in the beginning of the procedure like this:
Dim strSQL as string

strSQL = “SELECT DISTINCT CM_CLIENT.CHAIN, ”
strSQL = strSQL & “CM_Service_Line_Master.ServiceLineDescription, ”
strSQL = strSQL & “CM_CLIENT.LINE_OF_BUSINES FROM CM_DEBTOR ”
strSQL = strSQL & “INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client ”
strSQL = strSQL & “INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID ”
strSQL = strSQL & “Where CM_CLIENT.CHAIN = ‘” & strChain & “‘”

2. I took the ” =Array(“…”) ” part out of the .CommandText = Array(“…”)
and replaced it with .CommandText = strSQL and it works.

It seems that if you build your query by recording a macro, it inserts the .CommandText = Array(“…”) for some reason.

As always, thanks for your guidance.

Reply | Quote

Congrats on getting it to work.

Yes, the macro recorder creates an array, not sure why. It isn’t necessary, as you have found.

Reply | Quote

I’m attempting to populate Sheet1 with a list of data from our SQL Server 2005 database. The following query works well if I don’t add the WHERE clause.
I keep getting a type mismatch error with the .CommandText = Array line when I add the WHERE clause.

Some other useful info regarding my question:
1. I pass in a code called strChain
2. Each chain has 1 or more service lines
3. I want to return only the service lines for the chain code that is passed in.
4. If you know of a better way to pass in an SQL string to the database, I’m happy to give it a try!

Sub ChainSvcLines(ByVal strChain As String)
On Error GoTo Error_Handler

With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DRIVER=SQL Server;SERVER=999.99.99.9;UID=user123;PWD=*********;APP=Microsoft Office 2003;WSID=XXX-XXXXXX” _
, Destination:=Range(“I2”))
.CommandText = Array(“SELECT DISTINCT CM_CLIENT.CHAIN, ” & _
“CM_Service_Line_Master.ServiceLineDescription , CM_CLIENT.LINE_OF_BUSINES ” & _
“FROM CM_DEBTOR INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client ” & _
“INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID ” & _
“WHERE CM_CLIENT.CHAIN = ‘ ” strChain & ” ‘ “)
.Name = “qryChainLOB”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Error_Handler:
MsgBox Err.Number & ” ” & Err.Description

End Sub

As always, any suggestions would be most helpful.
Thanks,

Reply | Quote