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.
Quote:
> (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.)
Delete Method
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.
Quote:
> 3. Import the delimited text file(s). (Except it imports them to the
> current database, not the main one.)
I would recommend importing them into temporary tables in the current
database then using SQL to append the data to your attached tables.
Quote:
> 4. If the table is new, attach it to the current database. (Wishful
> thinking: It never gets to this point.)
Connect, SourceTableName Properties; TableDef Object Example
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
Quote:
> 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