Problems with SQL

Hi all...

see, im having quite a few problems with data retriving.

this is the code:

Private Function Conexion_DbMateriales()
Set Db_Materiales = OpenDatabase("C:\WINDOWS\Pro-Pet-Ind. Databases\Materiales.mdb")
Set Tb_Materiales = Db_Materiales.OpenRecordset("SELECT * FROM Materiales")
End Function

this should retrieve a full list of codes, but only return 1 code

if i errase my sql statement, leaving only "Materiales" it works fine.
any ideas?

thanks!

It's wrong that use sql statemet In the OpenRecordset function..I think..
Plz see it:http://msdn.microsoft.com/library/d...enrecordset.asp

Hey, thanks for your answer.

but i guess the statement it is fully ok.
moreover, i checked at msdn page, and it is correct.

but i dont know why it returns only 1 record, instead of the whole list of records.

any ideas??

How can u know it returns 1 recordset??Plz post your code for me..

The code is as simple as this:

Private Function Conexion_DbMateriales()
Set Db_Materiales = OpenDatabase("C:\WINDOWS\Pro-Pet-Ind. Databases\Materiales.mdb")
Set Tb_Materiales = Db_Materiales.OpenRecordset("SELECT * FROM Materiales")
End Function

When I try a "Msgbox(Tb_Materiales.RecordCount)" it returns a 1. which means that there is only one record.

thanks

does it show a recordcount of "1" or "-1"? Big difference if it shows "-1". That indicates that the recordset is not set up for dynamic, client-side processing. If a recordset is opened in its default state, then it will default to server-side and static, in which case, ADO returns a recordcount of -1

I'm glad to see u again,Fisherman!...

Well, actually, many times happened to me to get a "-1" but this is not that problem. In fact, I CAN see the only record returned, only one record, but not the whole.

Thanks!!

I think your returned recordcount value is true(a boolean value).Give u Some information for the reason:
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset, regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

NOTE: In ADO versions 2.8 and earlier, the SQLOLEDB provider fetches all records when a server-side cursor is used despite the fact that it returns True for both Supports (adApproxPosition) and Supports (adBookmark),

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

And http://msdn.microsoft.com/library/d.../mdapro05_9.asp

This is a most unusual method of opening a connection and recovering a recordset. Why don't you either use the Recordset.open method, or the connection.execute method. These are often easier to debug and offer more control

I'm aree with FisherMan!..Use ADO RecordSet to debug your code and check the returned result!..