General ODBC Error - Refresh BackgroundQuery:=False

[Shind1]

Hi,

Im getting a Runtime Error '1004' when using the below code. The error seems to be coming from ".Refresh BackgroundQuery:=False". I hgave looked at the other forums for similar issues but cant seem to find a solution.


Sub CreateRiskStorePivotConnection(TabName As String, TableName As String, DeskName As String)

Dim BooksFilter As String
BooksFilter = CreateBooksFilter(DeskName)

With ThisWorkbook.Worksheets(TabName).QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=lnhybrprd;UID=positions_server;PWD=m0rgan1;DBQ=KAPPOS;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful" _
), Array(";NUM=NLS;DPM=F;MTS=F;MDI=F;CSR=F;FWC=F;FBS=60000;TLO=0;")), _
Destination:=ThisWorkbook.Worksheets(TabName).Range("A1"))
.CommandText = "SELECT * FROM ""KAPPOSDB"".""" & TableName & """ WHERE TRADENAME IN (" & CreateTradeFilter & ") "
.FieldNames = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With

End Sub

Thanks

 

[Fazza]

please check the SQL

suggest before the line .commandtext = dadeda
insert debug.print = dadeda
then review the SQL string in the immediate window
compare it with some SQL that definitely works
(maybe manually create a query table and debug.print its SQL)

AND, are those identifiers correct for the database being queried?
FROM ""KAPPOSDB"".""" & TableName & """

the syntax has got to be 100% OK or there'll be an error

cheers