General ODBC Error

[jrichter]

My first post here. I have been using this website for a long time to learn VBA and it has helped me a lot! I am having a problem that I have been unable to find a useful post for and I need some help. I have a macro that is hitting a Remedy database using the ODBC driver and works well on my machine. However, when I try to send this Excel file to another user at a different work station, any attempt to run the macro results in a General ODBC Error at the '.Refresh BackgroundQuery:=False' line. I hope I have used the 'Code' button properly so you can view the code. Any help will be greatly appreciated. Thanks!

StartDate = InputBox("Please enter the start date")
EndDate = InputBox("Please enter the end date")


    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Remedy;ARServer=server.blah.blah;UID=xxx;PWD=xxx;ARAuthentication=;ARUseUnderscores=1;ARNameReplace" _
        ), Array("=1;SERVER=NotTheServer")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ""MNT:Maintenance"".RequestID, ""MNT:Maintenance"".Region, ""MNT:Maintenance"".System, ""MNT:Maintenance"".""Device Alias+"", ""MNT:Maintenance"".PlannedStartDate, ""MNT:Maintenance"".""HCF Fibernode""" & Chr(13) & "" & Chr(10) & "FROM """ _
        , _
        "MNT:Maintenance"" ""MNT:Maintenance""" & Chr(13) & "" & Chr(10) & "WHERE (""MNT:Maintenance"".PlannedStartDate>{ts '" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") & "'} And ""MNT:Maintenance"".PlannedStartDate<{ts '" & Format(EndDate, "yyyy-mm-dd hh:mm:ss") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY ""MNT:Maintenance""." _
        , "Region, ""MNT:Maintenance"".System")
        .Refresh BackgroundQuery:=False
    End With

 

[jrichter]

Any Takers?

Can anybody help me out on this??

 

[Cindy Ellis]

Hi,
I don't have experience with Remedy data sources, but with ODBC in general, each user has to have the DSN set properly to the same named data source in order for the query to work properly. It's the ".Refresh" that actually initiates the attempt to connect to the database and retrieve the records, so errors in the first statements may not show up until that line.
Use ODBC Admin from the Control Panel (on Windows systems) or C:\WINNT\system32\odbcad32.exe from the Start - Run dialog to access the ODBC data source administrator. Verify that the user(s) have the same DSN name for the Remedy database as you do. (Look at both User DSN and System DSN). If they don't, add a data source that matches what you have on your system. If they already have the data source, and it matches what's on your system, it's possible that security access levels are preventing their access to the server (just a guess).
Hope this helps,
Cindy

 

[jrichter]

HOLY S*** Cindy you are AWESOME! That was it exactly. I knew it had something to do with that, but I didnt know about going straight to the ODBC Source Admin tool directly. In Excle you dont see near as many options as there are in the tool you pointed me towards. Thank you VERY much for taking the time to square me away!