Select Query fails to retrieve from multiple tables in Access

[vickey66]

Hi,
I am new to VB programming. I am using Aceess 2010 database with VB 2010. I have one main table which stores information of company details. The details are broken down in seven different tables with codes to avoid data duplication and keep the main table less bulky. e.g.
Company table stores, company code, city code, state code, branch code etc., which can be multiple entries, with other details.
The details are stored in respective code tables. e.g. com_code table will store Company Code as Primary Key and the Company Name, city_code table will store city_code as primary key and city name etc.
When I try to retrieve the data from single or two tables it works, but I do it with all these tables to display in the form, the select query gives Syntax error. I have been trying to solve this for three days without result.

My code is as below:

tmpID = cbComDetComID.Text
   ConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\DB.accdb"
        Qre = ("SELECT Comp_DB.*, Comp_Code.Com_Name, [Branch_Code.Branch_Detail], City_Code.City_Name, State_Code.State_Name," & _
                  "Ind_Code.Ind_Name, [Prod_Code.Prod_Detail] " & _
                  "FROM (((((Comp_Code INNER JOIN Comp_DB ON Comp_Code.Com_Code = Comp_DB.Com_Code)" & _
                          "INNER JOIN City_Code ON Comp_DB.City_Code = City_Code.City_Code)" & _
                         "INNER JOIN Branch_Code ON Comp_DB.Bran_Code = Branch_Code.Bran_Code)" & _
                        "INNER JOIN State_Code ON Comp_DB.State_Code = State_Code.State_Code)" & _
                       "INNER JOIN Prod_Code ON Comp_DB.Prod_Code = Prod_Code.Prod_Code)" & _
                      "INNER JOIN Ind_Code ON Comp_DB.Ind_Code = Ind_Code.Ind_Code;" & _
             "where Comp_DB.Com_ID = '" & tmpID & "'")


        Dim Conn As New OleDbConnection(ConStr)
        Conn.Open()
        Cmd = New OleDbCommand(Qre, Conn)
        da = New OleDbDataAdapter(Cmd)
        ds = New DataSet()
        da.Fill(ds, Qre)

• It gives error at this stage with Qre marked for error
  The error is : Syntax error (missing operator) in query expression 'Prod_Code.Prod_DetailFROM (((((Comp_Code INNER JOIN Comp_DB ON Comp_Code.Com_Code = Comp_DB.Com_Code)INNER JOIN City_Code ON Comp_DB.City_Code = City_Code.City_Code)INNER JOIN Branch_Code ON Comp_DB.Bran_Code = Branch_Code.Bran_Code)INNER JOIN State_Code '.
  any and all help or suggestions to solve this are highly appreciated.

[cgeier]

Why are you using all these parentheses?

FROM (((((Comp_Code INNER JOIN...

Also, why are you naming columns the same name as the table that contains it?

You're more likely to receive help if you provide some more information about the tables (or the create table syntax for each table). See here for an example of some table documentation that I created for that post.

[vickey66]

Hi
These parentheses come from my habit of writing queries in MS Access. The table names also is an old habit to easily identify the table contents.
About the table, I am using a MS ACCESS database ( .accdb ).
In generatl the main table ( Comp ) is a Commpany Databse, storing details such as Name, Address, Idustry / Application Type, contact details etc. As these details can have multiple records, the information is divided into multiple tables. e.g. Each company will have company code. This code and the name of the company is stored in Comp_Code table, with Code as PrimaryKey in Comp_Code and ForeignKey in main table.
For all operations such as ADD (INSERT), MODIFY, UPDATE or DELETE, this information is retrieved from respective tables and displayed on a Form. Once the operation is complete, the information on Form is sent to respective tables.
I think this gives you required information.

Waiting for some solution.