ADO Execute stored procedure, Strange behavior

[timothyl]

O.k this seems bazaar to me, I have the below code were qryADO is a query like so: SELECT * parse(field1) then make table. I made the query in GUI so works fine. When I execute the code below it takes 2 minutes for the table to show up, but if I go to deign mode the table shows up immediately , any ideas on what going on and how to get the table to show up with out going into design mode. Thanks


Private Sub Command51_DblClick(Cancel As Integer)

Dim Cmd As ADODB.Command
Dim pSQL As String

Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = CurrentProject.Connection


Cmd.CommandText = "qryADO"
Cmd.CommandType = adCmdStoredProc
Cmd.Execute

Set Cmd = Nothing

End Sub

 

[boblarson]

Are you working with an ADP?

 

[timothyl]

Hello boblarson, Bob I don't think so, I just googled ADP to see what it means, if I understood it correctly then no. Thanks

 

[boblarson]

Then you really need not use ADO to execute a query.

To execute a query in the current database, use:

CurrentDb.Execute "qryADO", dbFailOnError

Also, your post seems to indicate you are running a "make table" query. Is there a reason why you can't just use a query where the table would be used? That way you avoid database bloat and uneccessary object creation.

 

[timothyl]

boblarson, partially I am confused by recordsets and ado, so mostly I'm trying to straighten out my understanding of them by writing some code that is useful to me in ado. I have a ton of on going data to scrub imported from a acrobat program and I am automating it by writing vba code to do so, I got to a part were I needed to parse a field with a select query and thought with my limited understanding I could so in ado, i.e that my select query could update the table directly. I think there must be a way to do this with recordsets but I can not figure it out, so your write in the end I parse then make table so I now have a action query and can run it in vba. I will use your code above, thanks for it. Tim

 

[boblarson]

1. A select statement doesn't do anything for the data in the table. It only displays it.

2. If qryADO is not an update, append, or delete query you cannot run it like I said either. It needs to be an action query.

VBA recordsets are good at times but for bulk operations, queries are still the best, most efficient, way to go.

 

[boblarson]

Oh, and it is pretty much best to use DAO for dealing with things inside the database as it works with JET, the internal working engine of Access. ADO is best used for dealing with things like SQL Server, or outside things. There are several DAO vs. ADO threads here which you might look up.

 

[timothyl]

Thanks for those comments I did not now that about ADO and DAO, one of the books I am reading implied DAO was a dying technology, not supported by Microsoft anymore and not worth learning because of this, I will study up on DAO. Thanks again,Tim

 

[boblarson]

Thanks for those comments I did not now that about ADO and DAO, one of the books I am reading implied DAO was a dying technology, not supported by Microsoft anymore and not worth learning because of this, I will study up on DAO. Thanks again,Tim

Must be an old book because there was talk of that happening back when Access 2000 came out, but the developer community was very vocal in that this couldn't happen and in fact in Access 2003, DAO was again made the default (with ADO) and in 2007 DAO is the default. DAO is definitely not going anywhere anytime soon.

 

[timothyl]

Yes, kind of old, access 2002 with VBA, other then that prediction not coming true a good book, regards Tim