Pervasive PSQL V9

Re: Opening a DSNless connection to the Pervasive database using VBA

deacan — Tue, 23 Aug 2011 14:20:42 GMT

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.

Re: Opening a DSNless connection to the Pervasive database using VBA

Mirtheil — Tue, 23 Aug 2011 13:37:15 GMT

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.

Re: Opening a DSNless connection to the Pervasive database using VBA

deacan — Tue, 23 Aug 2011 13:25:15 GMT

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.

Re: Opening a DSNless connection to the Pervasive database using VBA

Mirtheil — Tue, 23 Aug 2011 12:52:28 GMT

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.

Re: Opening a DSNless connection to the Pervasive database using VBA

deacan — Tue, 23 Aug 2011 12:48:01 GMT

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 ;)

Re: Opening a DSNless connection to the Pervasive database using VBA

Mirtheil — Tue, 23 Aug 2011 12:35:06 GMT

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.

Re: Opening a DSNless connection to the Pervasive database using VBA

deacan — Tue, 23 Aug 2011 11:41:41 GMT

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.

Re: Opening a DSNless connection to the Pervasive database using VBA

deacan — Tue, 23 Aug 2011 11:30:06 GMT

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

Re: Opening a DSNless connection to the Pervasive database using VBA

Mirtheil — Tue, 23 Aug 2011 11:17:52 GMT

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.

Opening a DSNless connection to the Pervasive database using VBA

deacan — Tue, 23 Aug 2011 10:02:26 GMT

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.