Data Bindings (FYI)

[Mike Labosh]

This is not a real big deal, because it's not a real application, just a
utility form I slapped together to sniff data. But this stupid error
message really got on my nerves.

So I have over 6 gigs of stuff in Access all in tables that have the same
structure. We get huge delimited data files to import and process here, and
this Access stuff is my playground to dump all these data files and then
analyze them to look at the distribution of values, duplicates, etc.

The way I work on this stuff is that I have like 6 or 7 MDB files each with
one table in it (I pour data files into the MDB till it reaches it's 2 GB
limit, and then make another one with the same table structure and pour some
more into it)

Then I have a single database that has the tables linked from the others,
and this is where I do my analysis.

So I have this subform, fsubValueSniffer that has two bound text boxes,
SampleSource and Stuff, in datasheet mode, with no record source.

The main form has an unbound combo box cboColumnName with RowSourceType =
Field List, and RowSource set to one of the tables. The combo box's drop
down lists the column names of the import data.

So I select a column name from the combo box and click this button, cmdSniff
that does this:

Private Sub cmdSniff_Click()

fsub.Form.RecordSource = _
Replace(m_SQL, "COLUMN_NAME", txtColumnName & "")

fsub.Requery
Beep

End Sub

m_SQL is a Private Const that looks like this:

Private Const m_SQL As String = _
"SELECT DISTINCT 'HardwareAAS' As SampleSource, COLUMN_NAME AS Stuff
FROM HardwareAAS WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'HardwareMPDS' As SampleSource, COLUMN_NAME AS Stuff
FROM HardwareMPDS1 WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'HardwareMPDS' As SampleSource, COLUMN_NAME AS Stuff
FROM HardwareMPDS2 WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'HardwareMSM' As SampleSource, COLUMN_NAME AS Stuff
FROM HardwareMSM WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'PrinterMSM' As SampleSource, COLUMN_NAME AS Stuff FROM
PrinterMSM WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'Software' As SampleSource, COLUMN_NAME AS Stuff FROM
Software WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'SoftwareEIW' As SampleSource, COLUMN_NAME AS Stuff
FROM SoftwareEIW WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'SoftwarePassPort' As SampleSource, COLUMN_NAME AS
Stuff FROM SoftwarePassport WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'SoftwareRetain' As SampleSource, COLUMN_NAME AS Stuff
FROM SoftwareRetain WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'StorageMSM' As SampleSource, COLUMN_NAME AS Stuff FROM
StorageMSM WHERE COLUMN_NAME IS NOT NULL " & vbCrLf & _
"UNION " & _
"SELECT DISTINCT 'TapeMSM' As SampleSource, COLUMN_NAME AS Stuff FROM
TapeMSM WHERE COLUMN_NAME IS NOT NULL "

The idea here is that since all these huge tables have the same schema, I
can select one of the columns and do a SELECT DISTINCT on it across all 6
gigs of stuff in all those different MDB files, by replacing the column name
in the SQL, binding the subform and requerying it.

All the columns in all of the tables are indexed with dupes allowed.

It takes on average, about 10 minutes to run this query each time.

Occasionally, while Access is chewing on the query, I change my mind and
want to cancel it. Typically, in Access, you do this with CTRL+BREAK. But
in this example (here's the problem) pressing CTRL+BREAK gave me an error
message "Object invalid or no longer set". I got this message once *for
each record*. So far, the only ways I could get out of this was to hold the
ESC key down for like a half hour, or kill it with the task manager.

Since the error messages never caused my even procedure to pop up in the
debugger, I assumed that they were not coming from my code, but from inside
the bowels of Access.

Today, just out of frustration, I added an On Error Resume Next to the event
procedure, and that solved it. I share it here now because the issue was SO
annoying and the solution is not obvious.
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Douglas J. Steele]

Today, just out of frustration, I added an On Error Resume Next to the event
procedure, and that solved it. I share it here now because the issue was SO
annoying and the solution is not obvious.

Sometimes that's a little drastic. You might want to determine the error
number for the specific error, and simply ignore it:

On Error GoTo EH



End_Routine:
Exit Sub

EH:
Select Case Err.Number
Case 1234 ' or whatever the error is
Resume Next
Case Else
Msgbox Err.Description & " (" & Err.Number & ")", _
vbOkOnly + vbCritical
Resume End_Routine
End Select

 

[Mike Labosh]

On Error GoTo EH

End_Routine:
Exit Sub

EH:
Select Case Err.Number
Case 1234 ' or whatever the error is
Resume Next
Case Else
Msgbox Err.Description & " (" & Err.Number & ")", _
vbOkOnly + vbCritical
Resume End_Routine
End Select

Right, I know, but in this case, I really don't care. This is a utility I
made for myself to use. It's not an "application" and no one but me uses
it.

The thing I was interested in pointing out, is that in this scenario, it was
getting an error but not popping up the debugger, so it's not obvious that
an error handler would trap it. Maybe I need to check service packs.
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Douglas J. Steele]

Right, I know, but in this case, I really don't care. This is a utility I
made for myself to use. It's not an "application" and no one but me uses
it.

The thing I was interested in pointing out, is that in this scenario, it was
getting an error but not popping up the debugger, so it's not obvious that
an error handler would trap it. Maybe I need to check service packs.

If On Error Resume Next fixed the problem, then I would say that it's a
trappable error. On Error works with the standard VBA Err object.