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.Execute CommandText, RecordsAffected, Options

For a row-returning command string:

Set recordset = connection.Execute (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 ExecuteOptionEnum 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********@hotmail.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********@hotmail.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.Application
Set objAccess = New Access.Application
'MsgBox objAccess.Reports.Count

objAccess.OpenCurrentDatabase "C:\Current Database\new.mdb"
objAccess.Visible = True
objAccess.DoCmd.OpenQuery ("Create_Invoice_Footer")
objAccess.Run ("Create_Invoice_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.com> 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.Application
Set objAccess = New Access.Application
'MsgBox objAccess.Reports.Count

objAccess.OpenCurrentDatabase "C:\Current Database\new.mdb"
objAccess.Visible = True
objAccess.DoCmd.OpenQuery ("Create_Invoice_Footer")
objAccess.Run ("Create_Invoice_Footer")

Are both the OpenQuery and Run lines needed?

Thanks again.

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

[David Schofield]

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

On Sun, 10 Jul 2005 20:31:32 GMT, New Guy <Ne****@yahoo.com> 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.Application
Set objAccess = New Access.Application
'MsgBox objAccess.Reports.Count

objAccess.OpenCurrentDatabase "C:\Current Database\new.mdb"
objAccess.Visible = True
objAccess.DoCmd.OpenQuery ("Create_Invoice_Footer")
objAccess.Run ("Create_Invoice_Footer")

Are both the OpenQuery and Run lines needed?

Thanks again.

I did find out that I don't need the "objAccess.Run
("Create_Invoice_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 strConnectionString As String
Dim objConn As ADODB.Connection
Dim objCommand As ADODB.Command

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=e:\My
Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4"

Set objConn = New ADODB.Connection
objConn.Open strConnectionString

Set objCommand = New ADODB.Command

objCommand.ActiveConnection = objConn

' Assign to ADO Command object
objCommand.CommandText = "qdelTable10"
objCommand.CommandType = adCmdStoredProc

objCommand.Execute

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***********************@swspectrum.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 strConnectionString As String
Dim objConn As ADODB.Connection
Dim objCommand As ADODB.Command

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=e:\My
Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4"

Set objConn = New ADODB.Connection
objConn.Open strConnectionString

Set objCommand = New ADODB.Command

objCommand.ActiveConnection = objConn

' Assign to ADO Command object
objCommand.CommandText = "qdelTable10"
objCommand.CommandType = adCmdStoredProc

objCommand.Execute

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.Application method below. Just for my
education, can you tell me why ADO is better?
Dim objAccess As Access.Application
Set objAccess = New Access.Application
'MsgBox objAccess.Reports.Count

objAccess.OpenCurrentDatabase "C:\Current Database\new.mdb"
objAccess.Visible = True
objAccess.DoCmd.OpenQuery ("Create_Invoice_Footer")
objAccess.Run ("Create_Invoice_Footer")
Thanks.

[Orion]

On Mon, 11 Jul 2005 22:59:59 GMT, New Guy <Ne****@yahoo.com> wrote:

Is ADO preferable to the Acess.Application method below. Just for my
education, can you tell me why ADO is better?

Hi
I suggested several reasons in my last post. ADO will also be a lot
quicker. You would only use the automation method if you wanted to do
access-specific things like run a report as opposed to just using the
data.
If you hadn't been so rude to pietlinden you might have found this out
earlier.

David

[Paul Clement]

On Mon, 11 Jul 2005 22:59:59 GMT, New Guy <Ne****@yahoo.com> wrote:

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

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

It's about as close as you can get in Access.

¤ Does this return an error message if the statement fails?
¤

Yes, a run time error is generated if the statement fails to execute so you should have error
handling code to take care of it.

¤ Is ADO preferable to the Acess.Application method below. Just for my
¤ education, can you tell me why ADO is better?
¤ Dim objAccess As Access.Application
¤ Set objAccess = New Access.Application
¤ 'MsgBox objAccess.Reports.Count
¤
¤ objAccess.OpenCurrentDatabase "C:\Current Database\new.mdb"
¤ objAccess.Visible = True
¤ objAccess.DoCmd.OpenQuery ("Create_Invoice_Footer")
¤ objAccess.Run ("Create_Invoice_Footer")

ADO is preferable to Access automation. First, ADO is more efficient and second if you use Access
automation then the Access application must be installed to run the code.
Paul
~~~~
Microsoft MVP (Visual Basic)

[BillCo]

am i missing something or could you just use
DoCmd.OpenQuery "qryYourQueryName"