Controlling Access from Excel

[Guest]

Hi,

I've been reading up on controlling various Office applications from other
Office Applications.

Right now, I'm trying to control Access from Excel and wondered if anyone
could tell me how to run a DoCmd statement on Access from Excel

Here's what I have so far.

Sub ControlAccessFromExcel()

Set objAccess = CreateObject("Access.Application")
Set objDatabase = objAccess.Database.Open("[access db filepath]")

objDatabase.DoCmd.RunSQL "SELECT...."

End Sub

Now I'm pretty sure that the word "Database" in the line
objAccess.Database.Open("[access db
filepath]")
is wrong. Does anyone know the correct syntax for this or if I'm
completely barking up the wrong tree.

Secondly, is the syntax for the line....
objDatabase.DoCmd.RunSQL "SELECT...."
..........correct?

Ta

Neil

 

[Gary L Brown]

Try something like...

Sub OpenAccess()
Dim objAccess As Object

On Error GoTo err_Sub

Set objAccess = CreateObject("Access.Application")

With objAccess
..OpenCurrentDatabase filepath:="G:\Temp\ReestablishLinks.mdb"
..Visible = True
..DoCmd.RunSQL "Select..."
End With

MsgBox ""



exit_Sub:
On Error Resume Next
Set objAccess = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - " & Err.Description
GoTo exit_Sub

End Sub
---------------------
where...
..DoCmd.RunSQL "Select..."
is fine as long as you are doing an action make-table query.

HTH,
Gary Brown
gary_brown@ge_NOSPAM.com

 

[Guest]

Cheers Dude,

That's exactly what I was after, I'll try it later.

Ta.

Neil

 

[Jim May]

Gary nice code; I'm trying it out here
and am currently getting:
Compile Error
Sub Function not defined
where
OpenCurrentDatabase << is highlighted
What can/should I do to move forward here?

TIA,
Jim

"Gary L Brown" <[email protected]>
wrote in message
news:[email protected]...

 

[Tom Ogilvy]

do you have

.OpenCurrentDatabase . . .

with a period in front of OpencurrentDatabase?

 

[Jim May]

Thanks Tom,
Your right it was omitted from the code I copied;
including the .Visible = True Which I changed..

I had previously commented out the line:
DoCmd.RunSQL "Select..." ' Just want to Open Northwind only - No action to
be taken..

But now I'm getting:
Error: 438 - Object doesn't support this property or method

Any ideas?
Jim

 

[Jim May]

Never mind, Tom
I had changed:
..OpenCurrentDatabase
to
..OpenDatabase

Changing back to
..OpenCurrentDatabase
gets it working..