Syntax error in JOIN operation

[ethanplace]

I'm trying to inner join multiple tables together from an access database but I keep on getting syntax error messages and I am unsure why.
Here's the code:

da = New OleDbDataAdapter("SELECT Categories.Category, Subcategories.Subcategory, Descriptions.Description, Quantities.Quantity FROM (((Categories INNER JOIN Subcategories ON Categories.CategoryID = Subcategories.CategoryID) INNER JOIN Subcategories ON Subcategories.SubcategoryID = SubcategoryDescription.SubcategoryID) INNER JOIN Descriptions ON SubcategoryDescription.DescriptionID = Descriptions.DescriptionID) INNER JOIN Quantities ON Quantities.SubDescID = SubcategoryDescription.SubDescID", con)

 

[The_Doc_Man]

Might be a pain in the toches to do this, but consider the Julius Caesar method: Divide and conquer. You can join tables, but Access really doesn't like that kind of join that well, and for debugging purposes you have a nightmare on your hands.

Instead, isolate the joins. Build queries to join the first couple of tables. Then do a query that does the join between the next table and the first query. (You can establish a link in the query design that is NOT an application-level relationship, but rather is local to that one query.) Add one join at a time until you have all your joins. This, I have done many times. I've had my own battles on multiple joins in a single query.

Now, I'll tell you why this approach is preferable. You can open the layers one query at a time to see what is returned for each as a way to verify what is in the output recordset. Remember that for the most part, Access doesn't CARE whether an input recordset came from a query or a table. The ONLY time you would have issues is if you wanted to build an UPDATEABLE query from four tables.

Don't forget that you used INNER JOIN in a circumstance that looks pretty complex. If even ONE of those tables really should be referenced by a LEFT JOIN or RIGHT JOIN because it a potentially sparse parent/child relationship, you might end up with less data than you first anticipated. That's another reason why you might want separate queries. The recordset navigation at the bottom of the query will give you a record count at each stage, which would help you find when/where you lost records you didn't want to lose. Or where you unexpectedly GAINED a bunch of records.

 

[sneuberg]

Where is this code? I tried put this statement in a VBA sub and the problem with it there is that OleDbDataAdapter is not recognized? Is this supported in VBA and if so what reference do you need for it?