type mismatch

[jasn_78]

hey guys when i run the following code i get a type mismatch error and i cant c y any help would be nice

Option Compare Database
Option Explicit

      '***************************************************************
      'The DoesTblExist function validates the existence of a TableDef
      'object in the current database. The result determines if an
      'object should be appended or its Connect property refreshed.
      '***************************************************************

      Function DoesTblExist(strTblName As String) As Boolean
         On Error Resume Next
         Dim db As Database, tbl As TableDef
         Set db = CurrentDb
         Set tbl = db.TableDefs(strTblName)
         If Err.Number = 3265 Then   ' Item not found.
            DoesTblExist = False
            Exit Function
         End If
         DoesTblExist = True
      End Function

      Function CreateODBCLinkedTables() As Boolean
         On Error GoTo CreateODBCLinkedTables_Err
         Dim strTblName As String, strConn As String
         Dim db As Database, rs As Recordset, tbl As TableDef
         
         
         
         ' ---------------------------------------------
         ' Register ODBC database(s)
         ' ---------------------------------------------
         Set db = CurrentDb
         Set rs = db.OpenRecordset("tblODBCDataSources")
        
        
         With rs
            While Not .EOF
                DBEngine.RegisterDatabase rs("DSN"), _
                        "Firebird/InterBase(r) driver", _
                        True, _
                        "Description=" & ("FUTUREIB") & _
                        Chr(13) & "Server=" & ("127.0.0.1:") & _
                        Chr(13) & "Database=" & rs("SERVER") & _
                        Chr(13) & "UID=" & rs("UID") & _
                        Chr(13) & "PWD=" & rs("PWD")
            
               ' ---------------------------------------------
               ' Link table
               ' ---------------------------------------------
               strTblName = rs("LocalTableName")
               strConn = "ODBC;"
               strConn = strConn & "DSN=" & rs("DSN") & ";"
               strConn = strConn & "APP=Microsoft Access;"
               strConn = strConn & "DATABASE=" & rs("SERVER") & ";"
               strConn = strConn & "UID=" & rs("UID") & ";"
               strConn = strConn & "PWD=" & rs("PWD") & ";"
               strConn = strConn & "TABLE=" & rs("ODBCTableName")
           
               If (DoesTblExist(strTblName) = False) Then
                  Set tbl = db.CreateTableDef(strTblName, _
                                dbAttachSavePWD, rs("ODBCTableName"), _
                                strConn)
                  db.TableDefs.Append tbl
                  
                  
               Else
                  Set tbl = db.TableDefs(strTblName)
                  tbl.Connect = strConn
                  tbl.RefreshLink
               End If

               rs.MoveNext
            Wend
         End With
         CreateODBCLinkedTables = True
         MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
         Exit Function
CreateODBCLinkedTables_Err:
         MsgBox Err.DESCRIPTION, vbCritical, "MyApp"
         Resume CreateODBCLinkedTables_End
      End Function

 

[jasn_78]

btw i have found what has caused it. i movied from an mdb file to an accdb file format now why has this caused this problem and how do i fix it?

 

[RuralGuy]

Disambiguate all of your libraries.
Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef etc.

 

[jasn_78]

thanks rural guy so so y does access 2007 format need that when i had it in an mdb it didnt?

 

[RuralGuy]

If you check your references you will probably find that ActiveX (ADO) is above the DAO reference.