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.