Transfer my data into another db

[Dina01]

Hello,

I create a db and I have 2 tables that are linked to my db named BackUp.....Everything works fine, but once my records get transfered into my table named BackUp, I need the original db to delete all the record in the tables, I create DELETE query and it works fine, but since my tables are linked to the other table in the BackUp db, well the records are DELETED.....Can anyone please tell me how to do this...

 

[bdmclennan]

Here is a module which will help you.

'==================================
'Goes through all access linked tables
'and creates a new backup table
'===================================

Public Sub subBACKUP()
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type In (6)", dbOpenSnapshot)

With rs
Do While Not .EOF
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT Now() AS ARCHIVE_STAMP, * INTO tblBACKUP_" & !Name & " FROM " & !Name
DoCmd.SetWarnings True
.MoveNext
Loop
End With
End Sub


Hope it helps

B.

 

[dannyocean]

If I am reading this correct. You want to keep the data in the backup and delet all the data in your working copy.

I would create a table and append the data to it. link this table to your backup db. Then append all data from the linked table to your back up dbs table.

Then run a delete query on your main dbs table

Hope this makes sense,

Danny

 

[Dina01]

Hey Danny,

The problem is that whenI runthe DELETE query fromthe main dbs table thenit end up deleting my data from the BackUp copy since the tables are linked

 

[bdmclennan]

Sorry,

I forgot that you want to delete your source data.

add this line AFTER the first DOCMD.RUNSQL

DoCmd.RunSQL "DELETE * FROM " & !Name


B.

 

[Dina01]

Hey B,

I am a little confused with your code, because I have 2 db, one will be the Mian but the other one is a backup...
Therefore what I need to do is that whenI click on a macron on the original I want to transfer the data that I specifeid to the back up table inthe BackUp db...

The problem is that since my tables are linked then when I run my DELETE qyery from the original well it DELETES also the info in the BackUp table.....

Do you see what I mean

 

[Dina01]

I am not too sure if this will help

I have No Relatioship...

I have to delete queried.....

1.
DELETE DataTracking_tb.*
FROM DataTracking_tb;

and
2.
DELETE AgentTrackingModification_tb.*
FROM AgentTrackingModification_tb;

The linked tables are DataTracking_tb and AgentTrackingModification_tb

but when I run my delete queries, I only want to delte the records from the Origianl db named Dispatch and not my records in my db named BackUp...

 

[bdmclennan]

OK,

That code was part of one of my Libaries. It uses a pull technique to archive all tables linked to the db where it is being run.

A quick modification to a push technique and you will be laughing:

You will need to change 'C:\BACKUP\BACKUP.mdb' to the fully qualified path of your back up db.

Each time you run it you get a new archive table. If this is not what you want then do the following:
1. Import(not link) your tables into your backup mdb
2. Add an Archive_timestamp field to each of your tables
3. Delete the data out of these tables
4. Change the sql in the first docmd.runsql statement from a make table statement to an append statement



'Goes through all access tables
'and creates a new backup table in your
'back up database.
'===================================

Public Sub subBACKUP()
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
'Get all local tables
'--------------------
Set rs = db.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type In (1) AND Left([NAME],4) Not In ('MSys')", dbOpenSnapshot)

With rs
Do While Not .EOF
DoCmd.SetWarnings False
'Create a new backup table
'--------------------------
DoCmd.RunSQL "SELECT Now() AS ARCHIVE_STAMP, * INTO tblBACKUP_" & !Name & "_" & format(date(),'YYYYMMDD') & " IN 'C:\BACKUP\BACKUP.mdb' FROM " & !Name

'clear local table
'-----------------
DoCmd.RunSQL "DELETE * FROM " & !Name
DoCmd.SetWarnings True
.MoveNext
Loop
End With
End Sub



About as clear as mud?

Bruce

 

[bdmclennan]

OK,

Let forget the code. As you have to tables lets mak this very simple.

Steps:
1. Import both DataTracking_tb and AgentTrackingModification_tb into your backup database
2. Rename each table with a 'BACKUP_' prefix.
3. Add a field ARCHIVE_TIMESTAMP (as a date) to each table
4. Delete all the data from the backup tables
5. Link these tables back to your live database
6a. Create query qryBACKUP_DATATACKING
INSERT INTO tblBACKUP_DataTracking_tb ( ARCHIVE_TIMESTAMP )
SELECT Now() AS Expr1, *
FROM DataTracking_tb;
6b. do the same for the other table

7a Create query qryDELETE_DATATRACKING
DELETE * FROM DataTracking_tb

7b Do the same for the other table

8. Run each of your backup queries then each of your delete queries.


B.

 

[Dina01]

Hey B,

Thanks for allyou help