Using a LIBNAME ODBC connection to an Access database with custom VBAs in data

hello_SAS — Tue, 03 Nov 2015 22:34:46 GMT

I'm using SAS enterprise guide, and need to connect/pull data from a 2007 Microsoft Access Database. The database has custom queries saved as objects in it, and those queries pull from various tables within Access. Overall, there is too much data within the tables to be able to import, so I used a libname statement to connect SAS with Access.

LIBNAME (specific to data set) type=access path="folder path to data in server"

SERVER="XXX Server numbers"

PORT=xxx port numbers;

This libname statement works great, the library location I have referenced is reading in objects/tables correctly without any issue. EXCEPT:

Several of the tables I need to call have a column that is populated using a custom VBA in the Access database. The VBA itself is referencing a couple different attributes within the data and other tables to populate a character in the column; it shows up in the column header when you view the properties of the table. When I try to get these tables, I get the following error:

ERROR: CLI describe error: [Microsoft][ODBC Microsoft Access Driver] Undefined function '(column name)' in expression.

SAS refuses to pull in any data from those tables because of this custom VBA. What are my options here? Has anyone else run into this issue?

Re: Using a LIBNAME ODBC connection to an Access database with custom VBAs in data

hbi — Wed, 04 Nov 2015 02:22:27 GMT

Unfortunately, your custom VBA function is a something that MS Access supports while your database is open in MS Access. The ODBC driver can handle built-in functions (e.g. Now(), Trim(), etc.), but not custom functions.

I would suggest running a "make-table" statement in MS Access that calls the VBA function:

SELECT Name, Address, NameOfVBAFunction(name_of_column) AS Expr1
INTO [copy_of_table]
FROM [name_of_table];

This would create a table called "copy_of_table" that contains the results of the function call.

topic Re: Using a LIBNAME ODBC connection to an Access database with custom VBAs in data in SAS Enterprise Guide

Using a LIBNAME ODBC connection to an Access database with custom VBAs in data

Re: Using a LIBNAME ODBC connection to an Access database with custom VBAs in data