ODBC error in Excel macro, on one particular PC

[elecooley]

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:

With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=ADW;UID=CurUser;APP=Microsoft Office 2003;WSID=CP040;DATABASE=DataWarehouse;Trusted_Connection=Yes" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "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'})")
        '"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")
        .name = "Query from ADW"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False

Thanks for any help!

[daniel aristidou]

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..

[elecooley]

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?

[kadghar]

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

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.

[Killer42]

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".

[daniel aristidou]

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