They say you learn something new every day.

Posts tagged ‘access’

Compacting Access without Access (10/01/2013)

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”

Advertisements

Table Details (07/03/2012)

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.

Listing Tables (15/01/2012)

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”)

objConnection.Open _
  “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”)
   objRecordset.MoveNext
Loop

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

Tag Cloud