Backing up Linked Tables in MS Access

[CharlieMike73]

Hi,

Can someone please tell me how I can back up a MS Access D-Base where all the tables are Linked Tables (comming from an Oracle 8i Server).

What I need to do is use Access to just back up the data and table structure for all tables in that oracle instance.
However we are not able to use Oracle to do this because the table allocations are set for future growth and we don't want to back the unused space up at this time (Oracle Admins will understand this - other people need not worry about it).

Brief:
We have an oracle server that holds all the tables and data,
we then have a MS Access DB that we use as a front end.

I want to copy all the linked tables from the Access Front End to a new Access Database that will be used for backup storage only.

The backup table must containe all the data locally (i.e. copy all tables and data from the Oracle Server and store it in the Access DB).

There are over 150 tables so doing a select * from <table name> and saving it as a 'Make Table' query is not really an option because then i either have to have over 150 Backup queries in the Front End - or I have to recreate them each time.

There has to be a way, I only ask that one of you who knows shares this knowledge with me and save me having to do the above mentioned practise.

I tried Export, and saved the results to a new DB, but this just exported the linked tables instead of making them local tables.

(

Regards,
Charlie

 

[THoey]

I am not 100% positive, but I get the feeling you may have to create another front end (your backup MDB), link all the tables again, create duplicate tables, and then insert the Oracle data into the Access tables with INSERT statements. That way, you write all of your statements one time, and they are not stored in your production front end.

Sorry if that doesn't help. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[scking]

This is just a little DAO code to give you the idea. Obviously you would still need to save the recordset data to other tables. This same this is as easy or easier in ADO.

Steve King

Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim i As Integer
Dim j As Integer
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
For i = 0 To db.TableDefs.Count - 1
Set tbl = db.TableDefs(i)
If tbl.Connect <> &quot;&quot; Then
If VerifyLink(tbl.Name) Then
strSQL = &quot;SELECT * FROM &quot; & tbl.Name & &quot;;&quot;
set rst = db.OpenRecordset(strSQL)
End If
End If
Next i
Growth follows a healthy professional curiosity

 

[BSman]

If you have multiple users and want to only give backup control to one or a limited number of users/administrators, then I would suggest creating a second Access front end that would only be used for backup (and other administrative tasks).

Basically, just create one or more Access back end databases to hold tables set up exactly as they are in Oracle. (You may have to be careful with dates, since Oracle is very weird in the way it likes to handle dates.) Depending upon the size of your Oracle database and the tables, you may need to create multiple Access back end databases, one for each of the larger tables, since Access can only go up to one or two gig (depending upon the version of Access) at most.

But you can link to more than one Access back end database, so you can get around some of the size limitation problem.

Once you have your Access &quot;backup&quot; back end database(s), link to every one of the databases as well as your Oracle working database.

The basic procedure will involve deleting all records in every backup Access database, compacting every one of the backup databases, then writing code in your Access administrative front end that just appends the data from each Oracle table into the appropriate linked table in an Access backup back end.

It actually sounds more involved than it is.