Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run time error 1004 General ODCB Error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Run time error 1004 General ODCB Error
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(Arra y( _
"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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default Run time error 1004 General ODCB Error
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" wrote in message
om...
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(Arra y( _
"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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Run time error 1004 General ODCB Error
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" wrote in message ...
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" wrote in message
om...
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(Arra y( _
"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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default Run time error 1004 General ODCB Error
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


"Kevin" wrote in message
om...
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" wrote in message
...
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" wrote in message
om...
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(Arra y( _
"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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
ODCB Error 1919 and ODCB Error 6 Boblink Setting up and Configuration of Excel 1 January 20th 07 10:18 PM
Run time error 1004, General ODBC error [email protected] New Users to Excel 0 September 19th 05 01:41 AM
Runtime error '1004' General ODBC error star_lucas New Users to Excel 0 August 29th 05 04:09 PM
Application Run Time Error 1004 and Stack Error ExcelMonkey[_190_] Excel Programming 9 February 11th 05 04:48 PM
VBA error - run-time error '1004': Romanian37 Excel Programming 3 May 21st 04 03:25 PM


All times are GMT +1. The time now is 06:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement