Microsoft Access with JDBC: how to get the "caption" property of a given field?

Question

Hi,

at work we have to deal with several legacy databases stored in the Microsoft Access format. One of the informations we need to extract is the "caption" property of the fields on a given table.

If we are using VB Script, this is quite easy, as you can see on the code above:

set dao = server.createobject("DAO.DBEngine.36")
    set bd = dao.opendatabase(arquivo, false, false, ";PWD=password")

    set query = bd.openRecordSet("select * from table")

    for i = 0 to query.fields.count - 1
    on error resume next
        response.write query.fields(i).name & "=" & query.fields(i).Properties("Caption") & vblf
    next 

How can we achieve the same results using JDBC? I know about the ResultSetMetaData class, and that it have a method called getColumnLabel(), which should return this caption property, but that's not happening.

Here is our code in Groovy:

ResultSet query = conexao.createStatement().executeQuery("select * from table")
metadata = query.getMetaData()
for (i = 1; i < metadata.getColumnCount(); i++) {
    String columnName = metadata.getColumnName(i)
    String label = metadata.getColumnLabel(i)

}

So here is my question: is it possible to retrieve this information using JDBC? If it is, how?

Answer 1

This is probably a driver issue. Try another MS Access JDBC driver. There is a list at sun.