Trying to use Microsoft Access to create a backup of a database accessed through ODBCAD connection

[xearow]

Hey guys, like the title says I have an ODBCAD connection to a database that I want to be able to back up regularly.

 

Right now, I am able to use access to get data from that odbcad connection, then link all of the tables. Once all of the tables are linked, I create a Query for each linked table that will create a local -unlinked tabled from that linked table. The problem is, there are a lot of tables and sometimes these tables can change. Additionally, there are multiple databases that need backed up.

So basically, I am trying to find a way to circumvent needing to go through and create all of these Query's and instead have a more automated process of backing up all of the tables from these connections.

 

I am thinking there should be some way to do this in VBA where it runs through a loop going through all of the linked tables and systematically creating query's for them. There might also be another way entirely to do this that I have not considered. Please let me know what the most viable route to backing this up would be- and what direction I should be looking.

 

Also, let me know if this post makes minimal sense because I do not often use access.

[Franck]

Are you talking about the PCB files. If yes those files can be humongous andi doubt VBA will handle those in reasonable time. Why don't you just backup the actual file ?

[JacobFW]

One thing to point out is that in addition to backing up the actual data, you also need to backup the database schema, a.k.a the table columns, types, defaults, any indexes and user functions that were created as well.  Some dbms will have a way to create scripts that let you recreate the actual tables, or the entire database, thus taking all the work for you.  Only some of them however provide a way to make those scripts automatically. 

 

Regarding the actual data, nearly database engines will provide a way for you to list the list of tables, as well as the list of columns in each table.  You may or may not need the list of columns, as once you have the table name, you can usually just go:

SELECT *
FROM <table_name>

To get all the data in that data.

Note though, whenever you run the query, it will usually return the list of field names with the query results, but I have seen some databases that don't do this.

 

While more involved, another method might be too setup database mirroring/synchronization, where anytime a change is made to one database, it gets updated within a few seconds to a few minutes in the other database.  This will depend upon your particular db though.