I have two tables in a database in access and I want to perform CRUD operations from excel vba. Though I was able to perform CRUD into individual tables separately, but it says some JOIN Error while performing CRUD on both the tables at same time.Here are the
Relationship between them.
I tried something like this but it says a JOIN Error
"Select CustomerT.First_Name,ProductT.Product_Name,CutomerT.Age From CustomerT Inner Join ProductT ON Customer.ID = ProductT.Customer_ID"
My main aim is to find out the details about the customer ans the product he/she ordered by entering the customer id.
Please suggest what to do.
Here is the full code that I am using
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Access2Excel\DB1.accdb;Persist Security Info=False;"
'Open Db connection
Set rs.ActiveConnection = con
rs.Open "Select CustomerT.First_Name,ProductT.Product_Name,CutomerT.Age From CustomerT Inner Join ProductT ON CustomerT.ID = ProductT.Customer_ID "
StartRow = 3
Do Until rs.EOF
Cells(StartRow, 4) = rs.Fields(0).Value
Cells(StartRow, 5) = rs.Fields(1).Value
Cells(StartRow, 6) = rs.Fields(2).Value
StartRow = StartRow + 1
Set rs = Nothing
Set con = Nothing
there seems to be a
T missing in
Customer.ID. The join should be like:
From CustomerT Inner Join ProductT ON CustomerT.ID = ProductT.Customer_ID
CustomerT.Age, thanks LondonRob!