ADO problem with a query

[Anonymous]

I've been using DAO succesfully for a while now, but I wanted to check if ADO will be a bit quicker at this new project I'm workign on, so I gave it a shot. It works fine if I want to get the whole table, but as soon as I plug into it SQL Query it returns nothing 😞 This is the same query I use in DAO and it works fine, but no working in ADO, can someone clue me in?

Sub testADO()
Dim objCnn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objCmd As New ADODB.Command
Dim qry As String
Dim category As String
category = "CONDUIT"
objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\Shared Data\AutoCad Support\BOM DATABASE\Stock Items.mdb;Persist Security Info=False"
qry = "SELECT [Old Ref Numbers].[Ref Number], [Stock Items Combined].Description, [Stock Items Combined].[Material Number], [Stock Items Combined].Type FROM [Old Ref Numbers] RIGHT JOIN [Stock Items Combined] ON [Old Ref Numbers].[Material Number] = [Stock Items Combined].[Material Number]WHERE ((([Stock Items Combined].Description) Like ""*" & itemType & "*"" And ([Stock Items Combined].Description) Like """ & listFilter & """) AND (([Stock Items Combined].Type)=""" & category & """))ORDER BY [Stock Items Combined].Description;"
objRs.Open qry, objCnn, adOpenForwardOnly, adLockReadOnly, adCmdText
With objRs
Do Until .EOF
MsgBox .Fields(0)
.MoveNext
Loop
End With

End Sub

Thanks.

[Anonymous]

I don't use ADO a lot but I think you may need "objRs.MoveFirst" before you
enter your "With objRs" loop. Maybe you are already at EOF?

HTH,

Gary

[Anonymous]

Thanks, but EOF and BOF are both true and movefirst is not working.

[Anonymous]

I've not used dao and only a little ado but
SELECT [Old Ref Numbers].[Ref Number] syntax is not familiar to me
in ado I would use "Select fldName, fldName2, etc From TableName Where ....
is Old Ref Numbers a table name?
and RefNumber a field in that table?
I've seen TableName!fieldName but not TableName.FieldName

you don't mention any errors so I assume you're not getting any so the
syntax, though I've never seen it must be ok?
you're just getting an empty recordset where you'd expect a return eh?

you might try microsoft.public.vb.database.ado or microsoft.public.data.ado
if no one comes along with an answer here

mark

wrote in message news:5865977@discussion.autodesk.com...
I've been using DAO succesfully for a while now, but I wanted to check if
ADO will be a bit quicker at this new project I'm workign on, so I gave it a
shot. It works fine if I want to get the whole table, but as soon as I plug
into it SQL Query it returns nothing 😞 This is the same query I use in DAO
and it works fine, but no working in ADO, can someone clue me in?

Sub testADO()
Dim objCnn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objCmd As New ADODB.Command
Dim qry As String
Dim category As String
category = "CONDUIT"
objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\Shared
Data\AutoCad Support\BOM DATABASE\Stock Items.mdb;Persist Security
Info=False"
qry = "SELECT [Old Ref Numbers].[Ref Number], [Stock Items
Combined].Description, [Stock Items Combined].[Material Number], [Stock
Items Combined].Type FROM [Old Ref Numbers] RIGHT JOIN [Stock Items
Combined] ON [Old Ref Numbers].[Material Number] = [Stock Items
Combined].[Material Number]WHERE ((([Stock Items Combined].Description) Like
""*" & itemType & "*"" And ([Stock Items Combined].Description) Like """ &
listFilter & """) AND (([Stock Items Combined].Type)=""" & category &
"""))ORDER BY [Stock Items Combined].Description;"
objRs.Open qry, objCnn, adOpenForwardOnly, adLockReadOnly, adCmdText
With objRs
Do Until .EOF
MsgBox .Fields(0)
.MoveNext
Loop
End With

End Sub

Thanks.

[Anonymous]

Thanks, no errors, just an empty recordset although I know I'm supposed to be getting thigs back. So I think you're unto something here. When I use DAO I simply copy the SQL query as is from access, it works smooth, no changes what so ever, but with ADO I should re-write the queries to a different syntax? if that's the case that sort of explains the problem I'm having.

Thanks again.

[Anonymous]

That is because of wrong wildcard "*" used. In ADO, you use "%" instead of
"*".



"Vik07" wrote in message news:5865977@discussion.autodesk.com...
I've been using DAO succesfully for a while now, but I wanted to check if
ADO will be a bit quicker at this new project I'm workign on, so I gave it a
shot. It works fine if I want to get the whole table, but as soon as I plug
into it SQL Query it returns nothing 😞 This is the same query I use in DAO
and it works fine, but no working in ADO, can someone clue me in?

Sub testADO()
Dim objCnn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objCmd As New ADODB.Command
Dim qry As String
Dim category As String
category = "CONDUIT"
objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\Shared
Data\AutoCad Support\BOM DATABASE\Stock Items.mdb;Persist Security
Info=False"
qry = "SELECT [Old Ref Numbers].[Ref Number], [Stock Items
Combined].Description, [Stock Items Combined].[Material Number], [Stock
Items Combined].Type FROM [Old Ref Numbers] RIGHT JOIN [Stock Items
Combined] ON [Old Ref Numbers].[Material Number] = [Stock Items
Combined].[Material Number]WHERE ((([Stock Items Combined].Description) Like
""*" & itemType & "*"" And ([Stock Items Combined].Description) Like """ &
listFilter & """) AND (([Stock Items Combined].Type)=""" & category &
"""))ORDER BY [Stock Items Combined].Description;"
objRs.Open qry, objCnn, adOpenForwardOnly, adLockReadOnly, adCmdText
With objRs
Do Until .EOF
MsgBox .Fields(0)
.MoveNext
Loop
End With

End Sub

Thanks.

[Anonymous]

Mucho Gracias! Spasibo! Dyakuyu! Thank you!
So the brackets and the same syntax is all good then, it does work. Any other changes to be done to the query as it is copied out of access?

Thanks,
Viktor.

[Anonymous]

In most cases, no other changes needed.

"Vik07" wrote in message news:5866143@discussion.autodesk.com...
Mucho Gracias! Spasibo! Dyakuyu! Thank you!
So the brackets and the same syntax is all good then, it does work. Any
other changes to be done to the query as it is copied out of access?

Thanks,
Viktor.

[Anonymous]

if this is true then your query did not return any records.

wrote in message news:5866021@discussion.autodesk.com...
Thanks, but EOF and BOF are both true and movefirst is not working.

[Anonymous]

Hi Viktor,

In my experience, the square brackets are beneficial. I did a program last
year with about 100 different SQL queries and was struggling with the fact
that a few of them didn't work after copying them across from the Access
query window. In discussions with a programming guru, I noticed that he
used the square brackets always. I added them to my code and never had any
problems.
As Norman told you the only other changes I made were between the * and the
%


--


Regards

Laurie Comerford
wrote in message news:5866143@discussion.autodesk.com...
Mucho Gracias! Spasibo! Dyakuyu! Thank you!
So the brackets and the same syntax is all good then, it does work. Any
other changes to be done to the query as it is copied out of access?

Thanks,
Viktor.

[Anonymous]

Thanks guys!

[Anonymous]

Hi Viktor

On a more general query, do you have control over the format of the data?
If so I recommend transferring it to XML.

Personally I find it much more intuitive to extract data from.
It may be a new process to learn but it appears to be becoming a 'standard' data storage method.

I've never fully understood the point/benefit of the cursor in ADO.
I've found XML much easier to manipulate.

Of course, if you can't then stick with the good advice above.

If anybody else would like to comment, I'd be interested on your views of XML v. Databases as a storage method.

Cheers
Dave F.