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.