Make table in BE and update link

[Mark Wild]

I have a table that is overwritten every month, but want to keep a copy of the old table (in the BE) for audit.

How can I control the BE from the FE and is it possible to create a link to the new table in code?

Hope someone can help!

 

[crosmill]

If the table isn't in use when you update then you should be able to rename the table using SQL, although access can sometines be a bit funny using SQL like this, so it may not work.

Somthing like this but you'll need to check the syntax.

RENAME TableName TO NewTableName

If that doesn't work then you should be able to copy the table and give it a new name

 

[Mark Wild]

cheers.

have got both rename and copyobject working , but what I'm trying to do is create a copy of the table in the BE, but as a part of an import function in the FE.

In other words, can you copy tables in a different db from the one you're in?

 

[crosmill]

I think SQL has a COPY TABLE function.

If it doesn't work in Access then again you could create the same effect by going all around the houses........hang on, is it copying into your frontend.......?

if thats the case then you need to send the sql command to the backend before it's executed, if this the problem?

 

[Mark Wild]

Both the table to be copied and the new table are in the BE. I just need to run it from the FE.

You mention sending the SQL statement to the BE. What do you mean by this?

 

[crosmill]

OK, whats your setup. waht are you using for a front and back end?

 

[Mark Wild]

v simle structure. 2 Access db's. both on the network. 1 with tables etc and the second with the forms etc

 

[crosmill]

v simle structure

don't know what this means, is it significant.

I've never passed sql to an access backend before but I have done it to SQL Server.

I've got nothing to do at work today so I'll see if I can work it out and post back in a bit.

 

[Mile-O]

Just a quick question, but why don't you just keep one table that stores all the archived data and use an Append and a Delete query to transfer and remove all the details from your original table?

It would seem so much easier.

 

[Mark Wild]

lol.

Cheers. Have tried every method I could think of.

As for my typo, i meant very simple structure. a school boy error from me!

 

[Mile-O]

The append query to take data from one table would be like this:

INSERT INTO tblYourArchive
IN 'C:\DatabasePath\YourDatabase.mdb'
SELECT tblMainData.*
FROM tblMainData
WHERE (((Month([YourDateField*]))<Month(Date())));

and then you can run a Delete query to remove all the details from the table in use as you have already appended them to an archive table.


*[YourDateField] is an identifier as I'm guessing you have a field that determines which month the data has arrived on.