Linked Tables - How to Close External Database?

[newsgroups]

I have a report that within the Report_Open event, I create a
temporary database and create a linked table to it (using the code
fragment below).

Dim tdfNew As TableDef
Dim RS As Recordset
Dim wrkDefault As Workspace
Dim dbsTemp As Database

Dim strTableName As String

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Make sure there isn't already a file with the name of the new
database.
If Dir(mstrTempDatabase) <> "" Then Kill mstrTempDatabase

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(mstrTempDatabase,
dbLangGeneral)

' Delete the link to the temp table if it exists
If TableExists(mstrTableName) Then CurrentDb.TableDefs.Delete
mstrTableName

' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(mstrTableName)
With tdfNew
.Fields.Append .CreateField("Resource Name", dbText)
.Fields.Append .CreateField("Profile", dbMemo)
dbsTemp.TableDefs.Append tdfNew
End With

dbsTemp.TableDefs.Refresh

' Link to the Import tables in the temp MDB
Dim tdfLinked As TableDef
Set tdfLinked = CurrentDb.CreateTableDef(mstrTableName)
tdfLinked.Connect = ";DATABASE=" & mstrTempDatabase
tdfLinked.SourceTableName = mstrTableName
CurrentDb.TableDefs.Append tdfLinked

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow


Now the problem. I can delete the linked table, but the other
temporary database is still open and I cannot delete it. It is
released about 5-10 seconds after the report has been closed.

Can someone help me on forcing a close on this report so I can delete
the temporary file.

Thanks in advance.

 

[Jim Burke in Novi]

Why aren't you just creating a temporary table in the application DB?

 

[Klatuu]

I'm with Jim on this. I see absolutly no reason to do what you are doing.
I would suggest you maintain a static table in your back end and just delete
any data in it prior to running the report. The only reason not to do that
would be if different users may be running the same report and could mix data
in the table. Then it might be better to maintain such a table in the front
end. Avoid make tables and deleting tables in a front end; however, because
in an mdb environment it can cause bloat and in an mde environment it will
not work.

 

[bandwood1]

I'm with Jim on this.  I see absolutly no reason to do what you are doing.
I would suggest you maintain a static table in your back end and just delete
any data in it prior to running the report.  The only reason not to do that
would be if different users may be running the same report and could mix data
in the table.  Then it might be better to maintain such a table in the front
end.  Avoid make tables and deleting tables in a front end; however, because
in an mdb environment it can cause bloat and in an mde environment it will
not work.

I understand your points, but I really do need to do this. I also
understand the bloating. This is why it is a linked table that only
consumed about 5Kb per instance. The linked table gets reused so there
is no bloating and the temporary mdb getsa deleted - If I can get it
to work.

So, I ask, can you help with why the external databasde stays open and
how to close it.

 

[bandwood1]

I have a report that within the Report_Open event, I create a
temporary database and create a linked table to it (using the code
fragment below).

    Dim tdfNew As TableDef
    Dim RS As Recordset
    Dim wrkDefault As Workspace
    Dim dbsTemp As Database

    Dim strTableName As String

    ' Get default Workspace.
    Set wrkDefault = DBEngine.Workspaces(0)

    ' Make sure there isn't already a file with the name of the new
database.
    If Dir(mstrTempDatabase) <> "" Then Kill mstrTempDatabase

    'Create a new temp database
    Set dbsTemp = wrkDefault.CreateDatabase(mstrTempDatabase,
dbLangGeneral)

    ' Delete the link to the temp table if it exists
    If TableExists(mstrTableName) Then CurrentDb.TableDefs.Delete
mstrTableName

    ' Create the temp table
    Set tdfNew = dbsTemp.CreateTableDef(mstrTableName)
    With tdfNew
        .Fields.Append .CreateField("Resource Name", dbText)
        .Fields.Append .CreateField("Profile", dbMemo)
        dbsTemp.TableDefs.Append tdfNew
    End With

    dbsTemp.TableDefs.Refresh

    ' Link to the Import tables in the temp MDB
    Dim tdfLinked As TableDef
    Set tdfLinked = CurrentDb.CreateTableDef(mstrTableName)
    tdfLinked.Connect = ";DATABASE=" & mstrTempDatabase
    tdfLinked.SourceTableName = mstrTableName
    CurrentDb.TableDefs.Append tdfLinked

    CurrentDb.TableDefs.Refresh

    RefreshDatabaseWindow

Now the problem. I can delete the linked table, but the other
temporary database is still open and I cannot delete it. It is
released about 5-10 seconds after the report has been closed.

Can someone help me on forcing a close on this report so I can delete
the temporary file.

Thanks in advance.

See my other reply.

 

[Klatuu]

No, I'm sorry, but you don't have to do this. It is just your choice to do
it this way. I can think of 3 other ways that would be better, but to answer
your question, you need to use the DeleteObject method to drop the link to
the table. Then use the Close method on the database object.

 

[Marshall Barton]

I understand your points, but I really do need to do this. I also
understand the bloating. This is why it is a linked table that only
consumed about 5Kb per instance. The linked table gets reused so there
is no bloating and the temporary mdb getsa deleted - If I can get it
to work.

So, I ask, can you help with why the external databasde stays open and
how to close it.

You close the database by using the Close method:

dbsTemp.Close
Set dbsTemp = Nothing

But that should be done at the end of the procedure that
creates the database and table. (There is no need for the
temp db to remain open while the report is running.)

