ADO Query only returns first record

[rmcmorris]

I have a VBA module in Excel where I am returning data from a database using ADO. Previously, the query would match a number variable. This worked fine and returned all of the records. Now I am changing the code to match a string variable instead on a different database field. When using an "=" operator for the match in the query, only the first matched record is returned to the ADO recordset. If I use the "LIKE" operator, all of the records are returned. Any ideas why this happens????

'This works fine with a number field.
MyNumber=123
rst.Open "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 FROM MyTable WHERE (((MyTable.`NumField`)=" & MyNumber & &quot)", ADOcon, adOpenForwardOnly, adLockReadOnly

'This Does not work with an alpha field. It returns first
'matching record only.
MyText="abc"
rst.Open "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 FROM MyTable WHERE (((MyTable.`TextField`)="' & MyText & "'))", ADOcon, adOpenForwardOnly, adLockReadOnly

'But using the LIKE operator in the Text field
'Works fine.
MyText="abc"
rst.Open "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 FROM MyTable WHERE (((MyTable.`TextField`) LIKE "' & MyText & "'))", ADOcon, adOpenForwardOnly, adLockReadOnly

Thanks,

Regan

 

[dilettante]

Questions that occur to me right off are...

What database? SQL processing varies widely despite attempts at standards.

Do all of the "equal" rows match the case of your search value?

Are there issues with some values having spaces at the left or the right while others do not?

 

[rmcmorris]

I am attaching to a Paradox table. The search values all match exactly, there are no spaces. The query works correctly if I link to the Paradox table from Access and perform an Access query on the string field using the "=" operator.

The query has the same problem using DAO rather than ADO.

The query also will only return the first matched record if I use the Excel Query Wizard to return data to the spreadsheet.

I have installed the Jet40 Service Pack 6. My OS is Windows 2000 Professional. I am using Excel 97.

It seems to work fine with a number field or using the LIKE operator with a String Field. Something is getting hosed up along the way using the "=" operator.

Regan

 

[dilettante]

Sounds like a provider (ODBC?) or a Paradox issue.

Does Paradox use Jet? I thought it had its own equivalent.

Anything else different about these items besides numeric vs. text? Indexed vs. not, etc?

Sorry, I'm out of ideas - no Paradox experience here.

 

[rmcmorris]

Actually, the connection works through Jet40. This is then integrated somehow through the Jet 40 Paradox driver with the Borland Database Engine (BDE) which is the control for Paradox tables. In the old Jet3.5, you needed to specify the location of the Paradox Network locking file, but Microsoft did away with that requirement in Jet40. I believe the Jet40 engine uses the registry settings for BDE to determine the location of the NET file.

Here is the ADO connection string:

ADOcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\DATABASE;Extended Properties=Paradox 5.X"

I'll experiment with some other tables and text fields and see if I can shed any more light on the problem.

Regan

 

[rmcmorris]

What I have found is that the error occurs only if the search field is the first index key. If I remove all indexes from the Paradox table, the query will correctly return all matching rows. If I add a new key as the first key, then the query will also work correctly, returning all rows. For example, Instead of having the table indexed on `TextField`, and `Field1`, If I add an additional key and key the table on `NewKey, `TextField`, and `Field1`, then the query works correctly.

I have narrowed the problem, I believe, to the Microsoft DLL MSPBDE40.DLL, rev. 4.0.5325.0. This comes with Jet40 SP6.

Any confirmation on this problem would be useful.