Creating DSN connection (1 Viewer)

[Freshman]

Hi all,

I'm trying to go the DSN-less route for a few databases.

1. mySQL = Working 100%

2. HFSQL got it working with this code: (locally hosted database)

Public Function CreateHFSQLDSN(strDsn)
Dim vAttributes As String
vAttributes = "DSN=" & strDsn & ";REP=C:\PathToLocalDatabase\" & Chr(0)
SQLConfigDataSource 0&, 1, "HFSQL", vAttributes
End Function

3. My problem is with a Pervasive SQL database (locally hosted database)

This is the connection string if I connect manully:
DSN=MyDatabase;ArrayFetchOn=1;ArrayBufferSize=8;DBQ=CONTEST;OpenMode=0;ClientVersion=10.30.017.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;AutoDoubleQuote=0;

This part of the code works when the connection is cached.
If I exit Access it no longer works giving me an error stating that I'm trying to connect DSN-less and need to setup the DSN in ODBC again - which is what I don't want to do.

tdfLinked1.Connect = "ODBC;DRIVER=Pervasive ODBC Engine Interface;REP=" & strFilePath & ";DSN=" & TestCode & ";DBQ=" & TestCode & ";TABLE=MyTable"

So I also tried the above code I used for the HFSQL database like this:

Public Function CreatePervasiveDSN(strDsn)
Dim vAttributes As String
''vAttributes = "DSN=" & strDsn & ";Description=Pervasive ODBC Engine Interface" & Chr(0)
vAttributes = "DSN=" & strDsn & Chr(0)
''SQLConfigDataSource 0&, 1, "Pervasive ODBC Client Interface", vAttributes
SQLConfigDataSource 0&, 1, "{Pervasive ODBC Engine Interface}", vAttributes
End Function

I does not seem to regognize the Driver name since it does not create it in the ODBC console like it does with HFSQL
From the above you can see I tried a few option.

Anyone with some advice please?

Thanks

 

[cheekybuddha]

I haven't done this with PervasiveSQL, but usually the simplest way is to create a File DSN (see docs), then link a table in Access using the File DSN.

Then check the table's .Connect property for the correct connection string.

Delete the (linked) table, and recreate using your connection string.

 

[Freshman]

HI David, thanks for the reply. I did not post the entire code since the rest is standard. I delete the table, then recreate it yes.

I normally use the User of System tabs but I tried the File tab as you suggested. It also stopped hafl way with the same error but it did create a DSN file with the following output:
[ODBC]
DRIVER=Pervasive ODBC Engine Interface

When I do the same with the HFSQL driver, it goes all the way without an error to create a file with the output being with slightly more data.

It seems like Pervasive is problamatic compare to other drivers.

Let's hope someone who knows it will jump in here.

Thanks!

 

[sonic8]

It seems like Pervasive is problamatic compare to other drivers.

I suspect this should better be rephrased to: "It seem like not reading the documentation is problematic compared to reading the documentation." ;-)

 

[Freshman]

I suspect this should better be rephrased to: "It seem like not reading the documentation is problematic compared to reading the documentation." ;-)

Ahh - thanks a lot for the link - that might come in handy. Will go through it now.

 

[Freshman]

Well I read and tried a lot of diffrent option but still no luck.
sonic8 or someone else who can help with the correct syntax to create a DSN from scratch would be very helpful.

Public Function CreateDSN(strDsn)
Dim vAttributes As String
''vAttributes = "DSN=" & strDsn & ";Description=Pervasive ODBC Engine Interface" & Chr(0)
vAttributes = "Driver={Pervasive ODBC Unicode Interface}" & strDsn & Chr(0)
''SQLConfigDataSource 0&, 1, "Pervasive ODBC Client Interface", vAttributes
''SQLConfigDataSource 0&, 1, "{Pervasive ODBC Engine Interface}", vAttributes
''SQLConfigDataSource 0&, 1, "Driver={Pervasive ODBC Unicode Interface}", vAttributes
SQLConfigDataSource 0&, 1, "DSN=Test", vAttributes
End Function

None of the above worked. I'm not getting anything written into the ODBC console.
I'm using the 32-bit since the driver is 32-bit only.

Thanks again

 

[cheekybuddha]

I'm using the 32-bit since the driver is 32-bit only.

What bitness is your version of Access?

The diver must match, ie 32bit Access, use a 32bit driver, 64bit Access, use a 64bit driver.

 

[Freshman]

Hi, I made sure all is 32-bit including Access thanks

 

[sonic8]

None of the above worked. I'm not getting anything written into the ODBC console.

Did you try calling SQLInstallerError after the apparently failed call to SQLConfigDataSource?

PS: Did you try to connect directly with Access (e.g. a Pass-Through-Query) with a DSN-less connection string?

 

[Freshman]

No I did not - not sure how to. Function/Method not found.

SQLConfigDataSource is declared like this:
Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Should I then also declare SQLInstallerError?

 

[Freshman]

I googled for some help and found this code which I modified a bit.
(I tested with HFSQL which worked fine)
But with Pervasive I get DSN creating failed: 6

