Run Time Error 1004 in Query To SQL Db

[QB]

Hi

I'm using OLE DB Query in xL 2003 to query an SQL database. I keep getting a run time error when the line .Connection = Range("nConnection").Value in the code below executes. There is no problem with the definition of my named range "nConnection", the connection string is correct and the SQL syntax in cell $A$1 is correct.

Can anyone help a bemused accountant?

Regards

QB

----------------------------------------

Sub Update_05_Sum_AA_CY()
Dim LastColumn As Long
Dim LastRow As Long

Sheets("05_Sum_AA_CY").Activate

' Update Query
With Range("Sum_AA_CY").QueryTable
.Connection = Range("nConnection").Value
.CommandType = xlCmdSql
.CommandText = Range("$A$1").Value
.Refresh BackgroundQuery:=False
End With

' Set Data Range Properties
With Range("Sum_AA_CY").QueryTable
.Name = "Sum_AA_CY"
.SavePassword = False
.BackgroundQuery = False
.RefreshPeriod = 0
.RefreshOnFileOpen = False
.SaveData = False
.FieldNames = True
.RowNumbers = False
.AdjustColumnWidth = False
.PreserveColumnInfo = True
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.FillAdjacentFormulas = True
End With

....
....
....

End Sub

 

[doofusboy]

Have you tried prefacing with what sheet the Range is on ?

Something like this?

' Update Query
With [B][COLOR=#ff0000]YourSheetNameHere.[/COLOR][/B]Range("Sum_AA_CY").QueryTable
.Connection = [B][COLOR=red]YourSheetNameHere.[/COLOR][/B]Range("nConnection").Value
.CommandType = xlCmdSql
.CommandText = [B][COLOR=#ff0000]YourSheetNameHere.[/COLOR][/B]Range("$A$1").Value
.Refresh BackgroundQuery:=False
End With

 

[QB]

Hi

Thanks for the suggestion. I had tried to add the worksheet name to the connection line without success. I still get the RTE 1004 when I add the names as you suggest.

Can anyone else help me?

Regards

QB

 

[QB]

Can any of the MVPs help me on this problem?