VB parsing function and ODBC

[Dennis Deck]

I have an Access database from a client that has an array of data (180
data elements) stored as a delimited string in a memo field that I want
to parse and analyze with SPSS.  The memo field contents are often
longer than 255 so importing to SPSS and then parsing is not a good
option because any data past 255 will be lost.  If I use Access string
functions in a query to create parse the string into data elements I run
into problems because it takes over 100 characters in nested functions
to parse a single data element so the total query would be huge.  

We finally wrote a slick little parsing function in VB and call this in
a query to parse each data element.  This works fine in Access but when
I try to link to that query from SPSS, the ODBC driver says it does not
recognize the function.  

Any ideas about how to make this strategy work?  I considered turning
the Access query into a Make Table query, then the ODBC driver would
access a saved table and avoid the problem with the VB function, but the
data file will be updated frequently and such an approach would be very
cumbersome for reasons I don't need to go into.  


Dennis Deck, PhD
RMC Research Corporation
111 SW Columbia Street, Suite 1200
Portland, Oregon 97201-5843
voice: 503-223-8248 x715
voice: 800-788-1887 x715
fax:  [phone]
[hidden email]

[Peck, Jon]

In recent SPSS versions -- starting in SPSS 13, I think -- the maximum string size is 32767, not 255.

An ODBC driver cannot see custom VB functions you have written, but if you define in your Access database a query that parses the field, you can access this from SPSS or other software using ODBC just as if the query were a table, and when you update the underlying table in Access, the query values that the ODBC drivers see will also be current.

When you parse your string in Access, are you turning it into 180 variables or just extracting some information from the array?  If you have an SPSS version with the larger string limit, you could probably do the parse on the SPSS side, and if you have SPSS 15 with programmability installed, I'll bet the parse could even be slicker on the SPSS side by making use of regular expressions.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Dennis Deck
Sent: Wednesday, October 11, 2006 9:24 PM
To: [hidden email]
Subject: [SPSSX-L] VB parsing function and ODBC

I have an Access database from a client that has an array of data (180
data elements) stored as a delimited string in a memo field that I want
to parse and analyze with SPSS.  The memo field contents are often
longer than 255 so importing to SPSS and then parsing is not a good
option because any data past 255 will be lost.  If I use Access string
functions in a query to create parse the string into data elements I run
into problems because it takes over 100 characters in nested functions
to parse a single data element so the total query would be huge.

We finally wrote a slick little parsing function in VB and call this in
a query to parse each data element.  This works fine in Access but when
I try to link to that query from SPSS, the ODBC driver says it does not
recognize the function.

Any ideas about how to make this strategy work?  I considered turning
the Access query into a Make Table query, then the ODBC driver would
access a saved table and avoid the problem with the VB function, but the
data file will be updated frequently and such an approach would be very
cumbersome for reasons I don't need to go into.


Dennis Deck, PhD
RMC Research Corporation
111 SW Columbia Street, Suite 1200
Portland, Oregon 97201-5843
voice: 503-223-8248 x715
voice: 800-788-1887 x715
fax:  [phone]
[hidden email]

[Dennis Deck]

I was using v14 but figured the 255 limit was imposed by the ODBC
driver.  I agree that the parse is relatively simple in SPSS (using
mainly INDEX() & SUBSTR()) and had already tested the logic.  The
problem was I was not getting the whole string to parse.

But you got me thinking.  I went back to the GET DATA and added
/ASSUMEDSTRWIDTH=1000.  That got me what I needed from the memo field
(and left regular text fields at 255)!  Now I can apply my SPSS parsing
strategy.  Apparently the 255 was simply an ODBC default.