Const ODBC_ADD_SYS_DSN = 4       'Add a system data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
hwndParent As Long, ByVal fRequest As Long, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Declare Function SQLInstallerError Lib "ODBCCP32.DLL" (ByVal iError As Integer, _
pfErrorCode As Long, ByVal lpszErrorMessage As String, ByVal cbErrorMsgMax As Integer, _
pcbErrorMsg As Integer) As Integer

Private Const SQL_SUCCESS = 0
Private Const SQL_SUCCESS_WITH_INFO = 1
Private Const SQL_NO_DATA = 100
Private Const SQL_ERROR = (-1)
Private Const SQL_MAX_MESSAGE_LENGTH = 512

Function Build_SystemDSN()

dataSourceName = "CARLYSLE"

'SQLConfigDataSource parameters
Dim Driver As String
Dim Ret As Long
Dim Attributes As String

'SQLInstallerError parameters
Dim iret As Integer
Dim lpszErrMess As String
Dim iErr As Integer 'may be 1 - 8
Dim pfErrCode As Long

lpszErrMess = Space(SQL_MAX_MESSAGE_LENGTH)

Driver = "Pervasive ODBC Unicode Interface"

Attributes = "DSN=" & dataSourceName & ";Description=Pervasive ODBC Engine Interface" & Chr(0)
Ret = SQLConfigDataSource(0&, 1, Driver, Attributes)

'ret is equal to 1 on success and 0 if there is an error
If Ret <> 1 Then
  iErr = 1
  iret = SQLInstallerError(iErr, pfErrCode, lpszErrMess, SQL_MAX_MESSAGE_LENGTH, 0)
  MsgBox "DSN Creation Failed: " & pfErrCode
End If
End Function

 

[Freshman]

This is what I tried again getting either error 6 or 11 - Google did not help me find that those error code mean??

''Driver = "HFSQL" ''Working
''Driver = "Pervasive ODBC Engine Interface"
''Driver = "{Pervasive ODBC Engine Interface}"
''Driver = "Pervasive ODBC Unicode Interface"
''Driver = "{Pervasive ODBC Unicode Interface}"
''Driver = "Pervasive ODBC Client Interface"
''Driver = "{Pervasive ODBC Client Interface}"
''Driver = "ODBC Unicode Interface"
Driver = "ODBC Pervasive Unicode Interface"

Any idea for the reason for the failures 6 or 11?

Thanks

 

[isladogs]

Try checking the ConnectionStrings.com website. Specifically:

SQL Server connection strings - ConnectionStrings.com

Connection strings for SQL Server. Connect using Microsoft.Data.SqlClient, SqlConnection, MSOLEDBSQL, SQLNCLI11 OLEDB, SQLNCLI10 OLEDB, SQLNCLI OLEDB.

www.connectionstrings.com

 

[Freshman]

Try checking the ConnectionStrings.com website. Specifically:

SQL Server connection strings - ConnectionStrings.com

Connection strings for SQL Server. Connect using Microsoft.Data.SqlClient, SqlConnection, MSOLEDBSQL, SQLNCLI11 OLEDB, SQLNCLI10 OLEDB, SQLNCLI OLEDB.

www.connectionstrings.com

Thanks but Pervasive does not appear there.
I can connect without a problem but only if I setup the DSN in the ODBC first. They I don't even have to spesify the driver.
My goal however is to create a DSN from scratch so that I don't have to first set them up at each User since there will be lots of Users with multiple databases.

 

[isladogs]

I've never used Pervasive. What is it meant to do?

 

[Freshman]

Pervasive PSQL is SQL type database previously known as Btrieve.
The Software company is actian.com

See installed Driver below in the ODBC

 

[isladogs]

Ah I see. In that case look at this page

Pervasive connection strings - ConnectionStrings.com

Connection strings for Pervasive. Connect using PsqlConnection, PervasiveOLEDB, OleDbConnection, Pervasive ODBC Client Interface and ODBC .NET Provider.

www.connectionstrings.com

For example:
Driver={Pervasive ODBC Client Interface};ServerName=myServerAddress;dbq=@dbname;

 

[Freshman]

Thanks I have tried that and various other name options but none seems to work. I've managed to book a service call with Actian to assist me tomorrow.

Thanks everyone for their input. I hope to get a soltution tomorrow!

 

[sonic8]

This is what I tried again getting either error 6 or 11 - Google did not help me find that those error code mean??

These ODBC error codes are defined in the header file odbcinst.h. This and almost every other header file for the Windows API is included in the Windows SDK downloads.

#define ODBC_ERROR_COMPONENT_NOT_FOUND           6
#define ODBC_ERROR_REQUEST_FAILED               11

If these are helpful or not depends on when which error happens and on whether the "Pervasive ODBC Client Interface", which I consider the suitable driver, is installed and listed in the "Drivers" tab of the ODBC manager.

 

[Freshman]

Thanks - I'm wating to for the support session on zoom later today so will discuss this with them then as well