CODEWITHSUNDEEP
vbams-access
Xiao Qiang
Xiao Qiang

Reputation: 83

How can I use vba to get access to a table in Access without opening the mdb file?

I am trying to add a field using VBA to a table in a mdb file if the field does not exist. If I open the mdb file in Access, and run the VBA code, it works fine. However, if I clode Access, I will encounter 'Error 3265 : Item not found in this collection.' at 'With Access.Application.DBEngine(0)(0).TableDefs("Contract")' stage.

Thanks!

Here is my code:

Sub ResetDB()
Dim nlen As Long

MsgBox ("Select the Access Database using this browse button")
    NewFN = Application.GetOpenFilename(FileFilter:="mdb.Files (*.mdb), *.mdb", Title:="Please select a file")

    If NewFN = False Then
        ' They pressed Cancel
        MsgBox "Try Again if database needs to be reset"
        Application.DisplayAlerts = False
        'ActiveWorkbook.Close
        Application.DisplayAlerts = True
        Exit Sub
    Else
        ActiveWorkbook.Unprotect ("12345")
        Sheets("Version").Visible = True
        Worksheets("Version").Unprotect (strPW)
        Range("Database").Value = NewFN

    'On Error GoTo Failed ' I comment this line just to see where the error is
    ' following line is when the error occurs
        With Access.Application.DBEngine(0)(0).TableDefs("Contract")
        .Fields.Refresh
        nlen = Len(.Fields("Industry_Type").Name)
        If nlen > 0 Then Sheets("Instructions").Range("a1") = 1 ' do nothing
        End
        End With
Failed:
    If Err.Number = 3265 Then Err.Clear ' Error 3265 : Item not found in this collection.
    With Access.Application.DBEngine(0)(0).TableDefs("Contract")
    .Fields.Append .CreateField("Industry_Type", dbLong)
    End With
    End

    End If
End Sub

Upvotes: 0

Views: 866

Answers (1)

E Mett
E Mett

Reputation: 2302

If the Access is closed, you will not be able to work on it.

You must open the MDB file:

Dim db As New Access.Application

db.OpenAccessProject filepath

The use db to retrieve the tables:

db.TableDefs....

Upvotes: 1

Related Questions