Import an Access table from a different Access database

[emik]

Here is the ideal scenerio:

The user clicks "Import" it opens up the table import window. The user selects "Table1" to import, when they do I want it to overwrite the existing "Table1" (each month this import needs to be done and it will always have the same name).

To import I'm using:
DoCmd.RunCommand acCmdImport

Right now when I import it shows up as "Table11". So how can I get the import to overwrite the table? (Or can I automatically delete the table through code before the import?).

Thanks.

 

[rubbernilly]

If the import is working (but giving you the incremented name), then delete the table before hand.

currentdb.TableDefs.Delete "YourTableNameHere"

You might want to pay attention to any links built on the table fields.

 

[AlexCuse]

If all tables to be imported have the same layout, you could use DoCmd.RunSQL to delete all records from Table1, then import new records. I am not sure if there is a way to automate table deletion (or 'dropping') through Access. If there is I would be very interested to know.

HTH,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

And there it is! Thanks rubbernilly!


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[emik]

Hi,

Thanks for the reply.

This table has no relationships to the others (except through code) that's why I need to keep the name consistent.

The other thing i have to be careful about is if the user imports the wrong table. I don't want my database to start getting junk tables so I need to test the name of the table the user is trying to import.


I'm using this code to check if a table exists:


Function TableExists(TableName As String) As Boolean
On Error GoTo ExitCode

Dim strTableNameCheck As Recordset

'try to assign tablename value
Set strTableNameCheck = CurrentDb.TableDefs(TableName)

'If no error and we get to this line, true
TableExists = True

Exit Function

ExitCode:
TableExists = False
On Error Resume Next
Exit Function

End Function


Then have the condition to delete the table:

If TableExists("Table1") Then
CurrentDb.TableDefs.Delete ("Table1")
End If

I might be over complicating what I need to do. Is there a way to capture the name of the table they selected to import?

 

[rubbernilly]

Is there a way to capture the name of the table they selected to import?

Not that I know of if you are using the built-in import dialog.

Alternately, you could build a form with a listbox in the main database. Have the user select the database that you want to import from (or have it be a known entity, like:

CurrentProject.Path & "\Rollout Changes.mdb"

Open that database through DAO, iterate the TableDefs into the listbox list, and then let the user select which ones they want to enter.

For each one, you would then step through the TableDefs collection of the main database, checking each for a name conflict. If there is a conflict, delete the table (possibly after a confirm - or not, if you want). Then, knowing there is no conflict, import the new one.

 

[PHV]

Why not simply use the DoCmd.TransferDatabase method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emik]

Hi PHV,

I'm not familiar with that command

 

[rubbernilly]

TransferDatabase would do the import, but not the deletion of the original, as far as I know.

Which is why I said whatever method he is using for the import, if it works and the deletion is the only piece missing, he would need the code provided.

 

[emik]

I think the best way would be to use:

CurrentDb.TableDefs.Delete ("Table1")

To delete the table and then use the transfer database method to import it. I think the benefit of the transfer database is it's all automated so the user does not have to select the actual table (thus preventing the wrong tables from being imported). The only thing required would be to have a default folder on the network where the new database is stored so I can link to that location.

Thanks for all the great suggestions and help.

 

[fneily]

Just a very, very, small concern. When you delete a table, that "space" it occupied is NOT reused. Then you create a new table. Your Database will grow out of control if you keep this up. Open your database, click File - Database Properties - then the General Tab and look at its size. Then Click Tools - Database Utilites - Compact and Repair. After the defragmentation, check the size again. Then start deleting and creating and watch how it blows up.

 

[emik]

Well the import would only be done once a month and I have the option "Compact and Repair Database on close", so that should keep things under control.

 

[NvrSaDi]

You can use a 3-step process with macros or code.
1) import the new table using macros which comes thru(hopefully) with a number 1 added if there is an existing table with the same name.
2) delete the first table
3) rename the imported table.

First potential problem is that if the macro crashes for some reason, it may delete the original table and crash the macro. Same potential problem if you try to delete a table that does not exist.

Second potential problem is if you already have a copied table which has been not been deleted, the import process will add a second 1 to the imported table name which subverts the process.

Before running the marco, I call a "for each" procedure to test for the existance of the original table and then to recreate a dummy table with the same name if it is missing. I then use a "for each" procedure to test for a copied record with a 1 and delete it before running the import macro.

I use this for nightly imports of paradox tables into Access 2000 with 25 tables using the tablenames as a source table and a "for" loop.

This gives me a reliable system.
You can do with macros or, if you have multiple tables to import, convert the macros to vba code.