ado Cmd.Execute ignoring sql statement

[timhans]

My apologies for posting so much, but I am making a big push to get this stuff and I am finding it confusing. So I can know execute a update and insert sql statement, but not a select statement that parse's my table
I don't get a error it just is completely ignored, the sql statement runs in the GUI and as DoCmd.Run SQL, but here it is just ignored!. If I run pSQL as a stored procedure and make table it works but takes about 2 minutes for the table to show up. I am befuddled by all of this, if you can help me understand what's going on I would appreciate it, thanks

Here is my code ( in the parse statement the ',' is calling out the delimiter)

Dim Cmd As ADODB.Command
Dim pSQL As String
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = CurrentProject.Connection

pSQL = "SELECT tblCustomersFinished.CustomerID, tblCustomersFinished.FirstName, tblCustomersFinished.LastName, tblCustomersFinished.Address, tblCustomersFinished.Apt, tblCustomersFinished.City, tblCustomersFinished.State, tblCustomersFinished.Zip, tblCustomersFinished.Phone, tblCustomersFinished.Contact, tblCustomersFinished.Physician, tblCustomersFinished.DOB, tblCustomersFinished.Model, tblCustomersFinished.SerialNumber, tblCustomersFinished.Insurance, Parse([Diagnosis],1,',') AS Diagnosis1, Parse([Diagnosis],2,',') AS Diagnosis2, Parse([Diagnosis],3,',') AS Diagnosis3, Parse([Diagnosis],4,',') AS Diagnosis4 FROM tblCustomersFinished"


Cmd.CommandText = pSQL
Cmd.CommandType = adCmdText

Cmd.Execute RecordsAffected:=recs, options:=adExecuteNoRecords
Set Cmd = Nothing
End Sub

 

[RoyVidar]

You can't execute a select. A select is used to retrieve a recordst.

adexecutenorecords means no recordset is returned, so drop that if you want a recordset, and you need to assign it to a recordset variable:

[tt]dim rs as adodb.recordset
set rs = cmd.execute[/tt]

But I'm not sure it'l recognize your Parse, function.

This will get you a readonly, forwardonly recordset, though.

BTW - if you're working with Jet data, the recommandation is to use DAO for data manipulation.

A bit of information about what you're trying to achieve, would perhaps help us help you...

Roy-Vidar

 

[timhans]

Hello royVidar thanks for responding, what I a'm trying to archive are a couple of things, one was to get a basic understandind of ADO, clearly have a ways to go and secondly I have a mountain of data to scrub on an on going basis imported form a Acrobat program and am trying to automate the process and part of that automation is to execute that parse function.
It did execute it as a stored procedure along with make table but took minutes for the table to show up. Have my automation almost worked out and do not need ADO for it except that select statement. I'm open to another way to execute my parsing function, it comes at the end of a sires of insert and update statements, do you know is it common when executing a stored procedur fo it to take so long to run. Thanks

 

[timhans]

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(using access 2007). 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