Open Access DB exclusively and run DoCmd

[doasidont]

From within Excel VBA, how do I open a specific Access database in exclusive mode (to make sure others are not using the database), and then run a DoCmd to run a macro within that database?

I have managed to open the database in exclusive mode by opening an exclusive connection to it, but then the DoCmd errors. There must be either a different way to open the database exclusively so that the DoCmd will run, or a different way to run the DoCmd with the exclusive connection.

Thanks for any help offered.

[taxidev]

Does the database only contain data or are there code modules in it? If it is only holding data and you want to work with that data you can do that directly from within the Excel VBA module.

If you explain what you are trying to accomplish with the DoCmd then maybe I can give a more explicit description.

[doasidont]

Thanks. Here's brief description.

Launch Excel and start a VBA macro that does the following:

1) SFTP data into multiple .txt files.

2) Using 'New ADODB.Connection' (ACE) to database and 'New ADODB.Recordset', copy records from db table to a worksheet that will be used later to clean data.

3) Using 'QueryTables.Add (Connection)', import and append the .txt files into another worksheet.

4) Clean and format the data in the second worksheet.

5) Create .txt files using the cleaned data.

6) Open Access and the database using 'Set oApp = GetObject(, "Access.Application")' and 'oApp.OpenCurrentDatabase db'.

7) 'Switch_Window ("Access")'.

8) Using 'oApp.DoCmd.RunMacro "Daily Import"', start a macro in the db that runs a code module that imports the .txt files to various tables using 'Saved Import Specifications'.

There's more but that's the skinny. I want to prevent the process from continuing to step (1) if anyone else is using the database, and I don't want to do a lot of redesign or recoding. So yes, the db has both data and code modules in it.

Thanks for listening and your help..

[taxidev]

I would approach the project this way:

In the Access file, construct the code so that opening it from a command line with a parameter will tell the location of the new files.

Then, use a shell command from the Excel VBA to open Access with the db, which will automatically run a macro on startup. That macro will call the code that will do all the work on the files in the location specified in the command line parameters.

By doing this you have minimal code changes. You could also make it so that when the code completes it automatically closes the database and shuts down Access.

Based on what you've written I believe you can figure out how to make the code adjustments.

[doasidont]

Thanks, but I don't think I'm getting an answer to my basic question, which is, how do I make sure that no one else has the database open before I start the process. The way the process is written, all of the prep work is done up front through step (7). If someone has the database opened and locked when step (8) kicks in, the process errors because the database can't be updated.

I want to open the database exclusively at the beginning of the process, and if that can't be done because someone else has it, stop the process from continuing and displaying an appropriate message. How do I do that (open exclusively) and still be able to do the DoCmd?

Thanks.

[taxidev]

This is my point - I have never seen how you can open a db from excel vba and run DoCmd. Maybe someone else knows a way, but I've never seen that capability in a stand alone db. In an addon, yes, you can run code, but that still has to be from within another Access db.

But calling Shell() from VBA you can have Access open the database, and based on the command line parameters it will update itself. If someone else has it opened then the call to Shell() will fail and you have your error message pop up.

[vbfbryce]

Unless I'm missing something in what you're asking, I think this would do it:

Sub acc()
    Dim oApp As Object
    
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    oApp.OpenCurrentDatabase "c:\yourPath\vbmain.accdb", True   'change to your path
    oApp.DoCmd.RunMacro "macRanFromExcel"   'name of my macro
    oApp.CloseCurrentDatabase
    Set oApp = Nothing
    
End Sub

[doasidont]

Thanks to both of you. Two things.
1) I've tried vbf's code and, at least in my tests, even though I use the 'True' arg to open exclusive, others can open and modify the database. That shouldn't happen, right?

2) What code do I use to execute step (2) above; i.e., copying a table recordset from the database to a worksheet? I had been using an ADODB.connection.

Thanks again.