Is there a small alternative editing tool for Access tables?

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.

From stackoverflow

• 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.

• 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
  

  David-W-Fenton : This works, of course, because the Jet db engine is installed by default on all copies of Windows, starting with Windows 2000.

• 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.

  DR : Yes, you are right, but I'm not in any position to change those things.

  David-W-Fenton : I voted this back up because even though this answer doesn't help the original questioner, it's really good advice, nonetheless.