ADO Control - "Syntax Error in FROM Clause" 2

[CIMTEET]

Hello again, I am connecting a VB 6 form to an Access 97 Database using an ADO control.

Whenever I use the Ado properties to build a connection, it tests out fine. I first go to the properties of the text box and use the "data sources" property to select my Ado control. Then I move up to the "Data field" property, where there should be a list of fields in the bound table. When I press the drop down arrow to get the list this error appears.

"Syntax error in FROM clause"

All I can do is hit OK, no field options appear in the list.
It won't let me bind text boxes. I am not writing any sql statements so I am sure this is underlying code. I am not sure why this happens. It is not consistent.

Second question.
Can I setup two ADO controls and attach them to the same field? (each conrol has its own form) Just curious.

 

[MikeCox]

I'd check all the rest of the properties of the ado, specifically the CommandType and RecordSource. Set CommandType to adCmdTable if you're not using SQL as the RecordSource, and set the RecordSource to the table you're looking to connect to.

Hope that does it!

-Mike


Difference between a madman and a genius:
A madman uses his genius destructively,
A genius uses his madness constructively.

 

[CIMTEET]

Mike

I checked the control properties and they are set on CmdTable and I have my table selected. Is there anything else I could look at?

 

[woyler]

What is in the recordsource property?

 

[CIMTEET]

Woyler,

Command Type
2-adCmdTable
Table or Stored Procedure Name
General (table name)

 

[woyler]

If you click on the data control, then look in your properties pane, what exactly is the text in the recordsource property say?

 

[MikeCox]

Ya, if you select the RecordSource property and click the ... button that appears, you should see a combobox titled "Table or Stored Procedure Name". Your table names should be available in that dropdown.

To answer ur second question above, an Ado doesn't typically connect to just one field, a textbox (or other bound control) connects to one field via the ado, which connects to the entire table in your case. But you can have a separate Ado on a different form, and bind a control to a field that's already bound to elsewhere. I don't know what problems this may cause tho. For instance, say the user (or your program) makes a change to that field from the second form. If (s)he closes that form after updating, does the first control reflect the change made without refreshing the recordset? If not, be aware that if the user moves to the next record, the database may be updated back to the origional value still in the old textbox. A safer way to do this is handle the second control's binding behavior thru code:

txtSecondTextBox_Validate
adoMain.Recordset.Fields("MyFieldName&quot.Value = txtSecondTextBox.Text
frmMain.txtFirstTextBox.Text = txtSecondTextBox.Text
End Sub

In this case, you don't need to bind txtSecondTextBox to anything, which avoids the possible anomoly I mentioned above. You can use the .Fields(index).Value to populate the second textbox on form_load.

Hope that makes sense!

-Mike
Difference between a madman and a genius:
A madman uses his genius destructively,
A genius uses his madness constructively.

 

[CIMTEET]

I see what you mean woyler. My fault. The Recordsource property says "General" which is my correct table name. Should it say something else? My datasource property has "Adodc1" which is the name of my ADO control. Its when I go to change the "datafield" property it gives me the "Syntax error in FROM Clause" window, therefore not letting me bind my text box to the database. Just a refresher on the problem.

 

[CIMTEET]

Mike

Thank you for the code snips. I am going to try them when I get past this first problem. Thanks again.

 

[woyler]

try setting it to cmdText and typing "SELECT * FROM GENERAL"

 

[MikeCox]

Ya, it'll give u syntax error in FROM clause if the Recordsource doesn't match the CommandType. If the RecordSource of the Adodc1 is set properly before you set the DataField of the bound control, you should no longer get this error (which isn't critical if you eventually set the RecordSource to what it needs to be, it's just letting you know). Difference between a madman and a genius:
A madman uses his genius destructively,
A genius uses his madness constructively.

 

[CIMTEET]

Woyler,
I entered the command text you gave me and I ended up with the same error. I have VB at home. I might try it there just to see if my PC at work is the problem. I will also try the sql syntax to gave. I have never setup an ADO control like that before and would like to see it work.

Mike,

The command type and corresponding boxes and properties do match. This seems to be a freak problem or something.

I want to thank both of you guys for your input. Its cool that take a little bit of time out of your day to help me out. Much Appreciated!! I will let you know how I fair out tonight. Please, if you think of anything else, feel free to post it.

Thanks again
GregI

 

[CIMTEET]

You guys are not going to believe what the problem was. It was in the table name itself. "General" is reserved by SQL or something like that. It would be like calling my table "From" or something. When I changed the name it worked great. Thank you guys for working with me and I look forward to a day, far far away, where I might be able to help you. Hey, it could happen! I know MS Word really well. lol, just kidding, Peace

Greg