Type mismatch when querying from ODBC table

[mikemny]

Hello All,

I am getting a type mismatch (run time error '13') when I run the below macro, trying to pull data from a database table:

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Returns").Select
Dim ports As String
ports = Range("BA5").Value
 
Range("E6").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _     "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X1234;", _
       Destination:=Range("$E$6")).QueryTable
 
strSQL = "SELECT" _
   & "perf_cust_port_bm_return.portfolio_name," _
   & "perf_cust_port_bm_return.portfolio_full_name," _
   & "perf_cust_port_bm_return.port_mv," _
   & "perf_cust_port_bm_return.mtd_port," _
   & "perf_cust_port_bm_return.mtd_bench," _
   & "perf_cust_port_bm_return.mtd_diff," _
   & "perf_cust_port_bm_return.qtd_port," _
   & "perf_cust_port_bm_return.qtd_bench," _
   & "perf_cust_port_bm_return.qtd_diff," _
   & "perf_cust_port_bm_return.ytd_port," _
   & "perf_cust_port_bm_return.ytd_bench," _
   & "perf_cust_port_bm_return.ytd_diff," _
   & "perf_cust_port_bm_return.begin_dt," _
   & "perf_cust_port_bm_return.end_dt," _
   & "perf_cust_port_bm_return.asof_dt," _
   & "perf_cust_port_bm_return.ov_status" _
& "FROM db_firmprod.dbo.perf_cust_port_bm_return perf_cust_port_bm_return" _
& "GROUP BY" _
   & "perf_cust_port_bm_return.portfolio_name" _
   & "perf_cust_port_bm_return.portfolio_full_name," _
& "HAVING" _
   & (" & ports & ") _
   & "AND (perf_cust_port_bm_return.end_dt={ts '2011-01-31 00:00:00'})" _
   & "AND (perf_cust_port_bm_return.asof_dt={ts '2011-02-14 00:00:00'})"
 
.CommandText = Array(strSQL)
 
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_firm_prod_1"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("Table_Query_from_firm_prod_1").TableStyle = _
   "TableStyleLight8"
Range("D5").Select
End Sub

WHAT I'M TRYING TO DO:

There is a table that stores data on many different portfolios (table is called "perf_cust_port_bm_return"). I would like to be able to type the name of the portfolio(s) I would like to see data for, run a macro, and the data is returned to me. The name(s) of the portfolios and the number of portfolios will vary.

I would also like the dates to be variable, although the macro above currently has them set as a particular date. There are two dates needed: an "as of" date, and the most recent previous business day date (the data is posted to the same table daily, and I want to grab the most recent data for that "as of" date).

For the portfolio names, I will list them in column B. For example:
B7 = Portfolio1
B8 = Portfolio2
B9 = Portfolio3

In column BA I have the following formulas set up that produce the following results (I went all the way over to BA so the following formulas will be out of normal viewing, but still easily accessed if something needs to be updated):
BA7 = 'Portfolio1' OR
BA8 = 'Portfolio2' OR
BA9 = 'Portfolio3' OR

In cell BA5, I have a formula that combines the values of BA7 and below, dropping the last "OR":
'Portfolio1' OR 'Portfolio2' OR 'Portfolio3'

The macro above uses the value of BA5.

I have tried many different versions of this macro, but just can't get this to work. I have very limited experience pulling data from database tables, so I appreciate your patience with me on this.

Any help you would be able to provide would be GREATLY appreciated!

Thanks!

 

[Norie]

Where in that code do you get the error?

 

[mikemny]

I get the error in the line:

.CommandText = Array(strSQL)

Thanks.

 

[Norie]

That would suggest there's something wrong with the SQL string.

It seems to be correct apart from some missing spaces.

There should be a space after SELECT and before FROM.

There could be other errors, but they're the most obvious.

 

[Richard Schollar]

Also, don't pass it as an array - pass it as a simple string.

.CommandText = strSQL

 

[mikemny]

I have added a space after SELECT and before FROM;

strSQL = "SELECT " _ 
 
....
 
& " FROM db_firmprod.dbo.perf_cust_port_bm_return perf_cust_port_bm_return" _

Doing this, I still get the Type mismatch error.

If I don't pass it as an array as suggested above, I get a SQL Syntax Error on the line:
.Refresh BackgroundQuery:=False

Any ideas?

 

[Norie]

There should be spaces before and after HAVING and GROUP BY.

There shouldn't be a coom after 'perf_cust_port_bm_return.portfolio_full_name' in the line before the HAVING part.

 

[mikemny]

Unfortunately, I'm still getting the SQL Syntax Error. Is everything correct with what I have set as "ports"? Do I need to add the name of the table before each portfolio name? Thanks again for the help, it is greatly appreciated!

Sub Macro()
Sheets("Returns").Select
Dim ports As String
ports = Range("BA5").Value

Range("E6").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X1234;", _
    Destination:=Range("$E$6")).QueryTable
        
strSQL = "SELECT " _
    & "perf_cust_port_bm_return.portfolio_name," _
    & "perf_cust_port_bm_return.portfolio_full_name," _
    & "perf_cust_port_bm_return.port_mv," _
    & "perf_cust_port_bm_return.mtd_port," _
    & "perf_cust_port_bm_return.mtd_bench," _
    & "perf_cust_port_bm_return.mtd_diff," _
    & "perf_cust_port_bm_return.qtd_port," _
    & "perf_cust_port_bm_return.qtd_bench," _
    & "perf_cust_port_bm_return.qtd_diff," _
    & "perf_cust_port_bm_return.ytd_port," _
    & "perf_cust_port_bm_return.ytd_bench," _
    & "perf_cust_port_bm_return.ytd_diff," _
    & "perf_cust_port_bm_return.begin_dt," _
    & "perf_cust_port_bm_return.end_dt," _
    & "perf_cust_port_bm_return.asof_dt," _
    & "perf_cust_port_bm_return.ov_status" _
& " FROM db_firmprod.dbo.perf_cust_port_bm_return perf_cust_port_bm_return" _
& " GROUP BY" _
    & "perf_cust_port_bm_return.portfolio_name" _
    & "perf_cust_port_bm_return.portfolio_full_name" _
& " HAVING" _
    & (" & ports & ") _
    & "AND (perf_cust_port_bm_return.end_dt={ts '2011-03-31 00:00:00'})" _
    & "AND (perf_cust_port_bm_return.asof_dt={ts '2011-04-27 00:00:00'})"
        
.CommandText = strSQL
           
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_firm_prod_1"
.Refresh BackgroundQuery:=False
End With

ActiveSheet.ListObjects("Table_Query_from_firm_prod_1").TableStyle = _
    "TableStyleLight8"
Range("D5").Select
End Sub

 

[Norie]

You don't seem to have added all the spaces I mentioned - it's before and after HAVING and GROUP BY.

You also need spaces before and after the ANDs.

That's all I can see right now.

What should the SQL look like for the query you want?

 

[mikemny]

I have added the spaces, and now I get a "General ODBC Error", highlighting line:
.Refresh BackgroundQuery:=False

I'm afraid I don't understand your last question. Are you referring to how I want the end output to look like?

Thank you again for your help and patience. As you can see, my knowledge in SQL is pretty limited.