CODEWITHSUNDEEP
excelvbams-access
Amal Kumar
Amal Kumar

Reputation: 3

How to define a path of access database in a folder instead of CurrentDB in excel VBA?

I have to run an access query via excel vba user form. This query basically find the record from the access table and updates the Out Time. The Table name is "Visitors" and the query name is "updateVisitors". Below is the Excel VBA code I have written :-

Sub VisitorsUpdateOT()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim con As Object

    Application.EnableCancelKey = xlDisabled
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("updateVisitors")

        qdf.Parameters("Recp_Visitors!OutTime").Value = Recp_Visitors.OutTime.Value
        qdf.Parameters("Recp_Visitors!SrNo").Value = Recp_Visitors.SrNo.Value
        qdf.Execute
        qdf.Close

    Set qdf = Nothing
    Set dbs = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

This code runs perfectly when the access database is open. I figured out that it does not works when the DB is open because of "Set dbs = CurrentDb". Is there any way that I can run this query even when the DB is closed?

Upvotes: 0

Views: 1068

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19225

From here https://sourcedaddy.com/ms-access/opening-external-database.html

Change

Set dbs = CurrentDb

to

Set dbs = DBEngine(0).OpenDatabase("C:\Temp\db1.accdb", False, False)

Upvotes: 1

Related Questions