I was contemplating a better way of storing our old emails and unhappy with some of the systems in place I started considering whether I could dump them into a database.
Thing is when you export from Outlook some of the standard fields in particular To and From are concatenated. Wouldn’t it be nice to separate those fields into their own table of names and addresses and reference them back to a main table of messages.
This is what I came up with.
For demonstrations purposes I will use two tables
pkid - autonumber primary key
ccaddresses - memo or long text
and the child table
pkid - autonumber primary key
ccaddress - string(150 should do it)
pkidt001 - number
and here is the blank second table
Next we create a user defined function
Public Function CreateChildTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strSQL As String
Dim strField1 As String
Dim strField2 As String
Dim varData As Variant
Dim i As Integer
Dim intPKID As Integer
Set db = CurrentDb
'Select all the ccaddresses from the parent table
strSQL = "SELECT pkid,ccaddresses FROM t001parent"
Set rsTarget = db.OpenRecordset("t002newchildren", dbOpenDynaset, dbAppendOnly)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
While Not .EOF
intPKID = !pkid
varData = Split(!ccaddresses, ";") ' Get all semi colon delimited fields
'add email addresses if there is only one email address there
!ccaddresss = Trim(varData(0)) ' remove spaces before writing new fields
!pkidt001 = intPKID
'loop through addtional email addresses and add them as children to table
For i = 1 To UBound(varData)
!ccaddresss = Trim(varData(i)) ' remove spaces before writing new fields
!pkidt001 = intPKID
Set rsTarget = Nothing
Set rs = Nothing
Set db = Nothing
After running this we should
We’ve just nicely split the parent table ccaddesses field into constituent emails and normalised it as a child of the parent table.
SQL Azure as part of the service offers a number of differing back up options as standard – however you may wish to take additional backups which for instance you can load onto a local version of SQL Server (Express or Enterprise). Here’s a quick rundown of one method of doing this.
Firstly open up your version of SQL Server Management Studio and navigate to the instance and database that you wish to backup
Highlight the database in this case DB001 and right click -Select Tasks and Export Data-Tier Application
Choose an appropriate location to put the backup file
Review the summary of items that will be backed up and then hit Finish
There will be a run down of how the export has worked
And just a check to see the exported file in the directory
For more information on SQL Azure see here
Mikhail Shilkov Azure and programming blog
This is hardly ground breaking but windows Task Scheduler can be used to run VBS scripts on a variety of events to automate repetitive and boring tasks. Backups for example or refreshing caches on web browsers before users come in.
Hit Window button
Type “Task Scheduler”
Press return you should get the following screen.
Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create
Give the Task a name in the area above marked with a red arrow and then go to the actions Tab where you can use the New… button to navigate to the vbs file you wish to run.
Next use the browse button OR if you know the path and file name simply type in the path to your visual basic script you would like to run. Here I have put in an imaginary backup script. Next go to the Trigger tab and again hit the New… tab
The Begin Task selection list gives a good indication of when you want to trigger whatever tasks.
If we continue with the theme and run a vbs script to do a backup at 11:48 everyday
Want to backup files and don’t have a fancy Database Administrator or sysadmin on 40k that knows his way around SQL Server 08R2 and MS Access , windows server and fully complies with continuous backup?
This might prove useful.
With any of the standard bespoke internally created applications that I am responsible for I like to have three main copies or parts.
1.a Back End
2.a Development copy front end – (this is used to adjust and create runtimes )
3.a Front end run time
Of the above two I backup both the back-end and the development copy of the front end. I do not back up run times (No.3), personally if I have the others backed up I can re-create a run-time from whatever point I desire. If for some reason I can’t create a run-time from a development copy it is time to roll back on the development copy – this should exist because of this very post.
In terms of the two types of copies I consider both equally important. Back-ends are easy to create but very difficult to re-create increasing in value with time. Front ends have no value in the data but take an extra ordinary amount of time to figure out and likewise are very awkward to recreate increasing in value with time. Both are digital and can be copied multiple times often with little to no cost (size dependent for back ends). Many front ends are actually tiny but their value comes from the way in which things flow and the speed with which they execute.
So I backup both to date I’ve had some brushes with almost disaster but never actually lost an application. Yes there have been hiccups mainly due to my own error – getting confused with versioning and deleting the most up to date version rather than yesterdays version. Forgetting a password on an encrypted USB and having it wipe itself requiring recovery from backup – that kind of thing annoying but not really a problem. I now have a daily backup routine that at least means that I cannot really loose more than a days work. I use it all the time.
Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "YourDatabaseBackEndCopy-Weekday-" & day(varnow) & ".accdb"
varmonthfile = "YourDatabaseEndCopy-Month-" & Month(varnow) & ".accdb"
BDayFilePath = "C:\" & vardatefile
BMonthFilePath = "C:\" & varmonthfile
FSO.CopyFile "C:\DatabaseTarget.accdb", BDayFilePath, "True"
FSO.CopyFile "C:\DatabaseTarget.accdb", BMonthFilePath, "True"
Set FSO = nothing
msgbox "Backup Complete" ,0, "Backup Script"
Save in simple word editor and change to vbs suffix , double click to run.
Note I have made this Option Explicit which is good practice although it is entirely possible to remove Option explicit and dynamically set the variables.
Best place to search for content particularly on mobile
- Recent Posts
- Archive by Month Year