Error In From Clause

[hascons]

Hello,

I'm getting an Error ( Error in From Clause ) with the following Statement:

strSQL = "SELECT * FROM [Bid Work Type SubCategory] WHERE [BidWorkTypeCategoryID]= " & strCategoryID

Set rsN1 = New ADODB.Recordset
rsN1.CursorType = adOpenKeyset
rsN1.LockType = adLockPessimistic
rsN1.Open strSQL, cnn, , , adCmdTable

I believe I have tried every conceivable way to write the SQL statement but I Keep getting this error.

Is There some special way to do this with ADO?

 

[boblarson]

If BidWorkTypeCategoryID is text then you need quotes:

strSQL = "SELECT * FROM [Bid Work Type SubCategory] WHERE [BidWorkTypeCategoryID]= " & [B][COLOR=red]Chr(34) &[/COLOR][/B] strCategoryID [B][COLOR=red]& Chr(34)[/COLOR][/B]

 

[boblarson]

By the way, what is it you are intending to do with the ADO part?

 

[hascons]

Hi Bob

[BidWorkTypeCategoryID] is a number Field.

I'm populating a TreeControl with the recordset.

I can use a designed query and this seems to work fine. I just like to create a SQL Staement in code if I only need the query for 1 specific purpose. I Cheat and build a query in the designer than I copy this SQL Statement to the VBA Macro and add the necessary apostrophes to make it a String Text.

I've been experimenting with ADO, The funny thing is the above statement does work if I'm using DAO Recordset. I was just wondering if there is some other syntax that i was missing if were to use ADO.

 

[boblarson]

I don't see where you've assigned anything to

cnn

your connection. How are you doing that?

 

[hascons]

Bob,

cnn is a global variable used to establish a connection on every form in my Project.
I Get the connection through a class Module, on the form Open event. It remains open through the life of the form and all objects and recordset are closed and set to nothing on a Form Close Event.

 

[jal]

Hi Bob

[BidWorkTypeCategoryID] is a number Field.

I'm populating a TreeControl with the recordset.

I can use a designed query and this seems to work fine. I just like to create a SQL Staement in code if I only need the query for 1 specific purpose. I Cheat and build a query in the designer than I copy this SQL Statement to the VBA Macro and add the necessary apostrophes to make it a String Text.

I've been experimenting with ADO, The funny thing is the above statement does work if I'm using DAO Recordset. I was just wondering if there is some other syntax that i was missing if were to use ADO.

Turns out I managed to reproduce the error. Apparently ADO doesn't like you using a WHERE clause with your "AdCmdTable" - I'm guessing you have to select the whole table to use adCmdTable.

 

[hascons]

Jal

Thanks for your response.

I changed this procedure to DAO and it works fine.