Left Outer Join problem in RS

[bobmac-]

Hi,

I have a query with a Left Outer Join. The query returns a #DELETED value in the returned recordset if their is no corresponding record. This is what happens when I run the query stand alone.

When I run the query using recordsetopen into an RS and try to read each record I eventually get 'Record deleted' error message and it falls over.

Is there a way of placing a piece of substitute text in the offending field so that it doesn't believe a record has been deleted? As part of the query itself perhaps.

I've tried to detect the error using ISEERROR but it doesn't seem to detect the bad field

Cheers, Thanks in advance

Bob

 

[gemma-the-husky]

if there IS NO deleted record, then that sounds more like a corruption to me.

Inspect the tables, and see if you have any strange records - it may just be a single field

 

[vbaInet]

Yeh, it may well be corruption.

1. Make a copy of your database
2. Create a new database shell and import all your objects into this new shell - at which point it would have gotten rid of corrupted records
3. Delete the relationship between the problem tables
4. Compile and Compact & Repair
5. Relink the tables

 

[bobmac-]

Hi,

I will check for corruption but I don't beleive that is the situation.
I've attached a display of the query design and the associated output.
Note the Outer Join.

A bit of background; Waterways and Banks have either a 'L' or 'R' (Left or Right)
You'll notice that the #Deleted appears where the object is not a Waterway or Bank. This is what I would expect. i.e. No record when it isn't logical to have one. For example, Regulators don't have a Left or Right

 

[bobmac-]

Hi

The upload didn't work the first time

 

[boblarson]

What's the actual SQL of the query?

 

[bobmac-]

Hi

As requested and design jpeg attached

SELECT dbo_ASSET.ASSET_ID, dbo_ASSET.ASSET_CODE, dbo_ASSET.RUNNING_DISTANCE, dbo_COMPONENT_TYPE.COMPONENT_TYPE_DESC, dbo_C_BANK.BANK_POSITION, dbo_CONDITION.RATING, dbo_ASSET.OWNERSHIP, dbo_ASSET.NUMBER_ON_PLAN
FROM (((dbo_CONDITION INNER JOIN (((dbo_ASSET INNER JOIN dbo_ASSET_ASSET_GROUP ON dbo_ASSET.ASSET_ID = dbo_ASSET_ASSET_GROUP.ASSET_ID) INNER JOIN dbo_ASSET_GROUP ON dbo_ASSET_ASSET_GROUP.ASSET_GROUP_ID = dbo_ASSET_GROUP.ASSET_GROUP_ID) INNER JOIN dbo_ASSET_COMPONENT ON dbo_ASSET.ASSET_ID = dbo_ASSET_COMPONENT.ASSET_ID) ON (dbo_CONDITION.ASSET_COMPONENT_ID = dbo_ASSET_COMPONENT.ASSET_COMPONENT_ID) AND (dbo_CONDITION.ASSET_ID = dbo_ASSET_COMPONENT.ASSET_ID)) INNER JOIN dbo_ASSET_TYPE ON dbo_ASSET.ASSET_TYPE_CODE = dbo_ASSET_TYPE.ASSET_TYPE_CODE) INNER JOIN dbo_COMPONENT_TYPE ON dbo_ASSET_COMPONENT.COMPONENT_TYPE_CODE = dbo_COMPONENT_TYPE.COMPONENT_TYPE_CODE) LEFT JOIN dbo_C_BANK ON dbo_ASSET.ASSET_ID = dbo_C_BANK.ASSET_ID
WHERE (((dbo_ASSET.ASSET_NAME)=[Forms]![frmAssetLifeMain]![cboChannel]) AND ((dbo_ASSET.ASSET_TYPE_CODE)="ST" Or (dbo_ASSET.ASSET_TYPE_CODE)="CH") AND ((dbo_ASSET_GROUP.SHORT_NAME)="MID Irrigation"))
ORDER BY dbo_ASSET.ASSET_NAME, dbo_ASSET.RUNNING_DISTANCE;

 

[bobmac-]

Hi

I've decided to try removing the outer join from the querydef and then using the resulting RS with a nested RS using a SELECT with the dbo_C_Bank table to enable detection of missing dbo_C_Bank records.

i.e. I've created a blank column in the original querydef and removed the outer join. Then using a recordset resulting from querydef I loop though and create a another recordset which includes the dbo_C_Bank table. If there are no records returned, I fill the blank column with something like 'NA' and where there is a return I place the Value in the Blank column.

The code, see below, has had the option dbseeChanges added as I was getting a 3622 'You must use dbseeChanges option. However, now I get
'too few parameters expected 2' error.

This is getting very frustrating, any help would be greatly appreciated


Set myDb = CurrentDb()
Set rs = Nothing
Set qdf = myDb.QueryDefs("qryAssetDetails_lessBank")

qdf.Parameters(0) = Me.cboChannel

Set rs = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)

' Sometimes required to get accurate count
rs.MoveLast
rs.MoveFirst
intRecCount = rs.RecordCount
Debug.Print intRecCount

' Load result array
ReDim strResultArray(0 To intRecCount, 0 To intFieldCount)

With rs
.MoveFirst
For intRowNumber = 0 To intRecCount - 1
strSQLBank = "SELECT * FROM qryAssetDetails_lessBank, dbo_C_Bank WHERE db0_C_Bank.Asset_ID =" & .Fields(0) & ";"
Debug.Print strSQLBank
Set rs2 = myDb.OpenRecordset(strSQLBank, dbOpenDynaset, dbSeeChanges)
rs2.MoveLast
rs2.MoveFirst
intRs2Count = rs2.RecordCount
If intRs2Count <> 0 Then
.Fields(3).Value = rs2.Fields(8)
Else
.Fields(3).Value = "NA"
End If
For intFieldNumber = 0 To intFieldCount - 1
strResultArray(intRowNumber, intFieldNumber) = .Fields(intFieldNumber)
Next
If Not .EOF Then .MoveNext
Next
End With

NB. Column numbers may be out of wack but that shouldn't be creating the errors I'm getting

 

[boblarson]

Does this query: qryAssetDetails_lessBank

have any criteria on it? If so you have to explicitly add parameters to it either by specifying them in the query (using the Parameters under QUERY) or you have to use DAO code to add them.

 

[bobmac-]

Hi Bob,

Yeah you're right. The outer RS loop does use qdf.parameters(0) to pass a channel name from the form but, when I resuse the query in the inner RS loop I'm not.

The inner loop uses a SELECT statement with the query. Is there a way I can pass the criteria into the SELECT statement or perhaps use the RS from the outer loop within the SELECT statement?