reinnovate database

Patch Data and Backup Database

Patch Database Data (or Amend Data)

Amend Data” is a convenient feature which lets you update a batch of records by using a single formula. It’s common in some points of working with your worksheets, you have to change some data in a number of records altogether, which maybe due to a design change, an incorrect executed formula or other reasons.
If there are only a few records to be changed, you can do it directly in the worksheet that holds the records or go to DB Tables to update it manually. If there are quite a number of records to be fixed, or you just don’t want to spend time locating the records, it’s a good choice to update all the records altogether in a single line of formula.
use Amend Data to update a batch of records all together.
In the sample application, suppose you have decided to change the prefix of Order Number, from the existing format using “ZTC” to “ZTO”. Since Order Number exists in both the header table – tblInv and the item table – tblInvDtl, 2 formulas are used to fix the records.
change the prefix of order number.
A semi-colon is used as a line break for a new line of formula and the last line of formula needs no semi-colon at the end of it. After adding formulas in the window, press “Execute” to run the formula and update the data:
Update tblInv Set InvNo = "ZTO" + right(InvNo, len(InvNo)-3) where InvNo like "ZTC*"; Update tblInvDtl Set InvNo = "ZTO" + right(InvNo, len(InvNo)-3) where InvNo like "ZTC*"
add the formula and press Execute to run the formula.
Click “Refresh” to renew the records in List View:
the order nos are updated with prefix ZTO
The fun part of SQL is that in this example you can also get the job done by using a single formula to update the records in the 2 tables if you link up the tables likes this:
Update tblInv, tblInvDtl Set tblInv.InvNo = "ZTO" + right(tblInv.InvNo, len(tblInv.InvNo) - 3), tblInvDtl.InvNo = "ZTO" + right(tblInvDtl.InvNo, len(tblInvDtl.InvNo) - 3) where tblInv.InvNo = tblInvDtl.InvNo and tblInv.InvNo like "ZTC*"
The condition where tblInv.InvNo = tblInvDtl.InvNo links the 2 tables up so that the 2 tables can be updated together in a single formula.
The formula function len(tblInv.InvNo) returns the number of characters, or its length, inside the field tblInv.InvNo.
The formula function right(tblInv.InvNo, len(tblInv.InvNo) -3) returns the rightmost portion of tblInv.InvNo with length equal to len(tblInv.InvNo) – 3, which means to retrieve the right portion of Order Number without the 3 letter prefix – “ZTC”.
Amend Data is also a great and useful feature when you are developing application to other users and supporting users in a remote location and you want to do some patching of data in the remote application. Just send the formulas through email or any chatting app and tell the user to paste and execute the formulas in the window. You may call it a Manual SQL Server if you like :).

Back Up Database File

BAU DB is a file based application and therefore backing up database is just simple. Firstly, create back up of the database files by using the Windows File Explorer. It is simply done by copy and paste of the database files. Remember to copy the formula file, or setting file, *.dbp, and database file, *.mdb, as well because the formula file will also changes over time.
backup dbp and mdb database files
Update the backup file names by appending the current date to it as a form of version control, and that’s all to be done for backup of an application.
rename the backup file by adding current date to it.
Open Backup Database
When you need to refer to the backup data, just open the backup app file, in this example “trade-goods – 20151216.dbp”, in the Main Menu:
open the backup database when you need to refer to the old data.

Trim Down Old Data Records