I would still like to figure out, however, if there is a way to access
queries that use VB functions.  We have a VB function that extracts one
data element from the array.  We created an Access query calling the
function 180 times to create a variable for each element of the array.
This works fine in Access but when I try to connect to this query via
the ODBC wizard it says the query has no columns.  When I manually
edited some successful GET DATA syntax to point to the query ('SELECT *
FROM qryParseActionStep'), I get an ODBC error message about an unknown
function.
We though an OLE connection might work but have to research how to do
this.


Dennis Deck, PhD
RMC Research Corporation
[hidden email]

-----Original Message-----
From: Peck, Jon [mailto:[hidden email]]
Sent: Thursday, October 12, 2006 5:09 AM
To: Dennis Deck; [hidden email]
Subject: RE: [SPSSX-L] VB parsing function and ODBC

In recent SPSS versions -- starting in SPSS 13, I think -- the maximum
string size is 32767, not 255.

An ODBC driver cannot see custom VB functions you have written, but if
you define in your Access database a query that parses the field, you
can access this from SPSS or other software using ODBC just as if the
query were a table, and when you update the underlying table in Access,
the query values that the ODBC drivers see will also be current.

When you parse your string in Access, are you turning it into 180
variables or just extracting some information from the array?  If you
have an SPSS version with the larger string limit, you could probably do
the parse on the SPSS side, and if you have SPSS 15 with programmability
installed, I'll bet the parse could even be slicker on the SPSS side by
making use of regular expressions.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Dennis Deck
Sent: Wednesday, October 11, 2006 9:24 PM
To: [hidden email]
Subject: [SPSSX-L] VB parsing function and ODBC

I have an Access database from a client that has an array of data (180
data elements) stored as a delimited string in a memo field that I want
to parse and analyze with SPSS.  The memo field contents are often
longer than 255 so importing to SPSS and then parsing is not a good
option because any data past 255 will be lost.  If I use Access string
functions in a query to create parse the string into data elements I run
into problems because it takes over 100 characters in nested functions
to parse a single data element so the total query would be huge.

We finally wrote a slick little parsing function in VB and call this in
a query to parse each data element.  This works fine in Access but when
I try to link to that query from SPSS, the ODBC driver says it does not
recognize the function.

Any ideas about how to make this strategy work?  I considered turning
the Access query into a Make Table query, then the ODBC driver would
access a saved table and avoid the problem with the VB function, but the
data file will be updated frequently and such an approach would be very
cumbersome for reasons I don't need to go into.


Dennis Deck, PhD
RMC Research Corporation
111 SW Columbia Street, Suite 1200
Portland, Oregon 97201-5843
voice: 503-223-8248 x715
voice: 800-788-1887 x715
fax:  [phone]
[hidden email]

[Peck, Jon]

Yes, assumedstrwidth only applies to fields where the width has to be ... assumed, as with memo fields.  In the ODBC wizard, you will see this setting in the penultimate panel.

As for the VB functions, in order to make these work, you would have to define your query in Access.  The test would be if you open the query in Access (Open, not Design), whether you see something that looks like the table you want SPSS to retrieve.  If you do, then SPSS should be able to get that same table via ODBC.  If not, then probably the query is not defined correctly on the Access side.

HTH,
Jon Peck

-----Original Message-----
From: Dennis Deck [mailto:[hidden email]]
Sent: Thursday, October 12, 2006 2:54 PM
To: Peck, Jon
Cc: [hidden email]
Subject: RE: [SPSSX-L] VB parsing function and ODBC

I was using v14 but figured the 255 limit was imposed by the ODBC
driver.  I agree that the parse is relatively simple in SPSS (using
mainly INDEX() & SUBSTR()) and had already tested the logic.  The
problem was I was not getting the whole string to parse.

But you got me thinking.  I went back to the GET DATA and added
/ASSUMEDSTRWIDTH=1000.  That got me what I needed from the memo field
(and left regular text fields at 255)!  Now I can apply my SPSS parsing
strategy.  Apparently the 255 was simply an ODBC default.

I would still like to figure out, however, if there is a way to access
queries that use VB functions.  We have a VB function that extracts one
data element from the array.  We created an Access query calling the
function 180 times to create a variable for each element of the array.
This works fine in Access but when I try to connect to this query via
the ODBC wizard it says the query has no columns.  When I manually
edited some successful GET DATA syntax to point to the query ('SELECT *
FROM qryParseActionStep'), I get an ODBC error message about an unknown
function.
We though an OLE connection might work but have to research how to do
this.


Dennis Deck, PhD
RMC Research Corporation
[hidden email]

-----Original Message-----
From: Peck, Jon [mailto:[hidden email]]
Sent: Thursday, October 12, 2006 5:09 AM
To: Dennis Deck; [hidden email]
Subject: RE: [SPSSX-L] VB parsing function and ODBC

In recent SPSS versions -- starting in SPSS 13, I think -- the maximum
string size is 32767, not 255.

An ODBC driver cannot see custom VB functions you have written, but if
you define in your Access database a query that parses the field, you
can access this from SPSS or other software using ODBC just as if the
query were a table, and when you update the underlying table in Access,
the query values that the ODBC drivers see will also be current.

When you parse your string in Access, are you turning it into 180
variables or just extracting some information from the array?  If you
have an SPSS version with the larger string limit, you could probably do
the parse on the SPSS side, and if you have SPSS 15 with programmability
installed, I'll bet the parse could even be slicker on the SPSS side by
making use of regular expressions.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Dennis Deck
Sent: Wednesday, October 11, 2006 9:24 PM
To: [hidden email]
Subject: [SPSSX-L] VB parsing function and ODBC

I have an Access database from a client that has an array of data (180
data elements) stored as a delimited string in a memo field that I want
to parse and analyze with SPSS.  The memo field contents are often
longer than 255 so importing to SPSS and then parsing is not a good
option because any data past 255 will be lost.  If I use Access string
functions in a query to create parse the string into data elements I run
into problems because it takes over 100 characters in nested functions
to parse a single data element so the total query would be huge.

We finally wrote a slick little parsing function in VB and call this in
a query to parse each data element.  This works fine in Access but when
I try to link to that query from SPSS, the ODBC driver says it does not
recognize the function.

Any ideas about how to make this strategy work?  I considered turning
the Access query into a Make Table query, then the ODBC driver would
access a saved table and avoid the problem with the VB function, but the
data file will be updated frequently and such an approach would be very
cumbersome for reasons I don't need to go into.


Dennis Deck, PhD
RMC Research Corporation
111 SW Columbia Street, Suite 1200
Portland, Oregon 97201-5843
voice: 503-223-8248 x715
voice: 800-788-1887 x715
fax:  [phone]
[hidden email]