Runtime Error '13'

[Frabker3]

I have an Access DB that was originally created in Access 2, then converted to Access '97, and a few years ago converted again, to Access '03.

During the convert to '03 it was determined that DAO reference was incorrect and needed to be updated to 3.6 which we did.

I have a macro that won't run in '03 but will run under '97

When running the macro, the Error generated is
"Run TIme Error '13' Type mismatch"

Function Have_Recs(Table_Name As String)
 
' This function will determine if a table is present and if present
' does it contain any records
' Returns -1 if records are found
' Returns 0 if the table is not present or is present without records
 
Dim listset As Recordset
 
Open_Data_Database
Set listset = db.TableDefs()[/b] (this line is highlited in VBA)
 
Have_Recs = 0
Have_Table = 0
 
listset.MoveFirst
 
While (Not listset.EOF And Have_Table = 0)
   If listset!Name = Table_Name Then
      If (listset!RecordCount > 0) Then
         Have_Recs = -1
      End If
      j = listset!RecordCount
      Have_Table = -1
   End If
   listset.MoveNext
Wend
 
End Function

Does anyone have a clue what I need to do with this?

[ADezii]

@Frabker3
It was easier just to rewrite the code to something simpler and more efficient. The following Function will return True only if a Table exists AND contains at least 1 Record in it:

Public Function Have_Recs(strTableName As String) As Boolean
If strTableName = "" Then Exit Function
 
Dim tdf As TableDef
 
Have_Recs = False           'Initialize to False
 
For Each tdf In CurrentDb.TableDefs
  If tdf.Name = strTableName Then           'Table exists
    If DCount("*", strTableName) > 0 Then   'Exists and has Records, return True!
      Have_Recs = True
        Exit For
    End If
  End If
Next
End Function

[NeoPa]

Can you say which line of your code generated the error message?

Welcome to Bytes!

[Frabker3]

Line 11 of the original post is the offending line. But like everything in programming could be elsewhere as well.

[Frabker3]

@ADezii
Thank You, this works well. And does what the other was supposed to do.

One issue that came up after this fix was run is that when closing the DB Access stays open. Any ideas?

[ADezii]

@Frabker3
Hard to say, but sounds like you have a

 DoCmd.Close 

instead of a

DoCmd.Quit 

somewhere.

[NeoPa]

In your original line #11 you are trying to assign a collection of recordsets (No indexd provided) to a recordset object variable. This is not possible. If you were to add an index however, this would be fine. It can also be done within a looping structure as in :

For Each listset in DB.TableDefs

[Frabker3]

Thank you ADezil and NeoPa for your input. I will give it a shot on Monday or Tuesday.

Frank

[NeoPa]

A pleasure :)

Let us know how you get on.

[MMcCarthy]

I would just mention here that when using DAO in Access you have to explicitly declare the variables as DAO type or they will default to ADO.

So in this case your recordset declaration has to be:

Dim listset As DAO.Recordset

[NeoPa]

Good point Mary. I overlooked that one.