How to execute a DoCmd from within a connection state.

[sumdumgai]

If the question makes no sense, I'm sorry but I'm trying to do something with Excel and Access.
From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection
With connDB
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeShareExclusive
.Open strdbpath 'path to database
End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table
If connDB.State = adStateOpen Then
Set objRS = New ADODB.Recordset
objRS.Open strSQL, connDB, adOpenForwardOnly
On Error Resume Next
objRS.MoveFirst
If Err.Number = 0 Then
On Error GoTo 0
fieldCnt = objRS.Fields.Count
For fieldNum = 0 To fieldCnt - 1
ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name
Next fieldNum
ws.Cells(2, 1).CopyFromRecordset objRS
End If
End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"

Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message. Can someone please help?

Thanks.

 

[sumdumgai]

Haven't had any responses to this so I thought I'd rephrase my question. 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 is either a different way to open the database exclusively so that the DoCmd will run, or there is a different way to run the DoCmd with the exclusive connection.

Thanks for any help offered.

 

[MarkK]

If you connect to ACE/Jet tables using ADODB you have not opened Access, and DoCmd is provided by Access. Access is the file at "C:\Program Files\Microsoft Office\OfficeXX\MSACCESS.EXE" and that is not the same as the database you connect to with ADO at C:\yourdocs\data.mdb.
You can open Access from Excel like this . . .

dim app as object
set app = createobject("Access.Application")
app.opendatabase("yourdatabasefile.mdb")
app.docmd. [COLOR="Green"]'now you can use the DoCmd object, but it's a member of Access.Application[/COLOR]

. . . but what I would want to do instead is see if I can execute my DoCmd process using ADO instead, because DoCmd will always require that you actually open MSACCESS.EXE.

 

[sumdumgai]

Thanks for reply. If I execute my DoCmd using ADO, how do I open the database exclusively? I've tried opencurrentdatabase (db, true), but it opens the database even while others have it open.

 

[MarkK]

Sorry if I wasn't clear. I would move the process out of DoCmd, out of Access, into ADO. DoCmd is not available unless you open Access. DoCmd is not available using ADO.

 

[sumdumgai]

That's my question. I want to open Access and the database (exclusively) from Excel, switch to Access and start a macro within the database via DoCmd. The database has both data and code modules. I don't want the process to continue if anyone else is using the database.

 

[pr2-eugin]

You can check if the lock file exists in the folder where the Access file resides. If it exists, then people are using the DB, else it is free for taking. A simple trick ! Not sure if that will work out for you.

 

[sumdumgai]

Thanks. I'll try that.

 

[MarkK]

Check the parameters for the OpenCurrentDatabase method of the Access.Application object, which is how you would open a Db for automation from Excel. There is an optional boolean parameter called "Exclusive."