VBA/Excel 2007 and Oracle Connection String

[darth.pathos]

Good afternoon all.......

OK, I admit is has been a very long time since I've done VBA Coding (changed jobs, new job didn't require it until now...). However, this is not something that should be too terribly difficult, and I cannot figure out where my problem is.

I have a query (see below) connecting me to an Oracle database. When I run the code from my computer, everything works beautifully. However, when I try the code from another PC, even if I'm logged into that other machine, I get an "Application-defined or Object-defined error" on the CN.OPEN portion of the code (highlighted).

I'm going on vacation next week and need to have this code finished so my director can run the monthly reports.....HELP! :D

I have verified that the References in VBE are the same, and that the content is enabled on both machines. I've saved the Excel file as a XLSM.

Option Explicit
Sub ADOExcel()
     
    Dim Cn As ADODB.Connection
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    SQLStr = "select case " & _
"when Program = 'Dept_A_' and (AVAILABLE='1' or AVAILABLE is null) and prov='ON' then '01 - Dept_A__DD_ONT' " & _
"when Program = 'Dept_A_' and (AVAILABLE='1' or AVAILABLE is null) and prov<>'ON' then '02 - Dept_A__DD_OOP' " & _
"when Program = 'Dept_A_' and AVAILABLE='0' and prov='ON' then '03 - Dept_A__LD_ONT' " & _
"when Program = 'Dept_A_' and AVAILABLE='0' and prov<>'ON' then '04 - Dept_A__LD_OOP' " & _
"when Program='Dept_B_' AND (AVAILABLE='1' or AVAILABLE is null) AND PROV='ON' then '05 - Dept_B__DD_ONT' " & _
"when Program='Dept_B_' AND (AVAILABLE='1' or AVAILABLE is null) AND PROV<>'ON' then '06 - Dept_B__DD_OOP' " & _
"when Program='Dept_B_' AND AVAILABLE='0' AND PROV='ON' then '07 - Dept_B__LD_ONT' " & _
"when Program='Dept_B_' AND AVAILABLE='0' AND PROV<>'ON' then '08 - Dept_B__LDD_OOP' " & _
"when Program='Dept_C_' AND PROV='ON' then '09 - Dept_C__ONT' " & _
"when Program='Dept_C_' AND PROV<>'ON' and PROV<>'XX' then '10 - Dept_C__OOP' " & _
"when Program='Dept_C_' and PROV='XX' then '11 - Dept_C__INT' " & _
"when Program='Dept_D_' AND PROV='ON' then '12 - Dept_D__ONT' " & _
"when Program='Dept_D_' AND PROV<>'ON' and PROV<>'XX' then '13 - Dept_D__OOP' " & _
"Else Program end as descript, " & _
"count(case when(date_of_Program<=add_months(trunc(sysdate),-1) " & _
"and date_of_Program>add_months(trunc(sysdate),-2))then 1 end) as PrevMonth " & _
"from Program t, programemployees p " & _
"where date_of_Program <= add_months(trunc(sysdate), -1) And date_of_Program > add_months(trunc(sysdate), -2) " & _
"and t.employeeid = p.employeeid " & _
"and last_name<>'TESTPATIENT' " & _
"group by Program, AVAILABLE, prov " & _
"order by Program, AVAILABLE, prov"
     
    Set Cn = New ADODB.Connection
  [B] Cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyDatabase;" & _
           "User Id=MyUserName;" & _        '''''''' real info stripped out for this 
           "Password=MyPassword"            '''''''' post
[/B]
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("sheet1").Range("a1:c50") 
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B5")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[darth.pathos]

I realise of course there are a huge number of threads being posted and that everyone's busy, but I'm hoping someone can help me out with this.

I also thought about removing the User Name and Password from the connection string section of my code, but because I use them to get into a number of databases etc. I cannot give them out to people; that's why I put them right into the string.

Thanks again for any help / ideas / suggestions...........
Chris

 

[RoryA]

Do you have the same client tools installed on all machines? Are you using a DSN?

 

[darth.pathos]

Hi Rorya,

Yes, everything is the same across the machines; admin privileges are very tight here, and I haven't installed anything extra for Excel.

I do have a DSN, but I was told by one of the DBAs here that with Oracle if I'm putting my username and pw in the connection string, it will "just work" (her words, not mine)...obviously this is not correct, given a) me having problems and b) you asking the question.

So, on the assumption that I need to set up my director with a DSN, and with the stipulation that she is unavailable the rest of this week and I'm leaving the country on Saturday, how can I go about getting this accomplished? If this is a completely lost cause I will have to come up with a plan B, but I'm hoping someone out there a whole heap smarter than I am will have a brilliant solution.

Chris

 

[xenou]

I'm not sure that the Oracle Provider is standard issue ... you might want to make sure that the other machine has it. Also, naturally, that the other machine has access to the disk where the database resides (some machines map drives differently, or don't have access to the same network resources).