Is there a small alternative editing tool for Access tables?

Question

We have several legacy applications which use Access databases for storing data and/or configuration.

Sometimes we have to do small changes or corrections at our customers databases. (Adding an index, modifying a data row, ...) In many cases Access is available on the customers' workstations, but sometimes it's not.

Is there any small tool for doing small maintenance operations on Access databases which needs not to be installed? (i.e. can be started from a USB stick)

I know of Squirrel SQL, but I'm hoping for something more lightweight.

Answer 1

MS Access uses ODBC so any DB tool on windows can be used.

The main problem with these tools is that many commercial ones use some kind of copy protection, for example a license key which is installed in the registry (for example, AQT). So these won't do.

So OSS tools like Squirrel SQL are your best bet since they don't come with artifical restrictions and it's simple to install it (along with Java) on an USB stick:

1. Just install Java somewhere
2. Copy the directory on your USB stick
3. Unpack Squirrel SQL on the USB stick

4. Create a small .BAT file in the home of Squirrel SQL:

   set DIR=%~dp0
   %DIR%..\java\bin\javaw.exe -jar squirrel.jar

That's it.

Answer 2

I use VBScript for edits and updates of databases when Access is not available. Scripts can be written quite quickly and there are a number of ready-made scripts available on-line, such as for compacting a database.

This example links a table.

Dim adoCn
Dim adoCat
Dim adoTbl

strLinkFile = "C:\Docs\DB1.mdb"
strAccessFile = "C:\Docs\LTD.mdb"

'Create Link...'
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & strAccessFile & ";" & _
       "Persist Security Info=False"

Set adoCat = CreateObject("ADOX.Catalog")
Set adoCat.ActiveConnection = cn

Set adoTbl = CreateObject("ADOX.Table")

Set adoTbl.ParentCatalog = adoCat
adoTbl.Name = "LinkTable"

adoTbl.properties("Jet OLEDB:Link Datasource") = strLinkFile
adoTbl.properties("Jet OLEDB:Link Provider String") = "MS Access"
adoTbl.properties("Jet OLEDB:Remote Table Name") = "Table1"
adoTbl.properties("Jet OLEDB:Create Link") = True

'Append the table to the tables collection'
adoCat.Tables.Append adoTbl

Answer 3

Personally I'd try and avoid doing this altogether. You're masking the problem rather than solving it.

If an index is worth adding to a single customer's database for example, it's probably worth adding to all customer databases. Otherwise the same issues will occur repeatedly going forward. I understand the need for rapid support, but having databases which are fundamentally different on different workstations is only going to cause more problems moving forward, for example in recreating bugs.

It also adds a potential user "fiddle factor" once they learn how to do this and if the application is left on their machine (i.e. "I wonder what happens if I change this value?").

Either modify the current application (legacy or not) to add the appropriate indexes on startup, or create a seperate small "hotfix" program that adds the indexes and require your customers to run it. The suggestion above that these are written using VBScript is perfectly reasonable. The key is that the databas echanges are repeatable and that you can track what changes have been made where.

If data itself needs modification, then why was this data written badly in the first place? Maybe the application can be appropriately modified so that this can be prevented in the first place? This would avoid the same issue happening with other databases.