By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

ODBC error in Excel macro, on one particular PC

P: 13
Baically, myself and the person I sit next to use a shortcut to an Excel file. The Excel file has multiple macros. When office mate opens it, it works fine (ie all macros run, excel file gets data,). When i click on the shortcut, I get an error pop up: Run time error 1004' General ODBC error.

When i click on Debug, VB opens and it shows:

Object = General
Procedure = Get_program_dates
and highlighted in Yellow is: .Refresh BackgroundQuery:=False

I have searched the internet for the error 1004 and found many solutions that dont work. The real strange thing is why it does not work on my machine, but on the guys next to me it does? We have checked that we have the same connections (Data Sources). Is this a problem with something else? Even the guy (not myself) who wrote the code can't figure out why it is not working. I found where someone said it might have to do with dates? But why does it work on his and not mine?

Here is the snippet of code:

Expand|Select|Wrap|Line Numbers
  1. With ActiveSheet.QueryTables.Add(Connection:= _
  2.         "ODBC;DSN=ADW;UID=CurUser;APP=Microsoft Office 2003;WSID=CP040;DATABASE=DataWarehouse;Trusted_Connection=Yes" _
  3.         , Destination:=Range("A1"))
  4.         .CommandText = Array( _
  5.         "SELECT DISTINCT STRAT_All_View.EvSDate" & Chr(13) & "" & Chr(10) & "FROM DataWarehouse.dbo.STRAT_All_View STRAT_All_View" & Chr(13) & "" & Chr(10) & "WHERE (STRAT_All_View.EvSDate>{ts '2005-03-01 00:00:00'})")
  6.         '"SELECT DISTINCT STRAT_All_View.EvSDate" & Chr(13) & "" & Chr(10) & "FROM DataWarehouse.dbo.STRAT_All_View STRAT_All_View" & Chr(13) & "" & Chr(10) & "ORDER BY STRAT_All_View.EvSDate DESC")
  7.         .name = "Query from ADW"
  8.         .FieldNames = True
  9.         .RowNumbers = False
  10.         .FillAdjacentFormulas = False
  11.         .PreserveFormatting = True
  12.         .RefreshOnFileOpen = False
  13.         .BackgroundQuery = True
  14.         .RefreshStyle = xlInsertDeleteCells
  15.         .SavePassword = False
  16.         .SaveData = True
  17.         .AdjustColumnWidth = True
  18.         .RefreshPeriod = 0
  19.         .PreserveColumnInfo = True
  20.         .Refresh BackgroundQuery:=False
Thanks for any help!
Jan 31 '08 #1
Share this Question
Share on Google+
5 Replies


daniel aristidou
100+
P: 491
Since he said it might be date...which is possible have you checked you computer date an sync to get correct time + Date....
Also check that the network connection...if the file is found over a network..
Jan 31 '08 #2

P: 13
Since he said it might be date...which is possible have you checked you computer date an sync to get correct time + Date....
Also check that the network connection...if the file is found over a network..
I checked both ofb these and they are fine. I am wondering if it is a setting on my actual computer? All other macros in other documents work fine..

Thanks for the help Daniel. I appreciate the quick response. Any other suggestions?
Jan 31 '08 #3

kadghar
Expert 100+
P: 1,295
I think the error comes from the Connection. But it's shown in "refresh" line because there is when excel connects with the data base.

write

Expand|Select|Wrap|Line Numbers
  1. msgbox (.connection)
before you refresh the querytable, this will show you the connection path, so you can check it.

now, what i've seen, is that you have written

"ODBC;DSN=ADW;UID=CurUser;APP=Microsoft Office 2003;WSID=CP040;DATABASE=DataWarehouse;Trusted_Con nection=Yes" _
, Destination:=Range("A1"))

check how 'Trusted_con nection=Yes' has a space between both n's, I'd say you dont have that space in your original code, since this wont even let you run the the macro... but you never know =P.
Jan 31 '08 #4

Expert 5K+
P: 8,434
Just on the date theme, it might be worth checking whether both PCs have the same settings for date format.

Also, can you try it on any other PCs? It might be useful to know which one is the "exception".
Feb 1 '08 #5

daniel aristidou
100+
P: 491
Just on the date theme, it might be worth checking whether both PCs have the same settings for date format.

Also, can you try it on any other PCs? It might be useful to know which one is the "exception".
Go to the regional settings in the control panel and check the date formats
Feb 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.