DSN connection to remote pervasive database

Question

Good Day,

I have created a small VBA procedure to get data from a Pervasive database using the DSN name. This works fine when I am on the same network as the server. I need to be able to run this procedure when I am not on the same network. I have tried many different connection strings but are unable to open the connection. I am not sure where to start trouble shooting to get this to work. I have access to the server via remote desktop using dyndns name - not sure if I can use this as the server address in my connectionstring?

Below is the VBA code that works when local. I need a way to make this work remotely.

Sub GetData()

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sSql As String

    sSql = "SELECT AccDesc, BalanceThis01, " & _
        "FROM LedgerMaster WHERE NumberSubAccs = 0"

    Set adoConn = New ADODB.Connection
    adoConn.Open "DSN=KAYDAV"


    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, _
        ActiveConnection:=adoConn

With Sheet2
    .Range(Cells(5, 1).Address, Cells(5000, 44).Address).Value = vbNullString
    .Cells(5, 2).CopyFromRecordset adoRs
End With

    Set adoRs = Nothing
    Set adoConn = Nothing

End Sub

Answer 1

To access a remote Pervasive engine using ODBC that may be behind a firewall, you'll need to open port 1583.
You can use a DSN as long as the DSN is pointing to the right machine name. In your case, you would use the DynDNS name. You can use a connection string in the form of "Driver={Pervasive ODBC Client Interface};ServerName=myServerAddress;dbq=dbname;"

If that doesn't work, you'll need to give more information including errors and version of Pervasive you are using.