Where Not Exits

[tfhwargt3]

If I have two tables, OrdersAll and OrdersCurrent that both contain the field OrderID, and I want to select all the records in OrdersCurrent that have a different OrderID than ones in OrdersAll, is this the most efficient way to accomplish this task:

"SELECT * FROM OrdersCurrent AS OrdersNew
WHERE NOT EXISTS
(SELECT OrderID FROM OrdersCurrent WHERE OrdersCurrent.OrderID=OrdersAll.OrderID)"

I am used to PHP mySQL where you can check if anything is returned and then go row by row in a loop and do actions on the database. I understand loops in VBA, which is what I am using, but I can't seem to find a way to find out if anything is returned from a query, and then also, how to make a loop that deals with each record the results of a query have returned. Basically I would like to do something like this:

If data exists in OrdersNew
...while row exists in OrdersNew
...... do some UPDATES, INSERTS etc.
...endloop

Does anyone know if its possible to do stuff like this is VBA/MS Access environment?

Thanks

 

[Remou]

This kind of thing?

http://www.granite.ab.ca/access/email/recordsetloop.htm

 

[PHV]

SELECT C.*
FROM OrdersCurrent AS C LEFT JOIN OrdersAll AS A ON C.OrderID = A.OrderID
WHERE A.OrderID Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tfhwargt3]

So I need to create a RecordSet from my results?

I am doing this once I make my query

DoCmd.RunSQL "SELECT ..."

Will this work?

Dim rs As RecordSet
Dim lngRCount As Long

rs = DoCmd.RunSQL "SELECT ... "

lngRCount = rs.RecordCount
if lngRCount = 0 Then
...Blah
Else
...Do My stuff

Is that how a recordset works?

 

[Remou]

RunSQL is only suitable for action queries. You need something like:

Set RS = CurrentDB.OpenRecordset _
("Select Field1, Field2 From tblTable")

 

[tfhwargt3]

Thanks everyone for the help. I am learning quickly how not helpful the debugging statements are in Access. So when I run this as the last poster said:

Set rsNewOrders = curDatabase.OpenRecordset ("SELECT * FROM OrdersAll, OrdersNew WHERE OrdersNew.[order-id] = OrdersAll.[order-id]")

This gives me Runtime Error '13' Types mismatch

When I run the exact query in the query window it works fine. I am not familiar with record sets, so maybe someone can explain this error.

Thanks for all the help.

 

[tfhwargt3]

In the references screen I have 5 things checked. They are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft AccessX Data Objects 2.1 Library
OLE Automation
Microsoft ADO Ext 2.8 for DDL and Security

 

[MajP]

this returns a dao.recordset not an ado recordset
curDatabase.OpenRecordset
Need a reference to DAO.

 

[MajP]

I assumed that Also make sure to
Dim rsNewOrders as dao.recordset

set rsNewOrders = currentDB.openrecordset....

 

[Remou]

With Jet, one would gererally use a Join for this type of query. For example:

[tt]SELECT * FROM OrdersAll INNER JOIN OrdersNew ON OrdersAll.[order-id] = OrdersNew.[order-id][/tt]

Or

[tt]SELECT * FROM OrdersAll LEFT JOIN OrdersNew ON OrdersAll.[order-id] = OrdersNew.[order-id][/tt]