help with function

[sparkbyte]

I have a function that deletes all ODBC linked tables ina BD. It work fine in an MDB, but when copied to an ACCDB it is failing wit the error message.

"Expected user-defined type, not project"

what is wronge? I have check both DB's and the referrences are the same.

Public Function DeleteODBCTableNames(Optional stLocalTableName As String)
On Error GoTo Err_DeleteODBCTableNames

Dim [highlight]dbs As Database[/highlight], tdf As TableDef, i As Integer
Set dbs = CurrentDb

If Len(stLocalTableName) = 0 Then
    For i = dbs.TableDefs.Count - 1 To 0 Step -1
        Set tdf = dbs.TableDefs(i)
            If (tdf.Attributes And dbAttachedODBC) Then
            dbs.TableDefs.Delete (tdf.Name)
            End If
    Next i
Else
    dbs.TableDefs.Delete (stLocalTableName)
End If

DeleteODBCTableNames = True

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Function

Err_DeleteODBCTableNames:
DeleteODBCTableNames = False

MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames
End Function

Thanks

John Fuhrman

 

[PHV]

You have to add a reference to either:
Microsoft DAO
or
Microsoft Data Access Components

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sparkbyte]

Had a reference to DAO 3.6 so i added MS DAC also. Still errors out with the same message.

Thanks

John Fuhrman

 

[traingamer]

What if you use

Dim dbs As [blue]DAO.[/blue]Database, tdf As [blue]DAO.[/blue]TableDef, i As Integer

rather than

Dim dbs As Database, tdf As TableDef, i As Integer

?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[kjv1611]

Yes, use DAO.Database, etc... not w/o the DAO... best way going forward. Also, beginning in '07 or '10, you no longer have to set a reference to MS DAO 3.6 or whatever number. It's done for you automatically. Matter of fact, I've run into issues where I tried to add that reference in '10.. it's now built into the main system structure, I suppose. When I don't set the reference, it just works - with DAO.whatever .... when I do set the reference, it starts acting up.