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]

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?

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]

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

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]

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?

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.