Crash on form load (DAO link problem?)

[Wild Bill]

The following Form Load code is murdering me in A2000 SR-1 on W2000 SP4
- "msaccess.exe has generated errors and will be closed" (M.S.
developers, you bastards...)

I say it's the form load code because from a stable DB, I added the
code, did a compact (and the form reloads automatically following
compact) - then BOOM. COMMENTING THE FUNCTION CALL THEREIN seems to
prevent the crash (but I'm fuzzy there - I fear corruption).

A possible culprit is an unusual tactic where I used a Table link within
the same database (it was tricky to even come up with such) - it is
called LinkAlias below. Another worry is the use of DAO360 - I did try
regsvr32 to no avail. jetcomp.exe didn't help (didn't bark either).

Any tips appreciated.

Private Sub Form_Load()
Dim i As Integer, sStr As String, sStrDefault As String, iListCount As
Integer
Dim obj As AccessObject, dbs As Object, iSelectThisOne As Integer
sStrDefault = Mid(lstbExpTable.DefaultValue, 2,
Len(lstbExpTable.DefaultValue) - 2)
sStr = """"
Set dbs = Application.CurrentData
iListCount = 0
For Each obj In dbs.AllTables ' Search for open AccessObject objects
in AllTables collection.
If Left(obj.Name, 7) = "tblmort" Then
If iListCount > 0 Then sStr = sStr & """" & ";" & """"
sStr = sStr & obj.Name
iListCount = iListCount + 1
If obj.Name = sStrDefault Then
iSelectThisOne=iListCount-1 'only way I know to identify
selected listbox item
End If
End If
Next obj
Set dbs = Nothing
Me.lstbExpTable.RowSource = sStr & """"
lstbExpTable.Selected(iSelectThisOne) = True
Call SetNewExpectedTable
lstRptNames.Selected(0) = True
End Sub
Private Sub SetNewExpectedTable()
Dim rs As DAO.Recordset, sStr As String
On Error Resume Next
CurrentDb.TableDefs.Delete "LinkAlias"
On Error GoTo 0
If Left(lstbExpTable, 1) = """" Then
sStr = Mid(lstbExpTable, 2, Len(lstbExpTable) - 2)
Else
sStr = lstbExpTable 'having trouble w/ hard double quotes in strings
End If
Set rs = CurrentDb.OpenRecordset(sStr)
rs.MoveFirst
DoCmd.TransferDatabase acLink,"Microsoft Access",CurrentDb.Name,
acTable, sStr,"LinkAlias"
rs.Close
Set rs = Nothing
End Sub

 

[Dirk Goldgar]

The following Form Load code is murdering me in A2000 SR-1 on W2000
SP4 - "msaccess.exe has generated errors and will be closed" (M.S.
developers, you bastards...)

I say it's the form load code because from a stable DB, I added the
code, did a compact (and the form reloads automatically following
compact) - then BOOM. COMMENTING THE FUNCTION CALL THEREIN seems to
prevent the crash (but I'm fuzzy there - I fear corruption).

A possible culprit is an unusual tactic where I used a Table link
within the same database (it was tricky to even come up with such) -
it is called LinkAlias below. Another worry is the use of DAO360 - I
did try regsvr32 to no avail. jetcomp.exe didn't help (didn't bark
either).

Any tips appreciated.

Private Sub Form_Load()
Dim i As Integer, sStr As String, sStrDefault As String, iListCount
As Integer
Dim obj As AccessObject, dbs As Object, iSelectThisOne As Integer
sStrDefault = Mid(lstbExpTable.DefaultValue, 2,
Len(lstbExpTable.DefaultValue) - 2)
sStr = """"
Set dbs = Application.CurrentData
iListCount = 0
For Each obj In dbs.AllTables ' Search for open AccessObject objects
in AllTables collection.
If Left(obj.Name, 7) = "tblmort" Then
If iListCount > 0 Then sStr = sStr & """" & ";" & """"
sStr = sStr & obj.Name
iListCount = iListCount + 1
If obj.Name = sStrDefault Then
iSelectThisOne=iListCount-1 'only way I know to identify
selected listbox item
End If
End If
Next obj
Set dbs = Nothing
Me.lstbExpTable.RowSource = sStr & """"
lstbExpTable.Selected(iSelectThisOne) = True
Call SetNewExpectedTable
lstRptNames.Selected(0) = True
End Sub
Private Sub SetNewExpectedTable()
Dim rs As DAO.Recordset, sStr As String
On Error Resume Next
CurrentDb.TableDefs.Delete "LinkAlias"
On Error GoTo 0
If Left(lstbExpTable, 1) = """" Then
sStr = Mid(lstbExpTable, 2, Len(lstbExpTable) - 2)
Else
sStr = lstbExpTable 'having trouble w/ hard double quotes in
strings End If
Set rs = CurrentDb.OpenRecordset(sStr)
rs.MoveFirst
DoCmd.TransferDatabase acLink,"Microsoft Access",CurrentDb.Name,
acTable, sStr,"LinkAlias"
rs.Close
Set rs = Nothing
End Sub

