CODEWITHSUNDEEP
ms-accesspervasive
A.D.
A.D.

Reputation: 31

DSN-Less Query to a Pervasive DB via Access 2007?

See Edit 3 for latest developments:

Backstory:

I tried looking, and I hate to ask such a newbish question but here goes.

I've recently gotten a hold of a database that apparently connects (or used to) connect to a Pervasive Database. Before the queries that were in the Access Database consisted of this sort of format: 

SELECT * FROM (ODBC;DRIVER={Pervasive ODBC Client Interface};
ServerName=####;ServerDSN=DB;ArrayFetchOn=;ArrayBufferSize=;OpenMode=1;
DecimalSymbol=.;ClientVersion=9.50.077.002;CodePageConvert=1252;
AutoDoubleQuote=0;)distributors;

I honestly have no idea if this ever worked or not, but anyway. I got a new bit of information regarding the connection string, which allegedly goes as follows. I'm unsure where it came from, I just know what they tell me.

ODBC;DSN=DATABASE;ServerName=######.#####;ServerDSN=DATABASE;ArrayFetchOn=1;
ArrayBufferSize=8;TransportHint=TCP:SPX;ClientVersion=10.10.125.000;
CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;
AutoDoubleQuote=0;

I've tried various permutations of the two and found that if I use something like this:

SELECT * FROM (ODBC;DRIVER={Pervasive ODBC Client Interface};DBQ=@DATABASE;
ServerName=#######.#####;ArrayFetchOn=1;ArrayBufferSize=8;
TransportHint=TCP:SPX;ClientVersion=10.10.125.000;CodePageConvert=1252;
PvClientEncoding=CP1252;PvServerEncoding=CP1252;AutoDoubleQuote=0;) distributors;

I get a Syntax Error on JOIN function. I have been able to play with the query, getting various errors, but this is the one I run into the most. The idea/end goal is that the database can query the pervasive database without having to use any client side DSNs or setting up anything like that if at all possible. For the record, this is a Win7 Machine with Access 2007 and (I think) Pervasive 10.

Again, probably something simply stupid, but if anyone has any idea or input, it'd be greatly appreciated. :)

EDIT: I guess the question would be to ask why Access says my SQL query is wrong, I.E. Why is it throwing up a Join error (Syntax Error on JOIN function) when I don't see any joins? It highlights the first semicolon after the FROM (ODBC; part.

EDIT 2: Playing around a bit more, I discovered that this query combined with a defined DSN gives me results:

SELECT * FROM [ODBC;DRIVER={Pervasive ODBC Client Interface}
ServerName=#####.#####;DBQ=st;ArrayFetchOn=1;ArrayBufferSize=8;
TransportHint=TCP:SPX;ClientVersion=10.10.125.000;CodePageConvert=1252;
PvClientEncoding=CP1252;PvServerEncoding=CP1252;
AutoDoubleQuote=0].[distributors];

However, if there is a way to make this DSN-less, that would be the end goal. :)

EDIT 3: I found a answer. Of sorts.

I think Ifigured it out, it works. Hopefully this helps someone else. ^^

SELECT *
FROM [ODBC;DRIVER={Pervasive 
ODBC Client Interface};
ServerDSN=st;ServerName=######.#####;ArrayFetchOn=1;
ArrayBufferSize=8;TransportHint=TCP:SPX;ClientVersion=10.10.125.000;
CodePageConvert=1252;PvClientEncoding=CP1252;
PvServerEncoding=CP1252;AutoDoubleQuote=0;].[distributors];

Upvotes: 1

Views: 969

Answers (1)

A.D.
A.D.

Reputation: 31

think Ifigured it out, it works. Hopefully this helps someone else. ^^

SELECT *
FROM [ODBC;DRIVER={Pervasive 
ODBC Client Interface};
ServerDSN=st;ServerName=######.#####;ArrayFetchOn=1;
ArrayBufferSize=8;TransportHint=TCP:SPX;ClientVersion=10.10.125.000;
CodePageConvert=1252;PvClientEncoding=CP1252;
PvServerEncoding=CP1252;AutoDoubleQuote=0;].[distributors];

Upvotes: 2

Related Questions