I'm trying to build a macro to import to MS Access a table from an ODBC database.
I used the "TransferDataBase" method, and it worked well. The problem, is that I want to update periodically (let's say weekly) the data in my Access database. Each time I re-launch the macro, Access adds a number to the name of the table as it conflicts with an existing name (the name of the previous version of the table). As the imported table has relationships with several other tables, that's a big problem.
Is there a way to force Access to replace the table? Won't the existing relationships be deleted? Is there a easier way to update my data from an ODBC db weekly? (I would prefer no to use linked tables).
Import the data into a temporary table that you have made sure is deleted just before the import process. Then copy the data from the temporary table into your "permanent" table. Of course you will need to delete the records from your permanent table before copying the records from the temporary table.