I don't have any problem with the TransferDatabase statement itself, in
A2K SP3 or in A2K2 under W2K SP4; not even with a recordset open on the
table I'm linking to, as you have. (Why? What are you actually trying
to do here?) However, I haven't tried putting the code in a form's Load
event yet. Is the form bound to the table you're working with here?

Have you tried setting a breakpoint and stepping through the code to see
what statement is causing the crash? You could also check the values of
variables at different stages to see if they contain what you expect.

Perhaps if you'd explain what you're doing we could figure out another
way to do it, even if we don't solve this specific problem.

 

[Wild Bill]

Single stepping is clean. Shift loading is clean. (The form
autoloads.) But compacting leads to a faulty autoload and problems.

The idea here is to simply vary what a table links to, based on a
listbox choice; thus one linked table name "LinkTable" can be used in
the 12 queries needed for the Report. Cute, in principle (Another
listbox controls which query is the report recordsource.)

I admit I'm coding way over my head; AFA the open dataset goes, that
just came from pecking away to adapt code found on the net. An earlier
thread today pointed to http://www.mvps.org/access/tables/tbl0009.htm so
I may go that way (.connect, .refreshlink). Another thing I've
considered is splitting my tables to another DB so I can vary the link
in a civilized fashion.

Some of the multitude of other problems smelling like corruption:

If I try to remove a module, I get "Network Connection may be lost"
though the whole thing is done on drive C. If I click Help, I get a
blank help screen.

