(Access 2.0 on Win 3.11)

Hi,

I've asked this before but saw no response, so I thought I'd try again.

I have a large database to which I have to add one or more new tables a
month (as well as "overwrite" existing tables) which are imported from
fixed-length text files.  What I'm working on is some code to automate
this.  Here are the steps:

1.  Open the database from which my attached tables in the current
database originate. (No problem there)

2.  Use ObjectExists to find out if a table with the same name as my text
file exists and, if so, delete the table from Access.  (Only problem with
this is that it searches the current database, not my main database.)

3.  Import the delimited text file(s).  (Except it imports them to the
current database, not the main one.)

4.  If the table is new, attach it to the current database.  (Wishful
thinking: It never gets to this point.)

The only way I can see around this is to open another copy of Access
using Shell and run the code from within that database, but that seems
clunky and memory-expensive.  Does anyone have a better mousetrap?

TIA,

Laura Wilson

I don't know what you mean by "current" and "main" exactly? I assume
"current" is the database from where you launch the code but what is
"main", is that where the attached tables are?

It sounds as though you want to manipulate objects in multiple
databases.

I found Access confusing in this respect for a long time because there
is a big difference between "Data Access Objects" and the "standard"
Access interface.

To get good functionality with multiple databases you may wish to look
into using Data Access Objects in code, only problem is, it's often
pages of code even to achieve something rather simple. I use it all the
time, however, because it's the only way I know to build decent Database
Apps.

When using Access without code, you don't get much facility for working
with multiple databases. Most of the "DoCmd" type functions only work in
reference to the current open database.

One possible idea (without using DAO); what if you wrote and launched
the code from the database which holds the attached tables? That way you
could use all the "current" database Inport/Export Commands?
--
Gerry Hickman (London UK)
http://www.users.dircon.co.uk/~gerry1/

I was hoping for a more elegant solution, as this is rather clunky and
memory expensive.

Thanks,

Laura Wilson

Laura,

From the MS Access help there are some things that would point you how to
do what you want.  I havn't done this exact process but from what I know
and read it looks like what you want.  You can use DOA to create/delete
tables in a remote database or you could create the table local and then
export it, using code, to the remote database.  You should also import your
data into a temp table then append it to the attached tables using SQL.
SQL appears to run much faster than DOA.

Hope this points you in the right direction.

James Dyer
HyperGen Inc.

See Also        Example 1       Example 2       1.0
Applies To

Recordset objects (dynaset-type and table-type only).  Data access
collections.

Description

Recordset objectsdeletes the current record in an open dynaset-type or open
table-type Recordset object.
        Collectionsdeletes a stored object from a collection.

Syntax

recordset.Delete
collection.Delete objectname

Remarks

Uses of the Delete method include deleting the current record of a
Recordset, deleting a stored table from a database, deleting a stored field
from a table, and deleting a stored index from a table.
When used with a Recordset object, the Delete method uses this argument.

Argument        Description

recordset       A variable of an object data type identifying an open
dynaset-type or open table-type Recordset object containing the record you
want to delete

When you delete records from a Recordset, there must be a current record in
recordset
 before you use Delete; otherwise, an error occurs.  To make a record
current, set the Bookmark property or move to the desired record using one
of the Move methods, one of the Find methods (for dynaset-type Recordset
objects only), or the Seek method (for table-type Recordset objects only).
In a table-type Recordset object, Delete removes the current record.  In a
dynaset-type Recordset object, Delete removes the current record and makes
it inaccessible.  Although you can't edit or use it, the deleted record
remains current.  What was the next record remains the next record, and
what was the previous record remains the previous record.  Once you move to
another record, however, you can't make the deleted record current again.

Subsequent references to a deleted record in a table are invalid and
produce an error.
You can undelete a record if you use transactions and the Rollback method.
When used with a collection, the Delete method uses these arguments.

Argument        Description

collection      The name of a collection
objectname      A String that is the Name property setting of an existing object
in collection

An object that is appended to a collection is a persistent object, stored
on disk, until you delete it using the Delete method.  If the collection is
a Databases, Recordsets, or Workspaces collection (each of which is stored
only in memory), you remove an open or active object by closing that object
using the Close method.
The deletion of a stored object occurs immediately, but you should use the
Refresh method on any other collections that may be affected by changes to
the database structure.  
When you delete a TableDef object from the TableDefs collection, you delete
the table definition and the data in the table.
The following table shows some limitations on the use of the Delete method.
 In each row, the first column is an object that contains the collection in
the second column.  The third column indicates when, if ever, you can
delete an object from that collection (for example, you can never delete a
Container object from the Containers collection of a Database object).

Object  
Collection      When you can delete
DBEngine        Workspaces      Never; use the Close method instead
Workspace       Databases       Never; use the Close method instead
Database        Containers      Never
Database        Recordsets      Never; use the Close method instead
Container       Documents       Never
Index   Fields  Never
QueryDef        Fields  Never
QueryDef        Parameters      Never
Recordset       Fields  Never
Relation        Fields  Never
TableDef        Fields  When the Updatable property of the TableDef is set to True
TableDef        Indexes When the Updatable property of the TableDef is set to True
Database, Field, Index, QueryDef, TableDef      Properties      When the property is
user-defined

Append Method, Delete Method Example

This example defines a new field called FAX Phone and appends it to the
Fields collection of the Employees table in the TableDefs collection of the
NWIND.MDB database.  After the field is used, it's deleted.

Dim MyWorkspace As Workspace, MyDatabase As Database
Dim MyField As Field, MyTableDef As TableDef
Set MyWorkspace = DBEngine.Workspaces(0)                        ' Get current workspace.
Set MyDatabase = MyWorkspace.OpenDatabase("NWIND.MDB")   ' Open database.
Set MyTableDef = MyDatabase![Employees]
Set MyField = MyTableDef.CreateField("FAX Phone")
' Set field properties.
MyField.Type = DB_TEXT
MyField.Size = 24
MyTableDef.Fields.Append MyField        ' Append field to collection.
...
MyTableDef.Fields.Delete "FAX Phone"  ' Delete field from collection.

This example creates a TableDef object in the current database, sets its
Connect, Name, and SourceTableName properties, and then appends it to the
TableDefs collection to attach it.

Function ConnectSource () As Integer
        Dim MyWorkspace As Workspace, MyDatabase As Database
        Dim MyTableDef As TableDef
        Dim I As Integer
        Set MyWorkspace = DBEngine.Workspaces(0)        ' Get default workspace.
        Set MyDatabase = MyWorkspace.Databases(0)       ' Get current database.
        Set MyTableDef = MyDatabase.CreateTableDef("PDXAuthor")
        ' Attach Paradox table Author in database C:\PDX\PUBLISH.
        MyTableDef.Connect = "Paradox 3.X;DATABASE=C:\PDX\PUBLISH"
        MyTableDef.SourceTableName = "Author"
        MyDatabase.TableDefs.Append MyTableDef  ' Attach table.
        ConnectSource = True
End Function