I needed to compact an Access database on a machine that doesn’t have Access installed.
It turns out there’s a Microsoft command line tool that can do this quite easily: http://support.microsoft.com/kb/295334
The syntax is quite simple:
jetcomp.exe -src:”C:\database.MDB” -dest:”databasec.MDB”
I spent a silly amount of time yesterday troubleshooting an access error. It kept saying I had an error in my SQL, yet I knew it was correct.
In the end, it turned out one of the columns had a reserved name.
The moral of the story is, always enclose your column names with square brackets:
SELECT [Column] FROM [Table]
It’s easy to get lazy. Just don’t. Always put square brackets round, and you won’t have this problem.
Occasionally, I’ve thought it would be useful to list all the tables in a database, but I’ve never actually tried to do it. It turns out there are different ways for different database engines.
Yesterday I was trying to list all the tables in access and I came across this code:
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
“Provider = Microsoft.Jet.OLEDB.4.0;Data Source = ‘C:\Scripts\Test.mdb’”
Set objRecordSet = objConnection.OpenSchema(20)
Do Until objRecordset.EOF
Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)
I’ve cleaned it up a bit, but it’s amazing – you just have to enter “20” to get a list of tables.
You do get system tables as well, so you need to filter them out, but this is great (at least for Access).