button in form to move data from one table to another (1 Viewer)

[akika]

hi,

Can u pls help..
I have button named 'backup' in a form that when click it copies all data from table1 and move it to table2_bk

in the on-click how can i add such that it move all data for 2018 with type = 'CPL'
format of date in table1 is DD-MON-YY.

 

[bob fitz]

perhaps you could create an append query with the appropriate criteria which could then be run by clicking the button but why do you need to move the data?

 

[akika]

the main table1 contains too much data for several years..
I would like to make a history table that will move all previous year data for 2016, 2017, 2018.
Keep only 2019 data in the table1.

which query i can add to this on-click btn to do this pls??

 

[bob fitz]

the main table1 contains too much data for several years..
I would like to make a history table that will move all previous year data for 2016, 2017, 2018.
Keep only 2019 data in the table1.

which query i can add to this on-click btn to do this pls??

Why is it too much? What problem does it cause?

 

[akika]

want to segregate the year of data..
want to have the main table only for 2019 and that of 2018 record with type not yet CPL.


Getting errror :
Syntax error in string in query expression '[type] = 'CPL'
When using below code:


Private Sub Command119_Click()
On Error GoTo Err_Command119_Click

DoCmd.RunSQL "Insert Into [table_backup] Select * from [tableMain] where [type]='CPL'" and date <= today

Exit_Command119_Click:
Exit Sub

Err_Command119_Click:
MsgBox Err.Description
Resume Exit_Command119_Click

End Sub

 

[isladogs]

You are making a lot of unnecessary work for yourself by doing this.
Much better to keep all records in one table and use a query to filter data for a specified year when required

 

[Galaxiom]

the main table1 contains too much data for several years..
I would like to make a history table that will move all previous year data for 2016, 2017, 2018.
Keep only 2019 data in the table1.

How many millions of records do you have? If the date field is indexed and the queries properly constructed the performance won't be seriously affected unless numbers of records are huge.

Copying records to another table then deleting the originals can be risky operation and should not be taken lightly.

Note that "Type" is a reserved word and should be avoided in object names.

 

[missinglinq]

What you're attempting to do here, essentially, is store Data (in this case the year) in the names of your Tables...a definite violation of relational database rules.

You are talking about a Relational Database...not a Spreadsheet app!

Linq ;0)>