Is there a small alternative editor for Access tables?
We have several legacy applications that use Access databases for data storage and / or configuration.
Sometimes we have to make small changes or fixes to our client databases. (Add an index, change a row of data, ...) In many cases, access is available on client workstations, but sometimes it is not.
Is there a small tool for small Access database maintenance operations that I don't need to install? (i.e. can be run from a USB stick)
I know Squirrel SQL, but I'm hoping for something lighter.
source to share
I am using VBScript to edit and update databases when Access is not available. Scripts can be written fairly quickly, and there are a number of ready-made scripts available online, for example to compact 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
source to share
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, like the license key that is set in the registry (like AQT ). So it won't.
Therefore OSS tools like Squirrel SQL are the best choices as they don't come with artificial restrictions and just install it (along with Java) on a USB stick:
- Just install Java somewhere
- Copy the directory to a USB stick
- Unpack Squirrel SQL on USB stick
-
Create a small .BAT file in Squirrel SQL home:
install DIR =% ~ dp0
% DIR% .. \ java \ bin \ javaw.exe -jar squirrel.jar
What is it.
source to share
Personally, I would try not to do this at all. You are masking the problem, not solving it.
If the index is worth adding to a single customer database, then it is probably worth adding all customer databases. Otherwise, the same problems will be repeated in the future. I understand the need for quick support, but having databases that are fundamentally different on different workstations will cause more problems, for example, when recreating errors.
It also adds a potential user "scripting factor" once they know how to do it and if the application is still on their computer (ie "I wonder what happens if I change this value?").
Either modify the current application (legacy or not) to add the appropriate indexes at startup, or create a separate small "patch" program that adds indexes and requires your clients to run it. The above assumption that they are written using VBScript is perfectly reasonable. The key is that changes to database settings are repeatable and that you can keep track of what changes were made there.
If the data itself needs modification, why was this data poorly written? Maybe the application can be modified accordingly so that this can be prevented in the first place? This will avoid the same problem with other databases.
source to share