Parameter in SQL for Access ADODB

[HobbitK]

Hi everyone ...
I am using an ADODB connection in a .BAS module(it accepts a strSQL as its' input) to run a simple select statement on an Access DBase. I use the return to dynamically add Option buttons and name them according to the business names returned.
That works great, but when I try to pass the .BAS procedure a different SQL statement, it does not return any records.
Here is the calling code ...
Private Sub Option1_Click(Index As Integer)

Dim strCoName As String
Dim strSQL As String

strCoName = Option1(Index).Caption
strSQL = "Select * From Suppliers where CompanyName = '" & strCoName & "'"
modTestConnect.ConnectToDBase (strSQL)
MyRS.Fields.Item("Dropped&quot.Value = True
MyRS.Update

End Sub

The original SQL that works is ...
strSQL = "SELECT CompanyName FROM Suppliers WHERE dropped = false;"
The only difference I see is the second SQL does not have the ; at the end. Could that be the problem? If so, where do I include it?

 

[mmilan]

Your current code limits you down to whichever company is in strCoName, and then assumes that a valid record is returned.

You should always check the bof and eof properties of a recordset before trying to do anything with a recordset returned from a "Select".

With all due respect, I think you should perhaps read a tutorial about using ADO - there are many on the internet. Try using Google.

mmilan.

 

[Mystic1112]

HobbitK,
As far as I know, when sending SQL statements to Access DBs you have to end the statement with a ; for it to process. To answer your question, you would include it after the strCoName.

Mystic1112