DAO and tabledefs 1

[JDRoss]

After exhaustive troubleshooting, I have traced the problem to the DAO. See thread181-557165

I had to convert this database back to 97 format to overcome a password problem, don't ask? It seems that in doing so there is confusion between ADO and DAO. I am now explicitly referencing DAO object, e.g. database, recordset, etc.

But I am getting the old stock reliables, 'Item Not in this Collection' or 'Type Mismatch' when I run the following piece of code.

Dim db As DAO.Database, tdf As TableDef
    Dim fld As Field, rst As DAO.Recordset
    Dim tbl As String

    tbl = Forms!THisForm!cboTable
        ' referneces a table name from a combo 
    Set db = CurrentDb
        
    Set tdf = db.TableDefs(tbl)
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * from TableFields")
    DoCmd.SetWarnings True
    Set rst = db.OpenRecordset("TableFields", dbOpenDynaset)
    ' pass the field names from the table

For Each fld In tdf.Fields

The above line is the one that gives the type mismatch error

        If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
            rst.AddNew
            rst!fieldName = fld.Name
            rst!FieldType = fld.Type
            rst.Update
        End If
    Next fld

    Set db = Nothing
    cboFieldName1.Requery
    cboFieldName2.Requery
    cboFieldName3.Requery

Has anybody a solution to this? I know this code works because users have been using it for months. I know the problem is related to the DAO reference library, is there a work around? Or is there another way to get field names from a table without using the Field type or tabledef?

Much appreciated as always.

John

 

[BillPower]

Hi John,

This will definitely solve the problem:

Dim db As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, rst As DAO.Recordset
Dim tbl As String

You've also got to explicitly reference the TableDef and Field as DAO.

Regards

Bill

 

[JDRoss]

Thanks Billpower

DAOing everything was the answer. It works perfectly now.

I lost a day on this. Thank you so much.

John