Database Search

match allmatch any Look in all areasLook in current areas Check all datesPrevious dayPrevious 3 daysPrevious weekPrevious fortnightPrevious monthPrevious 2 monthsPrevious 3 monthsPrevious half-yearPrevious year

TechHome

• Access 2000 Advanced DML Transactions
• Access Recordset to CSV File UDF
• Access RunCommand Constants
• ACCESS Table or Query Exists
• Access to Excel via SQL
• Access Visible Problem
• Adding Fields to an Existing Access Table
• Advanced DML Transactions
• Advanced SQL in the Jet 4.0 Provider Example
• Another OLE with Access Example
• Automating Access Tutorial
• Check if Access Table Exists
• Commit Rollback Begin Trans Updatebatch
• Control Access Forms
• Count Access Records
• Csv to Access
• Dealing with ACCESS 97 Field Types
• execute a macro in Access
• Execute a Public function in Access
• Export Access Report in Choice of Formats
• Export MDB to Flat File
• Find Read and Insert Data in Access
• Gather ACCESS Table and Column Information Into Excel
• Get Access Version That Created MDB File
• Get Column Data From an Access Database
• Get Record Count
• How to Use the OpenTable method
• Launch Access Macro from Commandline
• Loop thru a recordset in Access
• Office 2003 Macro Security Issue
• OLE and Access
• OLE Import a table into Access
• OLE Import Table into Access
• Open Access DB with DAO
• Pack an Access Database
• Query and Export Data from Microsoft Access
• Read Write Delete Tables from Access DB
• Repair MDB
• Retrieve Data in Access
• SQL UPDATE Fails with Access
• UDF to Return Record Count
• Updating Table From Filtered Recordset
• Ways to Get Data from and Access file without Access installed

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Access Visible Problem

---

Question:

I'm sure this has been done, and I *thought* there was a tutorial, but I can't seem to locate it. Anyway, all I'm looking to do is delete an existing table, and import another one. A shove in the right direction would be great.

Answer:

OLE Import a table into Access

;   setup constants...

acTable = 0  
acImport = 0

SourceMDBName        = "C:\Data\Access\LogFile.mdb"
DestinationMDBName   = "C:\Data\Access\Naruni.mdb"

SourceTableName      = "Log Table"
DestinationTableName = "New Log Table"

;   open access...
accessDB = ObjectOpen("Access.Application")
ADB = accessDB.Application
dbname = DestinationMDBName
ADB.OpenCurrentDatabase(dbname)
ADB.Visible = @FALSE
ADB.UserControl = @FALSE
;   reference the DOCmd method...
ADBCmd = ADB.docmd

;   set up the ref to tabledefs collection...
db = accessDB.currentdb
tdefs = db.tabledefs
tlist = @FALSE
;   loop thru the table defs...
For t = 0 To tdefs.count-1
   ; setup the ref for this table in the collection...
   table = db.tabledefs(t)
   ;   check to see if the table already exists...
   If table.name == DestinationTableName
      tlist = @TRUE
      Break
   EndIf
   ObjectClose(table)
Next

;   if table exists delete it...
If tlist Then ADBCmd.DeleteObject(acTable, DestinationTableName)
;   this will copy the whole table from the source to the destination...
ADBCmd.TransferDatabase (acImport, "Microsoft Access", SourceMDBName, acTable, SourceTableName, DestinationTableName)

Drop(tlist)
ObjectClose(tdefs)
ObjectClose(db)
ObjectClose(ADBCmd)
ObjectClose(ADB)
ObjectClose(accessDB)

Message("Debug", "All Done")

Exit

---

In ADO you could use:

DELETE * FROM Table1

INSERT INTO Table1 SELECT * FROM Table2 [ IN database ]

I believe if you set Errormode off prior to the Delete Statement, then set it back on after, you will not have to check that the table exists.

Notes: The IN Keyword is MSAccess specific, and it allows you to directly import into a table from either another Access Table or a supported ISAM.

INSERT INTO TABLE1 SELECT * FROM filename IN [dBase IV;Database=C:\temp];

if both the dBase IV file and TABLE1 had the same structure; if you were doing it from a text file you would have an entry in schema.ini - or it works with Excel or HTML.

Important NOTE: after Office 2000, Microsoft turned off all ISAM support except read-only for the Paradox/dbase ISAMS. So if you were using the Jet 4.0 Provider with Extended Properties=dBase III, you cannot update; you either have to install the BDE (Borlan Database Editor??) or use a workaround.

---

Article ID:   W16099

File Created: 2004:03:30:15:42:52

Last Updated: 2004:03:30:15:42:52