Import multiple tables with VBA - overwrite originals. (1 Viewer)

[Dranoweb]

Hi,

I'm currently working on an ongoing project that requires regular updates to the forms and code of a database.

I would like to be able to click a button and import all the tables from the old version to the new one, and OVERWRITE the data.

I may add new tables in between versions, so deleting all tables before import is not an option.

my current code, does almost what i need, but it adds a number on the end, and with 50 or so tables, it's just as time consuming as the manual method.

my current code:

Dim db As Database
Dim tdf As TableDef

    Set db = OpenDatabase("C:\Users\userdir\Desktop\stuff\BACKUPS\main backup.mdb")
    
    For Each tdf In db.TableDefs
    
        DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Users\userdir\Desktop\stuff\BACKUPS\main backup.mdb", acTable, tdf.Name, tdf.Name
    Next tdf
    
    Set db = Nothing

I am assuming I'll probably need to index the table names in a variable somewhere and delete tables mentioned in said variable.

Any suggestions will be appreciated...

 

[jdraw]

It's adding numbers to the newly created tables because the table names would be duplicated otherwise. That's a feature of Access.

I'm not following your requirements

requires regular updates to the forms and code of a database.

Sounds like the requirements have not been finalized and that you are developing incremental versions of something.

Perhaps you should list the things you are trying to do. I strongly suggest you get a good picture of the final structure, build a data model and then ask more specific questions as you progress.

Good luck.

 

[Dranoweb]

Perhaps you should list the things you are trying to do. I strongly suggest you get a good picture of the final structure, build a data model and then ask more specific questions as you progress.

Good luck.

This database is quite complex and the individual tasks many.
I have excess of 200 objects in the database.

It carries a variety of data, and is used to generate statistics required by an overseeing government agency, as hence I have to be careful what I mention about it's workings as I am bound by a privacy agreement.

the main conundrum as it stands is that The database is functional, however the demands regularly change.

I work on the database remotely, less the sensitive data.
When requested features have been added, I need to attend the site, copy over the new database, and then import several tables (about 20) manually from the original database, so that the data will populate the updated database.

What I am trying to do is not for user level use, simply a mildly quicker way to do my regular tasks.

Edit:

I have already considered other ways around this problem but they are not possible. Hence why I ask the question.

 

[the_net_2.0]

what do you mean ''adds a number on the end''???

 

[Dranoweb]

what do you mean ''adds a number on the end''???

It adds a duplicate table, and appends a number to the end of the original table name.
Aafter some research, sparked by a previous poster, I have come to understand that this is an access feature. Quite irritating that there isnt an overwrite option.

 

[DCrake]

What concerns me is that the tabls you want to update have structural changes not just data changes. Is this the norm?

Is it just a case of new fields in tables? or changes to data types? removal of existing fields?

Are new tables added?
Old ones removed?

By the way you are talking it sounds like a very unstable application.

More info about the changes in the structure is advisable.

By the way we are talking about a split application BE - FE arn't we?

 

[the_net_2.0]

Quite irritating that there isnt an overwrite option.

if you rename through the interface, an overwrite dialog pops up. makes me wonder why there isn't an option through code, really.

 

[Andrew Canter]

Hi - I know this as an old post, but it is high on the Google search results, and didn't give me a satisfactory solution.

I came up with this solution that seems to work well:

1. Switch off warnings.
2. Import the table with the same name, but "Copy" at the end.
3. Rename the table - which will overwrite an existing table if the name exists.
4. Repeat ad infinitum, then switch off warnings.

Here's some sample code:
Blue text is comments, Red text needs to be changed to the your database/tables.

The 3 lines that start "TableName=....","DoCmd.Transfer...","DoCmd.Rename..." can be copied and pasted as they are, and you just need to add the name of your additional tables.

Private Sub import_tables_Click()

Dim TableName As String        [COLOR=Blue]'this will be the name of the table to import[/COLOR]
Dim CopyName As String        [COLOR=Blue]'this will be added to the end of table name on import[/COLOR]
Dim DatabaseName As String   [COLOR=Blue]'this will be the name of the database[/COLOR]

DatabaseName = "[COLOR=Red]C:\Folder Address\Your Database.accdb[/COLOR]"     [COLOR=Blue]'or .mdb[/COLOR]
CopyName = "COPY"              [COLOR=Blue]'word added to end of imported table - use different if risk of duplicating actual table names[/COLOR]         

DoCmd.SetWarnings False       [COLOR=Blue]'switch off warnings[/COLOR]

TableName = "[COLOR=Red]Customers[/COLOR]"
DoCmd.TransferDatabase acImport, "Microsoft Access", DatabaseName, acTable, TableName, TableName & CopyName, False
DoCmd.Rename TableName, acTable, TableName & CopyName

TableName = "[COLOR=Red]Products[/COLOR]"
DoCmd.TransferDatabase acImport, "Microsoft Access", DatabaseName, acTable, TableName, TableName & CopyName, False
DoCmd.Rename TableName, acTable, TableName & CopyName

TableName = "[COLOR=Red]Another Table[/COLOR]"
DoCmd.TransferDatabase acImport, "Microsoft Access", DatabaseName, acTable, TableName, TableName & CopyName, False
DoCmd.Rename TableName, acTable, TableName & CopyName

DoCmd.SetWarnings True          [COLOR=Blue]'Switch warnings back on[/COLOR]

End Sub

 

[Dranoweb]

I thank you all greatly for your input and help.
I had totally forgotten about this post.

In the end the "government funded organisation" that I was desigining this system for, was restructured, and the new admin opted for a system utilising cloud storage and based on SAP, with a web interface, much to the outrage of the users.

I ended up making a far more simple database that would simply interpret data and fill out the web forms using a browser macro plugin. This was mostly a stop gap solution as the current system loads a new html page for each section of data.

eg: User enters time, clicks ok, load new page, user enters name, clicks ok, new page loads.

In a rural area, this is highly infuriating on low bandwidth, but you can't fight government and I have abandoned this contract as current management refused to heed any advice and attempted to make me liable for mistakes I advised against.

-end rant

in summary - thankyou all, I have learnt alot from your advice, and I have and will use the knowledge again in future projects.