Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Question Unanswered: Creating ODBCDirect workspace in Office 2007 VBA

    I have VBA code created in Office Excel 2003 used to make a connection to MySQL. However the code receives an error when ran in 2007 Office Excel. Run-error '3633': Cannot load DLL: '?????L?'

    I’ve searched everywhere for this solution but have not found one. I need help! Here’s my code:

    Code:
    Public Function GetPartNumbers() As Variant
    
        '--- Database variables
        Dim wrk As Workspace, cnn As Connection, rst As Recordset
        Dim strConnect As String
        Dim strSQL As String                'SQL statement to send to db
        
        Dim intRecordNumber As Integer      'Counter to cycle through recordset
        
        Dim aryPartNumbers() As Variant     'Array to hold the PO amount and due date to return
        
        DBEngine.DefaultType = dbUseODBC
        
        '--- Create ODBCDirect workspace.
        Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "EReports", "")
        '--- Create connect string.
        strConnect = "ODBC;DSN=EReports;DATABASE=ESystems;UID=EReports;;"
        '--- Open connection.
        Set cnn = wrk.OpenConnection("EReports", dbDriverNoPrompt, False, strConnect)
        
        '--- Create SQL string to retrieve the current list of part numbers
        strSQL = "SELECT ICM.PartNumber FROM InventoryComponentMatrix ICM ORDER BY ICM.PartNumber ASC"
        
        '--- Open recordset on connection with SQL string.
        Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset)
        
        '--- Dimension the array to hold the data to return based on the size of the recordset
        ReDim aryPartNumbers((rst.RecordCount - 1))
        
        '--- Cycle through all records of the record set
        For intRecordNumber = 0 To (rst.RecordCount - 1)
            
            '--- Put the dollar amount of the line item into the array
            aryPartNumbers(intRecordNumber) = rst!PartNumber
            
            '--- Go to the next record in the record set
            rst.MoveNext
        Next
        
        '--- Close the recordset and connection when finished
        rst.Close
        cnn.Close
        wrk.Close
        
        '--- Set the value to return the array
        GetPartNumbers = aryPartNumbers
    
    End Function
    It always errors here:

    Code:
        '--- Create ODBCDirect workspace.
        Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "EReports", "")
    Are there some changes in 2007 Excel that I should account for. I noticed that 2003 has the 11.0 Object Library while 2007 has 12.0 Object Library. Is that an issue? I would have thought that 12 would be backwards compatible with 11.

  2. #2
    Join Date
    Apr 2005
    Posts
    1

    Did this every get resolved.

    Hi I am having the same issue?

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    I resolved this issue. There is a .dll available in excel 2003 that is not loaded in excel 2007. I figured this out because on one Laptop I bought brand with office 2007 installed it gave me the error. But on another PC which I had 2003 install then installed 2007 I did not have the issue. 2007 does not load the same dlls as previous version. The code I had required this dll. So I simply just had to add the dll to my new laptop.

    Unfortunately I can't remember the dll I installed, but I know it start with ms*.dll search msdn that is where I found it. If I happen to remember it I'll post it here.

  4. #4
    Join Date
    Feb 2009
    Posts
    1
    Hi,

    I have add the same problem with Excel 2007 and got it fixed by copying the dll file MSRDO20.DLL from old machine with Excel 2003 into \Windows\System32 and referring the same in the project reference.

    Cheers

    Sarav

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •