How to run a query that exists in Access from VB

[Bookreader]

I tried Googling this but I get a whole lot of replies about running
the SQL statement in VB via ADO.

All I want to do is run an existing ACCESS 2000 query from VB with no
information returned to VB. I am presuming that I would use
Automation somehow.

Thanks in advance.

[pietlinden]

Hmm... you read the book... how about the manual this time...

Straight outta Compton... no, wait, the help file:

Execute Method (ADO Connection)
Executes the specified query, SQL statement, stored procedure, or
provider-specific text.

Syntax

For a non-row-returning command string:

connection.Exec ute CommandText, RecordsAffected , Options

For a row-returning command string:

Set recordset = connection.Exec ute (CommandText, RecordsAffected ,
Options)

Return Value

Returns a Recordset object reference.

Parameters

CommandText A String value that contains the SQL statement, table
name, stored procedure, a URL, or provider-specific text to execute.

RecordsAffected Optional. A Long variable to which the provider
returns the number of records that the operation affected.

Options Optional. A Long value that indicates how the provider should
evaluate the CommandText argument. Can be one or more CommandTypeEnum
or ExecuteOptionEn um values.

Remarks

Using the Execute method on a Connection object executes whatever query
you pass to the method in the CommandText argument on the specified
connection. If the CommandText argument specifies a row-returning
query, any results that the execution generates are stored in a new
Recordset object. If the command is not a row-returning query, the
provider returns a closed Recordset object.

[Bookreader]

On 9 Jul 2005 19:08:44 -0700, pi********@hotm ail.com wrote:

Hmm... you read the book... how about the manual this time...

Straight outta Compton... no, wait, the help file:

Execute Method (ADO Connection)

Nothing like an answer from a wiseguy that's wrong. Maybe you should
try reading the question.

How do I run a query that EXISTS IN ACCESS 2000 FROM VB?

As I suspected in the original post, it can be done via Automation
using the Access Application object.

Thanks.

[David Schofield]

On Sun, 10 Jul 2005 18:33:50 GMT, Bookreader <bo***********@ yahoo.com>
wrote:

On 9 Jul 2005 19:08:44 -0700, pi********@hotm ail.com wrote:

Hmm... you read the book... how about the manual this time...

Straight outta Compton... no, wait, the help file:

Execute Method (ADO Connection)

Nothing like an answer from a wiseguy that's wrong. Maybe you should
try reading the question.

How do I run a query that EXISTS IN ACCESS 2000 FROM VB?

As I suspected in the original post, it can be done via Automation
using the Access Application object.

Thanks.

Good, now both of you can feel superior to the other and everyone is
happy!
David

[New Guy]

On 10 Jul 2005 15:23:02 -0500, not@here (David Schofield) wrote:

Good, now both of you can feel superior to the other and everyone is
happy!
David

Actually, I knew I shouldn't have even responded to that guy. But, I
did allow the third poster an opportunity to feel superior to both of
us! (hardee har)

I'd still like an exact answer to my original question, if you would
please.

Here's the code I have so far:

Dim objAccess As Access.Applicat ion
Set objAccess = New Access.Applicat ion
'MsgBox objAccess.Repor ts.Count

objAccess.OpenC urrentDatabase "C:\Current Database\new.md b"
objAccess.Visib le = True
objAccess.DoCmd .OpenQuery ("Create_Invoic e_Footer")
objAccess.Run ("Create_Invoic e_Footer")

Are both the OpenQuery and Run lines needed?

Is it possible to get any resulting error message back from Access or
know that the query was successful?

Thanks again.

[New Guy]

On Sun, 10 Jul 2005 20:31:32 GMT, New Guy <Ne****@yahoo.c om> wrote:

I'd still like an exact answer to my original question, if you would
please.

Here's the code I have so far:

Dim objAccess As Access.Applicat ion
Set objAccess = New Access.Applicat ion
'MsgBox objAccess.Repor ts.Count

objAccess.OpenC urrentDatabase "C:\Current Database\new.md b"
objAccess.Visib le = True
objAccess.DoCmd .OpenQuery ("Create_Invoic e_Footer")
objAccess.Run ("Create_Invoic e_Footer")

Are both the OpenQuery and Run lines needed?

Thanks again.

I did find out that I don't need the "objAccess. Run
("Create_Invoic e_Footer")" line.

[David Schofield]

On Sun, 10 Jul 2005 20:43:51 GMT, New Guy <Ne****@yahoo.c om> wrote:

On Sun, 10 Jul 2005 20:31:32 GMT, New Guy <Ne****@yahoo.c om> wrote:

I'd still like an exact answer to my original question, if you would
please.

Here's the code I have so far:

Dim objAccess As Access.Applicat ion
Set objAccess = New Access.Applicat ion
'MsgBox objAccess.Repor ts.Count

objAccess.OpenC urrentDatabase "C:\Current Database\new.md b"
objAccess.Visib le = True
objAccess.DoCmd .OpenQuery ("Create_Invoic e_Footer")
objAccess.Run ("Create_Invoic e_Footer")

Are both the OpenQuery and Run lines needed?

Thanks again.

I did find out that I don't need the "objAccess. Run
("Create_Invoi ce_Footer")" line.

Hi
You put me in an embarrassing position.

Your original post said
"All I want to do is run an existing ACCESS 2000 query from VB with no
information returned to VB"

That implied that you are programming a VB program and want to run an
stored action query (ie insert (append), update or delete) in a JET
(ie Access) database. The way to do this is as PietLinden suggests,
though you could use DAO instead of ADO.

But the example code you since posted suggests you are going to follow
this up with other things in a full Access environment, eg create
reports.In this case automation is certainly indicated.

I suggest you go to
http://support.microsoft.com/default...NoWebContent=1
"Microsoft Office 2000 automation Help file is available in the
Download Center."
the download gives many examples of automating Access 2000.

Can't you do it all in one instance of Access and avoid automation? My
own experiences are it is nothing but trouble, eg it leaves dangling
references and the called aplication doesn't close but sits there
invisibly; any failures are hard to recover from; etc etc. Access 2000
also has many bugs in this area and maybe not all were cured by
service packs. I would prefer to use the shell method if I had to.
David

[New Guy]

On 10 Jul 2005 18:04:02 -0500, not@here (David Schofield) wrote:

I suggest you go to
http://support.microsoft.com/default...NoWebContent=1
"Microsoft Office 2000 automation Help file is available in the
Download Center."
the download gives many examples of automating Access 2000.
Thanks for this link. That's what I really need.
Can't you do it all in one instance of Access and avoid automation? My
own experiences are it is nothing but trouble, eg it leaves dangling
references and the called aplication doesn't close but sits there
invisibly; any failures are hard to recover from; etc etc. Access 2000
also has many bugs in this area and maybe not all were cured by
service packs. I would prefer to use the shell method if I had to.
David

I'm not sure I get your question about one instance of Access.

I just want to have the user click a button in a VB program to run a
query that resides in Access. Where does more than one instance of
Access fit into this?

All I want the user to know is that he has to push the button.

Thanks.

[Paul Clement]

On Sat, 09 Jul 2005 22:52:53 GMT, Bookreader <bo***********@ yahoo.com> wrote:

¤ I tried Googling this but I get a whole lot of replies about running
¤ the SQL statement in VB via ADO.
¤
¤ All I want to do is run an existing ACCESS 2000 query from VB with no
¤ information returned to VB. I am presuming that I would use
¤ Automation somehow.
¤

You can use ADO (Microsoft ActiveX Data Objects 2.x Library) to do this:

Dim strConnectionSt ring As String
Dim objConn As ADODB.Connectio n
Dim objCommand As ADODB.Command

strConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data Source=e:\My
Documents\db1.m db;" & _
"Jet OLEDB:Engine Type=4"

Set objConn = New ADODB.Connectio n
objConn.Open strConnectionSt ring

Set objCommand = New ADODB.Command

objCommand.Acti veConnection = objConn

' Assign to ADO Command object
objCommand.Comm andText = "qdelTable1 0"
objCommand.Comm andType = adCmdStoredProc

objCommand.Exec ute

objConn.Close

Set objCommand = Nothing
Set objConn = Nothing
Paul
~~~~
Microsoft MVP (Visual Basic)

[New Guy]

On Mon, 11 Jul 2005 08:29:57 -0500, Paul Clement
<Us************ ***********@sws pectrum.com> wrote:

On Sat, 09 Jul 2005 22:52:53 GMT, Bookreader <bo***********@ yahoo.com> wrote:

¤ I tried Googling this but I get a whole lot of replies about running
¤ the SQL statement in VB via ADO.
¤
¤ All I want to do is run an existing ACCESS 2000 query from VB with no
¤ information returned to VB. I am presuming that I would use
¤ Automation somehow.
¤

You can use ADO (Microsoft ActiveX Data Objects 2.x Library) to do this:

Dim strConnectionSt ring As String
Dim objConn As ADODB.Connectio n
Dim objCommand As ADODB.Command

strConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data Source=e:\My
Documents\db1. mdb;" & _
"Jet OLEDB:Engine Type=4"

Set objConn = New ADODB.Connectio n
objConn.Open strConnectionSt ring

Set objCommand = New ADODB.Command

objCommand.Acti veConnection = objConn

' Assign to ADO Command object
objCommand.Comm andText = "qdelTable1 0"
objCommand.Comm andType = adCmdStoredProc

objCommand.Exec ute

objConn.Close

Set objCommand = Nothing
Set objConn = Nothing
Paul
~~~~
Microsoft MVP (Visual Basic)

I see. A query existing in ACCESS is a stored procedure.?

Does this return an error message if the statement fails?

Is ADO preferable to the Acess.Applicati on method below. Just for my
education, can you tell me why ADO is better?
Dim objAccess As Access.Applicat ion
Set objAccess = New Access.Applicat ion
'MsgBox objAccess.Repor ts.Count

objAccess.OpenC urrentDatabase "C:\Current Database\new.md b"
objAccess.Visib le = True
objAccess.DoCmd .OpenQuery ("Create_Invoic e_Footer")
objAccess.Run ("Create_Invoic e_Footer")
Thanks.