"Can't find project or library" error w/ Excel 03, Win2000

[Hueby]

hi all,

I have a excel spreedsheet that pulls data from a MS SQL server on startup. It works just fine with Windows XP Pro and Excel 2003 machines.

Though, when on Windows 2000 and Excel 2003 I get an error. This error is: Compile Error: Can't Find Project or Library. It then debugs into my Dim Cn as ADODB.Connection line.

I don't know if this is a .dll file problem, or what? Any help please!

Below is my code for reference:

Sub ADOExcelSQLServer()
     ' Carl SQL Server Connection
 '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
 ' tried 2.8
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    Server_Name = "HWPC-SBS01" ' Enter your server name here
    Database_Name = "ForeFront" ' Enter your database name here
    User_ID = "sa" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT job_number, job_description, project_manager, CASE price_method_code WHEN 'T' THEN 'T&M' WHEN 'F' THEN 'Lump Sum' else 'Unknown' end, Case earned_calc_type when 'B' then 'Billed' when 'P' then 'Percentage' else 'Unknown' END FROM JC_Job_Master_MC WHERE (company_code = 'HWP' and status_code = 'A') or (company_code = 'UHP' and status_code = 'A') order by job_number" ' Enter your SQL here
     
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

 

[PHV]

check the references:
menu Tools -> References ...
you should tick a Microsoft Active X Data Objects 2.x Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886