Compact Access 2007 database from command-line

Microsoft recommends compacting Access databases “regularly” as this will prevent corruption. If the file is on the network, data loss is inevitable. Mitigating its effects involves a comprehensive strategy centered around damage control, seemingly.

Programmatic compacting can be done with

> start access.exe my-db.accdb /compact

Next challenge: making sure the .laccdb lock file is not present; i.e., your users aren’t using the database meanwhile.

Rules-processing template

Here’s one approach to writing up rules for a VBA macro:

Private Enum ColumnNames
  fldGender = 4    ' D
  fldAge = 2       ' B
  fldName = 1      ' A
  fldSSN = 3       ' C
  fldFaveColor = 5 ' E
End Enum

Sub RunModule(args As String)
  Range("A1").Select
  Range(ActiveCell, ActiveCell.CurrentRegion).Select

  For Each r In Selection.Rows
    If r.Row > 2 Then ' skip header
      Cells(r.Row, fldGender) = GetGender(Cells(r.Row, _
        fldSSN))
      Cells(r.Row, fldFaveColor) = GetFaveColor(Cells(r.Row, _
        fldGender))
      ' etc...
    End If
  Next
End Sub

Private Function GetGender(s As String)
  If s = "12345" Then
    GetGender = "F"
  Else
    GetGender = "M"
  End If
End Function

Task rescheduling with Tcl

schtasks demands confirmation for changing tasks; only deleting tasks has a force option. So, search for the task and delete it, then create it again with the new parameters.

set inp [open "|schtasks /query | findstr \"$task_name\""]
set contents [split $inp \n]
catch {close $inp} result

# if exists, delete
exec schtasks /delete /tn \"$task_name\" /f

# recreate
exec schtasks /create /tn \"$task_name\" /st 00:00:00 /sc daily

This was part of a synchronization process where report scenarios could use a read-only version of a file that was regularly backed up.

Run cygwin *nix tools from batch scripts

Windows batch scripting is so underestimated.

rem -----------
rem sort-me.bat
rem -----------

C:\cygwin\bin\sort -u windows-file.txt > uniq-sorted.txt

type uniq-sorted.txt

Your toolbox now consists of cscript, AutoIt console programs, and now all the shell builtins from Cygwin. On top of that, you have tclsh, perl, Rscript, python, …

Graphical attentions

I broke and slapped the Excel chart at the top of my reports. Two weeks had passed and no one had commented on the pretty pictures. They had to scroll down to see it; I imagined their faces lighting up as stacked columns aligned themselves under a trend line. Then I would get requests, become semi-famous, &c. Were it only so.

JPG makes blurry pictures. Excel can export to PNG. The numbers and lines are much sharper. Don’t forget the title, and label your horizontal and vertical axis. All that high school stuff, except this time I had to rationalize my yearning for acknowledgement.

A part of me dreads that part, the requests. It’s because the problem will be a slight permutation, and novel attempts must be refactored for maximal time savings. All of these tools shall rarely see the light of day; my years have gone to cloaked artifacts.

Honestly, I don’t think they look at them too closely. A part of me is relieved, while the other part wonders whether browsing Facebook would have been the lesser sin.

Programmatic PivotTable with VBScript and VBA

VBA has a lot going for it:

  • It comes with Excel.
  • The IDE has Intellisense, breakpoints, built-in reference and integrated library (for including ADO database tasks, for example).
  • Immediate window for interpreted Visual Basic
  • Watch window for tracking variable state, including arrays and Dictionary
  • Event-driven, simple, fast REPL development
  • Backed by spreadsheet-in-memory

VBScript can be used as glue – like Command Prompt – to leverage your existing code. You can create PivotTables from the command line!

See my gist for details.

Twenty-seven tokens

I finally had to use perl instead of batch scripts for a really long CSV-formatted file, because for /f only separates up to twenty-six tokens.

for /f "delims=, tokens=27" %%i in (csv-file.txt) do (
  echo nope
)

With perl,

my @fields = split(/,/, $line);
print $fields[26] . "\n";

Just like that.

Another means of web scraping

Using AutoIt, you can get a window handle on Internet Explorer. AutoIt will build the DOM tree. Awesome. From there, you can reference the form and its constituent elements. Here I salute the community, because of the User-Defined Functions (UDFs) in IE.au3.

One example use is transferring data from one source to another. Manual data entry is slow and error-prone; with one click, you can copy the data and fill in the fields, just like that. If you can save one person his time, multiply it tenfold or a hundredfold – that’s the real value, day in and day out.

Actually, I had to say, “I’m not sure we need more than one programmer.” I don’t know when I would need a second person. Sure, if we acted as independent consultants. There is an overhead to programming in a team, but the advantage is asynchronous productivity.

I haven’t looked at my work and thought, “Oh, if only there were a second person.” So far, everything seems tractable and viable. And if not that, at least learnable.

Specify multiple options to ssconvert in Windows

ssconvert is my go-to tool for converting Excel workbooks to CSV-formatted text files. Recently I found a way to specify multiple options to the command. Assuming your workbook doesn’t have commas in the cells, you can get a nice conversion in one step:

ssconvert -O "separator=, format=raw quoting-mode=never eol=windows" workbook.xls csv-format.txt

Suppose you have a collection of workbooks. You can use findstr to search for lines that do not match the header. Combine /V and /C:”<string>” like so:

for %%i in (*-csv.txt) do (
  findstr /V /C:"Name,Age,Gender" %%i >> final.txt
)

Another ssconvert to turn it into a consolidated workbook:

ssconvert final.txt final.xls

You can wrap it up in a .zip and mail it off:

7z a final.zip final.xls

Finally, set it all up as a schedule off a cURL batch script:

schtasks /create /tn make-final /tr "C:\scripts\curl-save-customer-time.bat" /sc daily /st 14:00:00 /ru admin /rp pass

Notes

  • Excel will fold double-quoted strings into one cell; this is desired behavior.
  • Search AutoIt v3 forums for email script.
  • Experiment with the ssconvert options. Sometimes, you may want quoting. This post empowers you to specify more than one setting.
  • If the one-step conversion does not give you useful line endings, use ssconvert twice: from .xls to .csv, and then .csv to .txt.

Customer requirements

I rolled up a TCP client-server and put out the alpha. I searched “single-threaded server” and thought about the I/O-blocking horror. If we set up all the machines beforehand, we could avoid that bottleneck. I thought about fast array lookups and cached indexing, logarithmic runtimes and great success.

“You shouldn’t make a form without first finding out what we want it to look like and what we want out of it,” the manager said.

So much for letting loose. We were explorers in a warehouse jungle, girder-shelves, cardboard trees. Our connections were spotty; we kept the control file in our electronic bibles and kept the scanners unsheathed.

I thought about our reporting process. We can’t do realtime. But we can do realtime extrapolation. If I can retrieve the data in 15-minute intervals, process it up in the between latent, run a linear regression and spit out a .png from R, and then mail it – bam: steaming-fresh, friendly, twice-daily works of art!