[SOLVED] Programmatically create ODBC Data Source using the IBM SPSS 20 standalone Data File Driver

Programmatically create ODBC Data Source using the IBM SPSS 20 standalone Data File Driver

The problem:

I have a .sav file containing a lot of data from a questionnaire. This data is transferred to an access database through an ODBC connection using VBA. The VBA macro is being used by a lot of not so tech-savvy coworkers and each time they need to use a new .sav file, they need to create or alter a connection. It can be a hastle to explain how every time, so I would like to be able to create this connection programmatically (ideally contained in the VBA macro, but if that's not possible something like a python script will do).

The solution I thought of:

I'm not very familiar with VBA, so to begin with I tried doing it with python. I found this example, which seemed (to me) like a solution:

http://code.activestate.com/recipes/414879-create-an-odbc-data-source/

However, I'm not sure what arguments to feed SQLConfigDataSource. I tried:

create_sys_dsn(
    'IBM SPSS Statistics 20 Data File Driver - Standalone',
    SDSN='SAVDB',
    HST='C:\ProgramFiles\IBM\SPSS\StatisticsDataFileDriver\20\Standalone\cfg\oadm.ini',
    PRT='StatisticsSAVDriverStandalone',
    CP_CONNECT_STRING='data/Dustin_w44-47_2011.sav',
    CP_UserMissingIsNull=1)

But to no avail.

Being green in this field, I realise that my proposed solution might not even be the correct one. Does stackoverflow have any ideas?

Thanks in advance

Solution

For anyone that happens to need a solution for this, what I ended up doing was creating the system DSN by manually editing the registry.

To see which keys and values needs to be modified, create a system DSN manually and inspect the values in 'HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI[DSN]' and 'HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources'. It should be fairly clear how to create a DSN through VBA then.