I tried to bind a text box via recordsource to a field from a query on
the linked database (the query is the form's recordsource). After I use
the code earlier in this thread to restate the link (LinkTable), any
combination of txtFoo.requery, me.requery, me.refresh will never update
that textbox! However I can alt-F6 over to the query, who gives a new
result.

I also get messages like "cannot find DB1.mdb." I've got quite a mess,
all in my effort to restate a table link.

 

[Dirk Goldgar]

Single stepping is clean. Shift loading is clean. (The form
autoloads.) But compacting leads to a faulty autoload and problems.

The idea here is to simply vary what a table links to, based on a
listbox choice; thus one linked table name "LinkTable" can be used in
the 12 queries needed for the Report. Cute, in principle (Another
listbox controls which query is the report recordsource.)

I admit I'm coding way over my head; AFA the open dataset goes, that
just came from pecking away to adapt code found on the net. An
earlier thread today pointed to
http://www.mvps.org/access/tables/tbl0009.htm so I may go that way
(.connect, .refreshlink). Another thing I've considered is splitting
my tables to another DB so I can vary the link in a civilized fashion.

Some of the multitude of other problems smelling like corruption:

If I try to remove a module, I get "Network Connection may be lost"
though the whole thing is done on drive C. If I click Help, I get a
blank help screen.

I tried to bind a text box via recordsource to a field from a query on
the linked database (the query is the form's recordsource). After I
use the code earlier in this thread to restate the link (LinkTable),
any combination of txtFoo.requery, me.requery, me.refresh will never
update that textbox! However I can alt-F6 over to the query, who
gives a new result.

I also get messages like "cannot find DB1.mdb." I've got quite a
mess, all in my effort to restate a table link.

Sounds to me like you may have gone about things the wrong way, made
some mistakes, and gotten yourself in a bit deep. BUT the "Error
accessing file. Network connection may have been lost" message on a
non-networked database *is* a sign of corruption, commonly caused by a
specific bug in Access 2000, which is documented in this Microsoft
KnowledgBase article:

http://support.microsoft.com/default.aspx?scid=kb;[LN];304548

Please read that article to determine if you have been bitten by this
bug. Office 2000 SP3 fixes the bug, but that won't repair your
corrupted database. You'll have to resort to a backup, or else try
importing all objects to a new database -- some objects probably won't
import, and you'll have to recreate them from scratch. When you do
import the objects, unless you've applied the service pack, it is
*crucial* that you compile and save the project before closing the
database.

After you've fixed the corruption, if that's what it is, it may be that
what you're attempting may work. However, I wonder about whether you
may be making things harder than they need to be. As I understand it,
you want to change the base table used in a varierty of queries, based
on the item selected in a list box, and you're doing this by creating a
linked table in your database, and linking it to the specific table in
that same database that was chosen by the list box selection. I'm
reasonably certain you could do it that way, but it seems to me that it
would be easier to have a single stored query that is referenced as the
data source for all those other queries, and simply update the SQL
property of that query according to the list box.

For example, suppose you have query named "qryReportBase", and three
tables "TableA", "TableB", and "TableC". Inititially, qryReportBase has
this SQL:

SELECT * FROM TableA;

Your list box has list items "A", "B", and "C", to choose TableA,
TableB, or TableC for reporting. Your list box could have this
AfterUpdate event:

Private Sub lstChooseTable_AfterUpdate()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strTable As String

If IsNull(Me.lstChooseTable) Then
MsgBox "You didn't choose anything yet!"
Exit Sub
End If

Select Case lstChooseTable
Case "A" : strTable = "TableA"
Case "B" : strTable = "TableB"
Case "C" : strTable = "TableC"
End Select

Set db = CurrentDb
Set qdf = db.QueryDefs("qryReportBase")
qdf.SQL = "SELECT * FROM " & strTable & ";"
Set qdf = Nothing
Set db = Nothing

End Sub

Now every query that selects data from qryReportBase will actually be
drawing data from the table that was last chosen in the list box. Does
that solve your technical problem?

Stepping back a bit further yet, the fact that you appear to have
multiple tables with the same (or vary similar) structure, that you have
to choose among for reporting, suggests that you might do much better to
consolidate all these tables into a single table, perhaps adding a field
to distinguish the "logical" table to which each record belongs. Then
either queries could use the list box as a criterion for selecting the
desired set of records from the greater table, or your list box choice
might be used to update a single-record "report control" table with the
desired logical table-type code. By inner-joining the report-control
table to the main table on that field, you would automatically get only
the desired set of records. In my opinion, either of these methods
would be superior to having multiple tables with the same structure, the
only logical distinction between them being the table name.

 

[Wild Bill]

Wow. You spent a lot of time on this, and I won't be able to react and
respond thoroughly for another day. Let me just express now my
gratitude and how impressed I am that you've given me a reply with real
teeth, and I'll grant the accuracy in advance.

Sounds to me like you may have gone about things the wrong way, made
some mistakes, and gotten yourself in a bit deep. BUT the "Error
accessing file. Network connection may have been lost" message on a
non-networked database *is* a sign of corruption, commonly caused by a
specific bug in Access 2000, which is documented in this Microsoft
KnowledgBase article:

http://support.microsoft.com/default.aspx?scid=kb;[LN];304548

Thank you for your attentiveness here!

Please read that article to determine if you have been bitten by this
bug. Office 2000 SP3 fixes the bug, but that won't repair your
corrupted database. You'll have to resort to a backup, or else try
importing all objects to a new database -- some objects probably won't
import, and you'll have to recreate them from scratch. When you do
import the objects, unless you've applied the service pack, it is
*crucial* that you compile and save the project before closing the
database.

Every statement above is very significant - way to go! Indeed I had
mixed success importing to a new DB from a backup. And I was just about
to back up again after the imports, but said, "I'll just compact first"
- arggh! Boom! (I keep generations of backups - often several in a
hard-coding day - so might as well saved them compressed, right?!)

After you've fixed the corruption, if that's what it is, it may be that
what you're attempting may work. However, I wonder about whether you
may be making things harder than they need to be. As I understand it,
you want to change the base table used in a varierty of queries, based
on the item selected in a list box, and you're doing this by creating a
linked table in your database, and linking it to the specific table in
that same database that was chosen by the list box selection.
Exactamundo.

I'm reasonably certain you could do it that way, but it seems to me that it
would be easier to have a single stored query that is referenced as the
data source for all those other queries, and simply update the SQL
property of that query according to the list box.

Well I was intimidated by adding more virgin territory VBA with

For example, suppose you have query named "qryReportBase", and three
tables "TableA", "TableB", and "TableC". Inititially, qryReportBase has
this SQL:

SELECT * FROM TableA;

Your list box has list items "A", "B", and "C", to choose TableA,
TableB, or TableC for reporting. Your list box could have this
AfterUpdate event:

Private Sub lstChooseTable_AfterUpdate()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strTable As String

If IsNull(Me.lstChooseTable) Then
MsgBox "You didn't choose anything yet!"
Exit Sub
End If

Select Case lstChooseTable
Case "A" : strTable = "TableA"
Case "B" : strTable = "TableB"
Case "C" : strTable = "TableC"
End Select

Set db = CurrentDb
Set qdf = db.QueryDefs("qryReportBase")
qdf.SQL = "SELECT * FROM " & strTable & ";"
Set qdf = Nothing
Set db = Nothing

End Sub

Now every query that selects data from qryReportBase will actually be
drawing data from the table that was last chosen in the list box. Does
that solve your technical problem?

Yes, and I concede that this is the "usual" way of addressing it, so
I'll surrender my 'cute' link approach.

Stepping back a bit further yet, the fact that you appear to have
multiple tables with the same (or vary similar) structure, that you have
to choose among for reporting, suggests that you might do much better to
consolidate all these tables into a single table, perhaps adding a field
to distinguish the "logical" table to which each record belongs. Then
either queries could use the list box as a criterion for selecting the
desired set of records from the greater table,

That is sage but it was simpler to just add tables as they were produced
for me externally - i.e., I could instantly _import_ new tables rather
than _integrate_ them with an append query. I'll re-weigh that decision
in light of the clear advantages you

or your list box choice
might be used to update a single-record "report control" table with the
desired logical table-type code. By inner-joining the report-control
table to the main table on that field, you would automatically get only
the desired set of records. In my opinion, either of these methods
would be superior to having multiple tables with the same structure, the
only logical distinction between them being the table name.

By this I understand you to mean that I would set the form recordsource
to the control table - I might be able to handle that.

Again, this was a lot of effort on your part - and it's clear that I'm
just one in a long line. Way to pay forward, guy.

 

[Dirk Goldgar]

Wow. You spent a lot of time on this, and I won't be able to react
and respond thoroughly for another day. Let me just express now my
gratitude and how impressed I am that you've given me a reply with
real teeth, and I'll grant the accuracy in advance.

(I keep generations of backups - often several
in a hard-coding day

Good! With any luck, that practice will have saved you from having to
recreate objects from scratch.

[...] it was simpler to just add tables as they were
produced for me externally - i.e., I could instantly _import_ new
tables rather
than _integrate_ them with an append query.

I understand. Still, you could probably write a VBA proceedure to take
care of both importing and appending -- with the appropriate
type-code -- making the import process just as easy on you, once you get
it set up.

By this I understand you to mean that I would set the form
recordsource
to the control table - I might be able to handle that.

I'm not sure I understand what you're thinking here -- I'm not sure
where the form comes into it -- so both your concept and mine need some
clarification. Get back to me when you get the chance.