Open Access file with SQL Statement

[fowlerjp]

When opening a database do I have to select the whole table?

This works:
strSQL = “SELECT * FROM mytable”
rstdb.open strSQL, dbcon, , , adcmbtable

This Does Not:
strSQL = “SELECT * FROM mytable WHERE myfiled LIKE ‘ABC*’”
rstdb.open strSQL, dbcon, , , adcmbtable

I tested the statement in the Visual Data Manager and it works fine. Can this just not be done?

[Ianpbaker]

Hi fowlerjp

There shouldn't be any reason what so ever why you can't use that SQL statement. You can use any SELECT statement that you like to open a recordset.

what error are you getting Back ?


Ian

[Ianpbaker]

Hi again. the reason the second query doesn't work is becuase you are using the adcmdtable in the options of your recordset.open. take it out and it should work fine.

The adcmdtable option returns the whole table. for more information check out this link

http://msdn.microsoft.com/library/psdk/dasdk/mdae1oz1.htm

hope this helps

Ian

[fowlerjp]

OK, I dropped the extra “, , , adcmbtable” but I still had the same problem. It was not returning any error codes it was simply returning –1 rows AKA nothing. But I kept looking and found an exact copy of what I was trying to do in MSDN.Search for and include the quotes “Running a basic query”

Private Sub RunButton_Click()
Dim rs As New ADODB.Recordset
rs.Open "select * from titles where title like '%h'", cn
ADOGrid1.ShowData rs
rs.Close
End Sub

So I changed my string to this and it worked!

strSQL = “SELECT * FROM mytable WHERE myfiled LIKE ‘ABC%’”
rstdb.open strSQL, dbcon, , , adcmbtable

Being new and only writing programs to make my life simpler. This would seam to deviate from what I have learned about SQL statements. I also tried the new string in “Visual Data Manager” and it returned nothing.

Background:
Create DB programmatically in Access 97 format.
Access using ADO Jet OLEBD 4.0 programmatically (Not the Control)
I did this so I can also open the file in Access 2000 and still read it.

Thanks for the help; if you have any idea why this is I am interested.
Jason

[Ianpbaker]

Sorry Jason, I forgot to pick up on to ask whether your using access or not becuase as you rightly found out you need to use a % sign as a wild card in access wheras in SQL server it is a *.

As for returning the record count, you need to be careful becuase only cerain types of cursors and locking options return the record count back.

If you get any more problems reply back.

all the best

Ian

[Clunietp]

% is the correct wildcard for SQL server
% is the correct wildcard for MS Access via ADO
* is the correct wildcard for MS Access via DAO

you need to specify a cursor type (besides the default Forward Only cursor)

rstdb.open strSQL, dbcon, adOpenStatic, adLockOptimistic, adCmdText

You should now be able to get the recordcount