Run time error 1004 General ODCB Error

[Kevin]

Hi
the code below works until either a new name is added to the list or
a name is removed can anyone tell me why and how to fix the problem

Sub Weekly_values()
ThisWorkbook.Worksheets("SC INFO").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\KCARTE10\Desktop\working
on\team_sfr.mdb;DefaultDir=C:\Documents and Setti" _
), Array( _
"ngs\KCARTE10\Desktop\working on;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("C33:bd42"))
.CommandText = Array( _
"SELECT team_members_weekly_values.Names,
team_members_weekly_values.`1`, team_members_weekly_values.`2`,
team_members_weekly_values.`3`, team_members_weekly_values.`4`,
team_members_weekly_values.`5`," _
, _
" team_members_weekly_values.`6`,
team_members_weekly_values.`7`, team_members_weekly_values.`8`,
team_members_weekly_values.`9`, team_members_weekly_values.`10`,
team_members_weekly_values.`11`, team_" _
, _
"members_weekly_values.`12`, team_members_weekly_values.`13`,
team_members_weekly_values.`14`, team_members_weekly_values.`15`,
team_members_weekly_values.`16`, team_members_weekly_values.`17`,
team_" _
, _
"members_weekly_values.`18`, team_members_weekly_values.`19`,
team_members_weekly_values.`20`, team_members_weekly_values.`21`,
team_members_weekly_values.`22`, team_members_weekly_values.`23`,
team_" _
, _
"members_weekly_values.`24`, team_members_weekly_values.`25`,
team_members_weekly_values.`26`, team_members_weekly_values.`27`,
team_members_weekly_values.`28`, team_members_weekly_values.`29`,
team_" _
, _
"members_weekly_values.`30`, team_members_weekly_values.`31`,
team_members_weekly_values.`32`, team_members_weekly_values.`33`,
team_members_weekly_values.`34`, team_members_weekly_values.`35`,
team_" _
, _
"members_weekly_values.`36`, team_members_weekly_values.`37`,
team_members_weekly_values.`38`, team_members_weekly_values.`39`,
team_members_weekly_values.`40`, team_members_weekly_values.`41`,
team_" _
, _
"members_weekly_values.`42`, team_members_weekly_values.`43`,
team_members_weekly_values.`44`, team_members_weekly_values.`45`,
team_members_weekly_values.`46`, team_members_weekly_values.`47`,
team_" _
, _
"members_weekly_values.`48`, team_members_weekly_values.`49`,
team_members_weekly_values.`50`, team_members_weekly_values.`51`,
team_members_weekly_values.`52`, team_members_weekly_values.`Total`" &
Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Setting" _
, _
"s\KCARTE10\Desktop\working
on\team_sfr`.team_members_weekly_values team_members_weekly_values")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

thanks

kevin

 

[RB Smissaert]

Why?
Because the names are hard-coded in your query.

How to solve?
Either don't change the names or make some code that alters the query
according
to the present names.

RBS

 

[Kevin]

Thanks for relpy
The names in the query we have no control over this means that people
will move from area to area also they may not use the system for
while, so not changing names is not an option.
Writing code to amend the query tha only reads the current names, can
you suggest a sulution as i dont know where to start
thanks
kev

 

[RB Smissaert]

Well, you will have to make some code that builds your SELECT clause from
the names.
I don't know your details, but it will be something like below. You will
have to figure out
what sheet and what range holds the names. Then replace the bit in your
query that is the SELECT
clause with the the variable strSELECT.

Sub BuildSELECT()

Dim i As Long
Dim strSelect As String

strSelect = "SELECT "

For i = 1 To 52
If i < 52 Then
strSelect = strSelect & Cells(i) & ", "
Else
strSelect = strSelect & Cells(i) & " "
End If
Next

End Sub


RBS