Web Development

Ask something
Discussion Locked

SQL Statement

0 helpful
3 replies

by afisher 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an app in VB 6 which uses an Access 2K backend. I have all of the latest service packs and MDAC updates installed. When I run the following SQL statement as a query within Access, I get one matching record:

SELECT dbo_Property.*, dbo_Apartment_For_SaleDetails.* FROM dbo_Property INNER JOIN dbo_Apartment_For_SaleDetails ON dbo_Property.PropertyID = dbo_Apartment_For_SaleDetails.PropID WHERE (((dbo_Property.Name) LIKE "*plaza*"));

When I use the following code from within my app, Iget no matching records with the above SQL statement:

Dim strConnStr As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

strSQL = SQLStatement

strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Hq_sql\Databases\CompData\Compdata.mdb;" & _
"Persist Security Info=False;"

cn.Open strConnStr

rs.CursorLocation = adUseClient

rs.Open strSQL, cn, adOpenForwardOnly, adLockPessimistic, adCmdText

I do get matching records with this code when using the following SQL statement:

SELECT dbo_Property.*, dbo_Apartment_For_SaleDetails.* FROM dbo_Property INNER JOIN dbo_Apartment_For_SaleDetails ON dbo_Property.PropertyID = dbo_Apartment_For_SaleDetails.PropID WHERE (((dbo_Property.ADNDataClassif) = 1));

This leads me to believe that the problem might be due to an incompatibility between the first SQL statement and the parameters I used while opening the connection and recordset objects.

Thanks in advance for your help!

3 replies

by sl-campbell

Please show the SQLStatement string being used in VB. You may have a mistake in the 'Like' staement.

Try it like this usin SINGLE quotes instead of double quotes:

WHERE (((dbo_Property.Name) LIKE '*plaza*'));

If this doesn't help then post a comment to your question and I will pick it up.

by afisher

Apparently, even thought "*" should be the Access equivalent of SQL's "%", it is the "%" that must be used as a wild card when constructing a SQL statement for delivery to Access via the recordset's Open method. I've found both single and double quotes to work for me. Thanks for taking the time to respond!

This discussion is closed to new replies.