After the report closes, you can use the Kill statement to
delete the file. Opening the report in dialog mode may be
needed to sync the code that opens the report with the
report closing. OTOH, it's a heck of lot easier to delete
the file just before you create it.

But, in general I agree with the other responders that it is
extremely rare to have a need for a temp table. Off the top
of my head, the only reasons I can think of are that the
report and its record source query don't get along with each
other or if a query can not collect the report's data
because the data values are calculated in code.

 

[bandwood1]

I understand your points, but I really do need to do this. I also
understand the bloating. This is why it is a linked table that only
consumed about 5Kb per instance. The linked table gets reused so there
is no bloating and the temporary mdb getsa deleted - If I can get it
to work.

So, I ask, can you help with why the external databasde stays open and
how to close it.

You close the database by using the Close method:

        dbsTemp.Close
        Set dbsTemp = Nothing

But that should be done at the end of the procedure that
creates the database and table.  (There is no need for the
temp db to remain open while the report is running.)

After the report closes, you can use the Kill statement to
delete the file.  Opening the report in dialog mode may be
needed to sync the code that opens the report with the
report closing.  OTOH, it's a heck of lot easier to delete
the file just before you create it.

But, in general I agree with the other responders that it is
extremely rare to have a need for a temp table.  Off the top
of my head, the only reasons I can think of are that the
report and its record source query don't get along with each
other or if a query can not collect the report's data
because the data values are calculated in code.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

The reports values are calculate in code. The report contains a sub-
report that uses these calculated values. These calculated values are
weritten to the temp table.

dbsTemp.Close
Set dbsTemp = Nothing

are used when the table is created and everything works fine there.

The issue is when the sub-report runs it opens the table and then
opens and locked the temp database. I do not have a handle to it to do
the .Close bercaue it is opened by the Access runtime because the
report is using the linked table.

So, when the report is closing (report_close event) I can delete the
temp linked table, but cannot delete the datbase because Access still
has it opened.

I hope this explains it better.

Dennis.

 

[bandwood1]

No, I'm sorry, but you don't have to do this.  It is just your choice to do
it this way. I can think of 3 other ways that would be better, but to answer
your question, you need to use the DeleteObject method to drop the link to
the table.  Then use the Close method on the database object.
--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

Ok, it my choice - but I would welcome your 3 other suggestions.

Tried "DoCmd.DeleteObject acTable, mstrTableName" and it does delete
the table but the linked external database is still open and locked.

so "Kill mstrTempDatabase" fails.

The lock on the external database is not released until the report
actually closes - please refer to my other response.

 

[Marshall Barton]

I'm with Jim on this.  I see absolutly no reason to do what you are doing.
I would suggest you maintain a static table in your back end and just delete
any data in it prior to running the report.  The only reason not to do that
would be if different users may be running the same report and could mix data
in the table.  Then it might be better to maintain such a table in the front
end.  Avoid make tables and deleting tables in a front end; however, because
in an mdb environment it can cause bloat and in an mde environment it will
not work.

I understand your points, but I really do need to do this. I also
understand the bloating. This is why it is a linked table that only
consumed about 5Kb per instance. The linked table gets reused so there
is no bloating and the temporary mdb getsa deleted - If I can get it
to work.

So, I ask, can you help with why the external databasde stays open and
how to close it.

You close the database by using the Close method:

        dbsTemp.Close
        Set dbsTemp = Nothing

But that should be done at the end of the procedure that
creates the database and table.  (There is no need for the
temp db to remain open while the report is running.)

After the report closes, you can use the Kill statement to
delete the file.  Opening the report in dialog mode may be
needed to sync the code that opens the report with the
report closing.  OTOH, it's a heck of lot easier to delete
the file just before you create it.

But, in general I agree with the other responders that it is
extremely rare to have a need for a temp table.  Off the top
of my head, the only reasons I can think of are that the
report and its record source query don't get along with each
other or if a query can not collect the report's data
because the data values are calculated in code.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

The reports values are calculate in code. The report contains a sub-
report that uses these calculated values. These calculated values are
weritten to the temp table.

dbsTemp.Close
Set dbsTemp = Nothing

are used when the table is created and everything works fine there.

The issue is when the sub-report runs it opens the table and then
opens and locked the temp database. I do not have a handle to it to do
the .Close bercaue it is opened by the Access runtime because the
report is using the linked table.

So, when the report is closing (report_close event) I can delete the
temp linked table, but cannot delete the datbase because Access still
has it opened.

I still say that you should avoid all these issues and kill
the temp db file the next time you want to create it.

 

[david]

Why delete the temp database? I always leave it in place
until the next time, and delete at the start of the next process.

If the next process follow immediately, why not use the
same temp database? I use the same temp database for
all the actions. The deletion takes place next week.

If you use the temp file api (search at www.mvps.org/access/)
then you get a new filename each time. When I do my
deletion, I delete all the temp filenames that match my
template file name, and a start with a new file name
using the temp filename API.

(david)

 

[david]

other or if a query can not collect the report's data

because the data values are calculated in code.

Just use user defined functions in the query. The problem
is when values are calculated in code AND there is
complex counting and summation in the query AND
there are many-many joins.

The complexity adds up to where you can't get it to work,
and even if you can, you can't see what you are doing.

That said, my summation temp tables are still in the FE.
The temp databases are used for large imports.

(david)