Pervasive

Hi there, I'm very new at connecting to databases so I apologise if I'm not familiar with some of the terminology. I would like to open up a DSNless connection to the pervasive database using VBA and I'm running into various issues. The forums that I have come across all give various bits of code which are helpful but I would like to see one full subroutine to see how it all fits together. By trying to apply different bits of code to my code I end up running into various error codes. Therfore could someone please post an example of the full code to open a connection and create a recordset. It would be most appreciated.

 I don't have any VBA code but if you post your connection string and the actual error you are getting, we might be able to help.

Thank you... any help would be appreciated.This is the code I am using and it may be very incomplete. The database is in a folder called TestData on my local server. Sub pervasiveExample() Dim adoConn As ADODB.Connection Set adoConn = New ADODB.Connection adoConn.Provider = "PervasiveOLEDB" adoConn.ConnectionString = "driver={Pervasive ODBC Client Interface};Data Source=C:\TestData" adoConn.Open If adoConn.State = adStateOpen Then MsgBox "Welcome" Else MsgBox "Error connecting to Database." End If End Sub This is the error I get when I run this code: run-time error'-2147217837(80040e53)': Mode, Protection Level, or unknown parameter has been set (incorrectly) in the connection string

Sorry but I can't seem to get this message in the correct format with different paragraphs and spacing so its difficult to see. It just groups it all together into one paragraph no matter what I do.

 You can use the [ code] tag or the [ quote] tag to get proper spacing. 

Anyway, I see your problem:

Sub pervasiveExample()
Dim adoConn As ADODB.Connection
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = "driver={Pervasive ODBC Client Interface};DBQ=DEMODATA"
adoConn.Open
If adoConn.State = adStateOpen Then
MsgBox "Welcome"
Else
MsgBox "Error connecting to Database."
End If
End Sub

If you are using the "driver="  in the connection string, you cannot use a path.  You must specify the database name (or engine DSN by using ServerDSN= and ServerName= for remote connections) in the connection string.  You also do not specify a Provider when using the ODBC driver. 

 

Thank you for your feedback... Please could you explain your answer further. Just to clarify, I am trying to connect to the pervasive database without setting up an ODBC DSN. If we need to specify a serverDSN name or DBQ name wouldn't that be going against the purpose of what we are trying to achieve. Is there a way of creating this connection only by using a connection string that points to the database path without having to set up a DSN string? Still haven't managed to get the spacing right ;)

 You cannot connect to a PSQL database without creating at least a Pervasive Database Name.  You don't need an ODBC DSN but it helps.  There is no supported way to connect to a path with PSQL ODBC or OLEDB. 

You can create the Database Name in code though using DTO.  

Thank you, that is very useful information... If you wouldn't mind please could you set us off in the right direction with regards to creating the Database Name in code using DTO.

Docs for DTO:  http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=dto/dtointro.1.1.html

On http://pervasivedb.com/support/Pages/PSQLSDK-Archives.aspx is a link for the "DTO SDK for PSQL v9".  You'll want to download that.  It should have some samples.

Thank you.. we are definitely getting there. I have downloaded the required samples but am having trouble importing the FRM files into Visual Basic. Excuse my ignorance but is there a way of importing these sample forms into VBA? Are these samples created to be able to run in VBA as well? When I copy and paste the code I get the error reading: Compile error: User-defined type not defined. This is for DBNamesForm.frm.