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

Question

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?

You have to have an open connection to get data, but it doesn't have to be currentDb. To run it from Excel you will need to get that connection a different way. — braX
– braX, Commented Nov 11, 2021 at 10:40

Nick.Mc · Accepted Answer · 2021-11-11 11:35:29Z

1

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)

edited Nov 11, 2021 at 11:35

answered Nov 11, 2021 at 10:55

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

braX Over a year ago

Excel does not know what DBEngine is.

Amal Kumar Over a year ago

It throws error saying MS Access DB cannot open or write to the file "DB Path". It is already opened exclusively by another user.

Nick.Mc Over a year ago

Have you got the MS Access file open? If you've been messing with VBA, it's possible it's still open in an orphan process.

Amal Kumar Over a year ago

Hi Nick, you are right. I changed the code as below Set dbs = DBEngine(0).OpenDatabase("DB Path", , False, True) Thanks a ton everyone... :)

Nick.Mc Over a year ago

feel free to accept as the correct anser

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related