Controlling Access from Excel
 

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

Controlling Access from Excel
 

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 L Brown
------------------------------------------------------------------------
Gary L Brown's Profile: http://www.excelforum.com/member.php...o&userid=28563
View this thread: http://www.excelforum.com/showthread...hreadid=484324

Controlling Access from Excel
 

Cheers Dude,

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

Ta.

Neil

"Gary L Brown" wrote:


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 L Brown
------------------------------------------------------------------------
Gary L Brown's Profile: http://www.excelforum.com/member.php...o&userid=28563
View this thread: http://www.excelforum.com/showthread...hreadid=484324

Controlling Access from Excel
 

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"
wrote in message
news:Gary.L.Brown.1ycfxa_1131731409.0439@excelforu m-nospam.com...

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 L Brown
------------------------------------------------------------------------
Gary L Brown's Profile:
http://www.excelforum.com/member.php...o&userid=28563
View this thread: http://www.excelforum.com/showthread...hreadid=484324

Controlling Access from Excel
 

do you have

.OpenCurrentDatabase . . .

with a period in front of OpencurrentDatabase?

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:%_odf.11888$0l5.5789@dukeread06...
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"
wrote in message
news:Gary.L.Brown.1ycfxa_1131731409.0439@excelforu m-nospam.com...

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 L Brown
------------------------------------------------------------------------
Gary L Brown's Profile:
http://www.excelforum.com/member.php...o&userid=28563
View this thread:
http://www.excelforum.com/showthread...hreadid=484324

Controlling Access from Excel
 

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

"Tom Ogilvy" wrote in message
...
do you have

.OpenCurrentDatabase . . .

with a period in front of OpencurrentDatabase?

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:%_odf.11888$0l5.5789@dukeread06...
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"

wrote in message
news:Gary.L.Brown.1ycfxa_1131731409.0439@excelforu m-nospam.com...

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 L Brown
------------------------------------------------------------------------
Gary L Brown's Profile:
http://www.excelforum.com/member.php...o&userid=28563
View this thread:
http://www.excelforum.com/showthread...hreadid=484324

Controlling Access from Excel
 

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

Changing back to
..OpenCurrentDatabase
gets it working..


"Jim May" wrote in message
news:BVrdf.11899$0l5.1108@dukeread06...
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

"Tom Ogilvy" wrote in message
...
do you have

.OpenCurrentDatabase . . .

with a period in front of OpencurrentDatabase?

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:%_odf.11888$0l5.5789@dukeread06...
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"

wrote in message
news:Gary.L.Brown.1ycfxa_1131731409.0439@excelforu m-nospam.com...

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 L Brown
------------------------------------------------------------------------
Gary L Brown's Profile:
http://www.excelforum.com/member.php...o&userid=28563
View this thread:
http://www.excelforum.com/showthread...hreadid=484324