Moving Days

{EDIT} Around about September 2008, I decided not to move articles off this blog after all. I might copy a few to the tips page at my Web site, but otherwise I'm going to keep this blog intact. {EDIT}

When I started this blog three years ago, I had intended to use it as a quick way to publish my technology findings both for the general Internet community, and also to use it as a knowledge base for myself.

As it turns out, the older articles wind up being buried in the archives, grouped by month, so many times I've had to guess the month & drill down from there. The built-in search is quite effective, but being able to scan down a complete list of topics is much faster.

For those reasons, I've been moving articles from blogger to my personal web site, where I use a classic ASP page and the FileSystemObject to present a scrollable list of all articles, sizes, dates etc. The current list can be found at http://www.wvmitchell.com/tips.

Another difficulty using the blogger format is that many html tags are scrubbed-out from posts, so any code samples will lose their indenting & formatting, making them hard to decipher. My workaround has been to present a link to the actual code on a separate web page, but I would prefer to be able to post a complete article intact with all the formatting.

The blogger search facilities work very well, and Google indexes the blogger posts, so much information can be found via a normal Google search, but my web site also includes a search feature so nothing will be lost.

In closing, this blog will eventually be shrunk down to include only tech comments and observations.

Batch Files

By all accounts, DOS is dead. But since cmd.exe is still part of Windows, you can still write DOS batch programs to automate certain tasks.

For example, I am working with an Access application that is rebuilt every month. The previous process was to have the user navigate to a shared drive and use Windows Explorer to copy a new version of the MDB file and then paste it to a specific folder on their C: drive.

The new process uses a simple batch file like this:

@ECHO OFF
CLS
ECHO.
ECHO *** XYZ file setup program ***
ECHO.
IF NOT EXIST C:\XYZ MD C:\XYZ

IF NOT EXIST C:\XYZ\XYZ.MDB GOTO NOFILE
ECHO.
ECHO You already have a copy of XYZ on your computer
ECHO ===============================================
ECHO.
ECHO  Do you want to replace your existing copy?
ECHO  ------------------------------------------
GOTO HASFILE

:NOFILE
ECHO *** COPYING FILE, PLEASE WAIT ***
COPY /Y \\MYSERVER\XYZ\XYZ.MDB C:\XYZ
GOTO END

:HASFILE
ECHO  Y = Yes, replace my old file
ECHO  N = No, keep my existing copy of the file
ECHO.
SET CHOICE=
SET /P CHOICE= Type the letter and press {ENTER}
IF /I '%CHOICE%'=='Y' GOTO NOFILE
ECHO.
ECHO  (Nothing was copied or replaced)
ECHO.
:END
PAUSE
EXIT

For a new user, the folder is created if it does not exist.
Then, if the file exists the user is prompted to either overwrite or keep their existing copy.

Without the CHOICE statement, DOS would ask the overwrite question as
Yes/No/All
and this avoids the confusing "All" option.

Removing Access Replication

Using replication with MS-Access adds numerous system fields and tables, and combined with the replication-tracking system, a replicated Access database can grow to an unwieldy size, especially if you have many users and/or there are frequent changes in the design master.

The customary solution is to rebuild the database, i.e. to create a new non-replicated file and then use that to create a new design master and the replicas.

Unfortunately, you cannot simply create a new empty file and import all your Access objects, because all the system fields in the tables will also be carried over via the import. You cannot simply go into design view & delete those replication fields because they are system fields.

The basic process is to create a new empty file, and then export all the Access objects except the tables, and then export the tables but without the replication fields.

Import the VBA module Unreplicate_Access into your design master, and when you execute the code it will create a new non-replicated version of your database. It will also set a number of startup properties such as hiding the DB Window, turning off shortcut keys and so on.

Once you have created the new file, simply copy it over your existing design master (after you make a backup copy, of course) and then click Tools ~ Replication ~ Create a Replica and follow the prompts.

This module is still a work in progress; it does not set any primary keys, nor does it copy any default values for the tables. This will appear in a future post. But there won't be any code to create relationships; Microsoft actually recommends against using RI in a replicated db due to the mechanics of replication.