Sunday, May 01, 2005

Dynamically creating an Access database

Even need to export data in an Access database format? It may be a little easier than you think, or even the way you're currently doing it.

Two things to consider:

1. You can dynamically create a Access database file without having Access on the system.
2. You can connect to that database and write data to it without ever creating ODBC entries for it.

The first item is possible because the Microsoft Data Access Components, which are installed as part of the operating system on most recent Microsoft operating systems, allow you to perform a number of database operations through OLE Automation. The actually implemention looks something like this:

oleobject l_adocatalog
string ls_database = "c:asaexport.mdb"

l_adocatalog = CREATE oleobject
l_adocatalog.ConnectToNewObject ( "ADOX.Catalog" )
l_adocatalog.Create ( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ls_database )
l_adocatalog.DisconnectObject()
Destroy l_adocatalog

Of course, you would add some appropriate error checking and wrap most of the calls in a try/catch block to allow gracefully recovery from any OLE Automation errors.

The second item is accomplished through something called a "DNSless Connection". That is, rather than create an ODBC entry and point to that in the ODBC ConnectString, we simply give the ODBC drivers everything they need to get connected directly in the ConnectString. In the case of Access, all we need to do it tell it the Driver we want to use and where the file is located in the DBQ paramater, as well as the uid and pwd we would use for any typical ODBC ConnectString:

SQLCA.DBMS = 'ODB'
SQLCA.DBParm = "ConnectString='Driver={Microsoft Access Driver (*.mdb)};Dbq=" + ls_database + ";Uid=admin;Pwd='"

I've put this all together in a little sample app on CodeXchange that creates an Access data file dynamically and the pipelines data from the ASA sample database to that new Access data file.

2 comments:

Thom Lamb said...

This is a pretty cool feature. However, since the "Driver" parameter is documented only for the JDBC DBMS type, will this functionality disappear in the future? I checked out the help file for PB10.2; the section "Database Connectivity|DBParm Parameters listed by database interface" shows the Driver parameter listed in the JDBC group, but not the ODBC group.

bruce.armstrong said...

The Driver parameter isn't documented in the PowerBuilder help for an OBDC connection because it's an ODBC features, not a PowerBuilder feature. It will be supported as long as Microsoft continues to support DSN-less connections in ODBC. See, for example:

http://support.microsoft.com/default.aspx?scid=kb;en-us;165866#XSLTH3125121123120121120120