CODEWITHSUNDEEP
databasevbaexcelms-access
2562166
2562166

Reputation: 17

DMax function not working when MS Access is not open

I am writing a code in VBA that allows for a new user to be added in the database using a user form in MS Excel. The database must be automatically updated once the form is submitted. I am using the DMax function to get the highest existing primary key in the table. I then increment it to get the primary key for the new user to be added. The code works perfectly when the database is open in MS Access. However, it returns the error "Sub or Function not defined." whenever MS Access is not open. Here is the code that is highlighted when the error appears:

userID = DMax("[UserID]", "subscriber_user") + 1

userID is my variable, UserID is the column containing the primary keys, and subscriber_user is the name of the table in the database.

Is it necessary for the database to be open using MS Access for it to work? Is there another method to make sure that the database gets updated without having it to open it via MS Access or any other IDE?

Upvotes: 1

Views: 745

Answers (1)

Mark Butler
Mark Butler

Reputation: 895

DMax for a database is an Access VBA function, not an Excel VBA function.

You'll need to either connect via DAO/ADO, or create an Access object like this:

Public Sub AccessDmax()

    Dim userID as Integer
    Dim oAccess As Object
    Set oAccess = CreateObject("Access.Application")

    oAccess.Visible = False
    oAccess.OpenCurrentDatabase "C:\test_databases\test.accdb"

    userID = oAccess.DMax("ID", "subscriber_user")

    oAccess.Quit
    Set oAccess = Nothing

End Sub

Upvotes: 1

Related Questions