Member Avatar for coalminer17
coalminer17 0 Newbie Poster

I am updating an Access 97 DB w/ VBA code to Access 2007. The VBA code uses DAO objects that Access 2007 references when I converted the db files (first from 97 to 2002, then to 2007). My problem specifically is with the two references: "Microsoft DAO 3.6 Object Library" and "Microsoft Office 12.0 Access database engine Object Library." The stuff I've read on the web says that the ADE 12.0 is supposed to supercede the DAO 3.6 reference, and that by just checking the ADE 12.0 box everything should work. Since they both provide references for objects of the same name, these two references cannot be checked at the same time. However, when check the DAO 3.6 box, and leave the ADE 12.0 box unchecked, Access can't find a "frmLogin" form that exists. If I uncheck the DAO 3.6 box and check the ADE 12.0 box, I get a run-time error 13: "type mismatch" for a Visual Basic "Err" object:

Public Function basGetString(ByVal lngStringID As Long, ParamArray varStringArgs() _ 
      As Variant) As String 
     Dim intTokenCount As Integer 
     Dim strResString As String 
 
On Error GoTo err_basGetString 
 
    strResString = basLoadString(lngStringID) 
 
    If Not IsMissing(varStringArgs) Then 
        intTokenCount = 0 
 
         Do While intTokenCount <= UBound(varStringArgs) 
              strResString = basReplaceToken(strResString, _ 
              m_cstrArgToken, varStringArgs (LBound(varStringArgs) + intTokenCount),_    
              1, 1) 
 
              intTokenCount = intTokenCount + 1 
         Loop 
    End If 
 
    strResString = basReplaceToken(strResString, m_cstrVBCRLFToken, vbCrLf, -1, 1) 
    basGetString = strResString 
 
 exit_basGetString: 
      Exit Function 
 err_basGetString: 
      Err.Raise Err.Number, "basGetString", Err.Description 
 
 End Function

The debugger points the the second-to-last line:

Err.Raise Err.Number, "basGetString", Err.Description

I have been successful disambiguating recordset objects, which resolved many of the other referencing conflicts in the code: Dim rs As DAO.Recordset or Dim rs As ADODB.Recordset, but this hasn't worked with the Err object. -That is, I've tried to rewrite the Err object as a DAO Error object. I also have not been able to find another reference in the VB editor that resolves this referencing error, but doesn't also conflict with the other ADE 12.0 reference. So, I don't understand why checking the ADE 12.0 box doesn't solve all my problems. And I'm having trouble figuring out how to rewrite the code so that it references the ADE 12.0 library instead. Lastly, the ADE 12.0 reference is really pointing to the ACEDAO.DLL in the following folder: C:\Program Files\Common Files\Microsoft Shared\OFFICE12. Any suggestions would be really appreciated.

  • 1 Contributor
  • 0 Replies
  • 111 Views

Dani AI

Generated

— library collisions between the legacy DAO runtime and the ACE/Access Database Engine are the usual culprit here. When two referenced libraries expose identically named types or members, the VBA project will resolve unqualified names according to reference precedence (and sometimes by shadowing), which explains why some constructs suddenly fail after switching references.

Practical checklist to resolve it:

  • Open the VBE References dialog and clear any "MISSING:" entries. Broken references can cause wildly misleading errors.
  • Remove the old DAO reference and keep the ACE/Access Database Engine reference for an Access 2007 accdb project. After changing references, use Debug → Compile to force the first compile-time error; fix errors in order.
  • Search the entire project for any declarations or modules that might shadow the VBA error object (for example Dim Err, Public Err, or a module/property named Err). Rename any such identifiers — shadowing the built-in error object commonly produces a type mismatch on Raise.
  • Fully qualify ambiguous types (e.g., DAO.Recordset, ADODB.Recordset) throughout the code so the correct library is always used.
  • Where the built-in error object is ambiguous, qualify it explicitly to the VBA library. Example:
    VBA.Err.Raise 5, "ModuleName", "descriptive message"

If odd form-resolution or other inexplicable behavior persists after fixing references and shadowing, import all objects into a new blank Access 2007 database (External Data → Access → Import) and recompile there. That often clears stale compiled state and hidden reference baggage. Always keep a backup before making these changes.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.