Once backup file is created, it is a smart idea to move away some “old” record entries that are already processed or reported, and no need to remain in the current working database file. When your application has been running over a period, it may be one year, two years or more depending on how the application is used, there will be a large number of this kind of “used” records.
These old records can be kept in a backup file and then removed from the current database file keeping it fresh and light weighted. This is done by the feature of “Patch Data” as demonstrated above.
Since backup file is created, we can go to remove these “old” records from the current database. Since there is no old data in the demo application, let’s just have a look at this example. Suppose we are going to remove all customer order records in the year 2013 or before and the order status are completed. These formulas are applied in the window of Amend Data:
Delete * from tblInvDtl where InvNo in (Select InvNo from tblInv where DocType = "ODR" and InvDate < datevalue("2014/1/1") and Status = "PAID"); Delete * from tblInv where DocType = "ODR" and InvDate < datevalue("2014/1/1") and Status = "PAID"
remove the old invoice records from the current database
To remove an Customer Order entry, we have to delete records in the main table, tblInv, and the item table, tblInvDtl, and therefore 2 formulas are used. Note that records in tblInvDtl must be removed first, since the formula relies on the data in tblInv to sort out the order numbers, InvNo.
The clause (Select InvNo from tblInv where DocType = “ODR” and InvDate < datevalue("2014/1/1") and Status = "PAID") is a sub-formula, or sub-query. It provides a set of invoice numbers for the Delete formula to remove records.
The formula function datevalue(“2014/1/1”) returns a date value for comparision with the date fieldInvDate.
Since other worksheets such as Invoice, Purchase Orders etc also use the tables tblInv and tblInvDtl, the condition DocType = “ODR” is used in the formula to sort out the customer order records. tblInv.DocType is a hidden cell in the Customer Order worksheet with default value equal to “ODR”.
When trimming out the “old” records, make sure these records are not used in other parts of the worksheets for some aggregated calculations. You can always restore the removed data from the backup file to the current database application, which is explained in the following section.

Restore Data From Backup File

Now, assume a Customer entry is lost in the current database file, which is deleted by someone accidentally. This demonstration shows you how to retrieve the record from backup file and restore it into the current database. This is also done by applying formulas in Amend Data to move data. In the below demonstration, let’s assume the entry with Customer Number “EK001” is lost.
the demonstration is to recover the lost Customer entry EK001
Direct Restore of Data
You can move records directly from the backup file to the current database using the Insert into … Select … from … formula. An Customer entry consists of two tables, one is the main table tblCust and the other is the multi-contacts table tblCustL, therefore 2 formulas are used to retrieve the records. These are the formulas:
recover records from backup database directly
Insert into tblCust Select * from tblCust in "D:\mitproject\0.sample01.trade.good\trade-goods - 20151216.mdb" where CustNo = "EK001"; Insert into tblCustL Select * from tblCustL in "D:\mitproject\0.sample01.trade.good\trade-goods - 20151216.mdb" where CustNo = "EK001"
The path used in the formulas “D:\mitproject\0.sample01.trade.good\trade-goods – 20151216.mdb” is the location of the backup database file. Since only the entry with Customer Number “EK001” is needed, the condition CustNo = “EK001” is applied in the formulas to retrieve the records.
Restore Data with Intermediate File
You can also extract the backup data to an external file first, and then upload to the current database. The first thing is to open the application of the backup file. From the Main Manual of the backup application, apply these 2 formulas to retrieve the Customer entry into a new database file.
retrieve records from backup database
Select * into tblCust in "d:\abc001.mdb" from tblCust where CustNo="EK001"; Select * into tblCustL in "d:\abc001.mdb" from tblCustL where CustNo="EK001"
This formula uses the Select * into … from … pattern and will create a new table tblCust in the target database file d:\abc001.mdb.
After execution of the formulas, a new database file d:\abc001.mdb is created, and the records in the 2 tables are retrieved into the new database.
a new databse file is created after execution of the formulas
the retrieved customer record
the retrieved contact records of the customer entry EK001.
Restore Records
The next step is to move the extracted records from d:\abc001.mdb into the current database. Now, open the current application file and apply the formula:
move the extracted data into the current database
Insert into tblCust Select * from tblCust in "d:\abc001.mdb"; Insert into tblCustL Select * from tblCustL in "d:\abc001.mdb"
Since the tables tblCust and tblCustL already exist in the current database, we do not use the Select * into … from … formula pattern as above, which creates new database tables. In this case, Insert into … Select … from … is used to move the data from an external database file into the current application database, without creating new tables.
This technique of restoring data using intermediate database file is also useful when you are supporting user in remote site and want to extract data from user’s side to the database on your side. You can also use this as a method to exchange data with your working partner in a remote site.