View original (French)

Syntax error in the FROM clause

dormdaz Posted messages 361 Status Member -  
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   -
Hello,

I wrote a small program in VB6 with an Access database. After execution, it shows an error message as follows: [Microsoft][ODBC Access Driver] Syntax error in FROM clause. and the 'Refresh' method of the 'Adodc' object failed.
And the body of the program is as follows: 

Private Sub cmd_chercher_Click() If Me.Tcode.Text = "" Then t = MsgBox("You must enter the code first", vbExclamation + vbOKOnly, "Information") Exit Sub End If Adodc_maj_clients.RecordSource = "SELECT maj_clients.* From maj_clients WHERE (maj_clients.code = '" & Me.Tcode & "')" Adodc_maj_clients.Refresh If Me.Adodc_maj_clients.Recordset.RecordCount <> 0 Then Me.Text11.Text = Me.Adodc_maj_clients.Recordset!nom Me.Text9.Text = Me.Adodc_maj_clients.Recordset!Date_N Me.Text6.Text = Me.Adodc_maj_clients.Recordset!Etat Me.Text5.Text = Me.Adodc_maj_clients.Recordset!Date_RDV '' Me.Text4.Text = Me.Adodc_maj_client.Recordset!Date_Paiement Me.Text3.Text = Me.Adodc_maj_clients.Recordset!N_Recu Me.Text2.Text = Me.Adodc_maj_clients.Recordset!N_reg Me.Text12.Text = Me.Adodc_maj_clients.Recordset!Agence Me.Text3.Text = Me.Adodc_maj_clients.Recordset!Date_Paiement Else p = MsgBox("Search unsuccessful", vbOKOnly + vbInformation, "Information") Exit Sub End If End


I really can't solve the problem, thank you in advance and have a good day

Configuration: Windows XP / Firefox 33.0

4 answers

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    try this

    Tcode is a string 

    Adodc_maj_clients.RecordSource = "SELECT maj_clients.* From maj_clients WHERE maj_clients.code = " & Me.Tcode
    0
  2. PlacageGranby Posted messages 402 Status Member 26
     
    Rather difficult to test.
    If you start with
    " SELECT maj_clients.* From maj_clients"

    Does the error occur?
    If so, we know that the table name or the connection to the DB is causing the problem.

    If not, well we know that it is: WHERE ( maj_clients.code = '" & Me.Tcode & "')
    that is causing the problem.

    Simplify the code until it works, and add complexities one by one to pinpoint which part is causing the issue.
    0
    1. dormdaz Posted messages 361 Status Member
       
      Thank you anyway, the problem is not resolved. Here is exactly where my program stops:
      Adodc_maj_clients.RecordSource = " SELECT maj_clients.* From maj_clients WHERE ( maj_clients.code = '" & Me.Tcode & "') "
      Adodc_maj_clients.Refresh <----------- ( the problem appears here )
      If Me.Adodc_maj_clients.Recordset.RecordCount <> 0 Then
      .
      .
      .
      I can't solve the problem, I thank you infinitely.
      Dorm.
      0
    2. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
       
      Hi,

      wouldn't it be a requery rather than a refresh?

      And if maj_clients.code is numeric, there's no need to use single quotes...
      0
    3. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Hello Blux,

      in VB6.0 there is no requery for the adodc data control.
      0
    4. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
       
      I don't know VB6.0 :-(

      However, it would be interesting to check the content of the query by putting all this in a msgbox, the explanation is probably there (me.tcode with a bizarre value (or maybe not filled in...), maj_clients.code numeric...)
      0
    5. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Re,

      me.tcode is a textbox I suppose, therefore a string of characters.
      0
  3. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hi,

    the syntax of the request is good, but with your VB6 project and the database available at https://www.cjoint.com/ it would be easier.
    0
    1. dormdaz Posted messages 361 Status Member
       
      I thank you, but the problem I first located when I use Adodc alone with text editors to retrieve data. And when I run it, everything is fine, but when I use the text editor to retrieve the code to search in the table, I am obliged to do these instructions:
      Adodc_maj_clients.RecordSource = " SELECT maj_clients.* From maj_clients
      WHERE ( maj_clients.code = '" & Me.Tcode & "') "
      Adodc_maj_clients.Refresh <--- (the error appears here)
      and when I execute the program, it displays an error message:
      [Microsoft][ODBC Access Driver] Syntax error in FROM clause. and The 'Refresh' method of the 'Adodc' object has failed.
      Please, is there another way to retrieve and display the data? Thank you very much.
      Dorm.
      0
  4. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,

    Knowing nothing about it, I’ll just throw in my two cents.
    Sorry for the inconvenience if this isn’t it, just consider that I was passing by...

    Seen on Microsoft.com:
    If the CommandType of the ADO Data control is set to
    adCmdTable, the instruction "SELECT * From" is automatically added to
    the RecordSource value. Setting RecordSource to an SQL instruction, such as Select * From Table_name, results in an incorrect SQL instruction like Select * From Select * From Table_name.


    To test then: 

    Adodc_maj_clients.CommandType = adCmdText 'or better: adCmdUnknown Adodc_maj_clients.RecordSource = " SELECT maj_clients.* From maj_clients WHERE ( maj_clients.code = '" & Me.Tcode & "') " Adodc_maj_clients.Refresh


    Sources: This very poor translation...

    🎼 Best regards,
    Franck 🎶
    0
    1. dormdaz Posted messages 361 Status Member
       
      Thank you very much. There's a small thing; how do I define the command text (SQL)? I get an error message "Adodc1: [ADODC]: No resource specified [Ado]: No command text has been defined for the command object."
      I'm really sorry for the inconvenience.
      Best regards.
      Dorm
      0
    2. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      As previously stated, I don't know anything...
      Personally, I thought that the "connection" always needed to be declared. A bit like this example: 

       Private Sub DataGrid1_Error(ByVal DataError As Integer, Response As Integer) Dim MaComm As ADODB.Command, Recup If DataError = 6153 Then Set MaComm = New ADODB.Command 'CONNECTION!!! With MaComm .ActiveConnection = Adodc1.Recordset.ActiveConnection .CommandText = "INSERT INTO Publishers (Name,[Company Name]) VALUES('" & Adodc1.Recordset!Name & "','" _ & Adodc1.Recordset![Company Name] & "')" .Execute .CommandText = "SELECT @@IDENTITY" Recup = .Execute .CommandText = "INSERT INTO Titles (Title,ISBN,[Year Published],PubId) VALUES('" & Adodc1.Recordset!Title & _ "','" & Adodc1.Recordset!ISBN & "','" & Adodc1.Recordset![Year Published] & "','" & Recup(0).Value & "')" .Execute End With Set DataGrid1.DataSource = Nothing Adodc1.Recordset.CancelUpdate Adodc1.Recordset.Requery Set DataGrid1.DataSource = Adodc1 Response = 0 End If End Sub


      But, this link might prove useful...
      0