Relationships in a SQL statement

[markcrobinson]

I have 4 tables with a common field, ItemNumber.


Set db = CurrentDb
Set rs1 = db.OpenRecordset("select * from Items;")
Set rs2 = db.OpenRecordset("select * from Stock;")
Set rs3 = db.OpenRecordset("select * from OpenPOMARS;")
Set rs4 = db.OpenRecordset("select * from RequireMARS;")

I want to pull the first ItemNumber from Items and look up and combine data from each of the other three databases.
There are a lot of calculations I'm performing on the data -- too many for a query. So I'm writing a vb program.
The sloppy way is to open up all 4 databases and do Lookups.

The joins are all Left joins. I need ALL the records in Items and whatever records exist in the other tables. When I create a query to cheat of figuring out the SQL statement I get:

SELECT Items.itemId, Items.use, Stock.qStk, OpenPOMARS.*, RequireMARS.*" & _
"FROM ((Items LEFT JOIN Stock ON Items.itemId = Stock.itemId)" & _
"LEFT JOIN OpenPOMARS ON Items.itemId = OpenPOMARS.ItemNumber)" & _
" LEFT JOIN RequireMARS ON Items.itemId = RequireMARS.ItemNumber;"

but when I set it to a variable and do
docmd.runsql variable I get
"A RunSQL action requires a argument consisting of an SQL statement."

My Questions:
1 - Whats wrong with my SQL statement? It works in a Query but not in a program.
2 - And I going about this the right way?

Thank You!

[Gicu]

Please try:

Dim sSQL as String
sSQL="SELECT Items.itemId, Items.use, Stock.qStk, OpenPOMARS.*, RequireMARS.*" & _         " FROM ((Items LEFT JOIN Stock ON Items.itemId = Stock.itemId)" & _
         " LEFT JOIN OpenPOMARS ON Items.itemId = OpenPOMARS.ItemNumber)" & _
         " LEFT JOIN RequireMARS ON Items.itemId = RequireMARS.ItemNumber;
Set db = CurrentDb
Set rs1 = db.OpenRecordset(sSQL)   'default is dbOpenDynaset

[pbaldy]

The problem is that RunSQL only works with action queries (append, update, delete). You could open a recordset on the SQL or set a record source or row source to, or...

[markcrobinson]

Please try:

Dim sSQL as String
sSQL="SELECT Items.itemId, Items.use, Stock.qStk, OpenPOMARS.*, RequireMARS.*" & _         " FROM ((Items LEFT JOIN Stock ON Items.itemId = Stock.itemId)" & _
         " LEFT JOIN OpenPOMARS ON Items.itemId = OpenPOMARS.ItemNumber)" & _
         " LEFT JOIN RequireMARS ON Items.itemId = RequireMARS.ItemNumber;
Set db = CurrentDb
Set rs1 = db.OpenRecordset(sSQL)   'default is dbOpenDynaset

You make it look soooo easy!

[kd2017]

I just wanted to point out that a problem with your query in the original post was that you didn't have any spacing between word in your string concatenation. When I'm pragmatically generating a query like this the first thing I do after building the string is print it out to the debug window to make sure the SQL I generated looks valid.

Another easy way to generate SQL would be to just build your query in the query designer and copy the SQL it generates. The Allen Browne website has an example form and code that will format SQL to a VBA string. http://allenbrowne.com/ser-71.html

Oh, and I've been using this formatter a lot lately, it has an output mode "VB" that will build a string for you. http://www.dpriver.com/pp/sqlformat.htm