Results 1 to 10 of 10

Thread: Connection to a remote pervasive databse

  1. #1

    Connection to a remote pervasive databse



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I ran across a sample Excel VB macro that Ken wrote that connects to a local Access database and inserts the rows of excel data into the Access table. It works perfectly, except i need to connect to a remote Pervasive SQL database. I have had success doing some ODBC connections using the connection wizard . These were just Select statement queries, i need to be able to connect to my remote pervasive database and insert rows of data . i tried recording a macro so i could look at the connection string but have had no luck getting the string to work in VB. I am thinking that the Insert code would have to be adjusted to work with Pervasive. I supplied Ken's code below Thanks for any help that can be offered.

    IP address of pervasive server: 192.168.1.70

    Name of pervasive database: globalHII




    Here is Kens Code:

    Code:
    Sub DB_Insert_via_ADOSQL()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To add record to Access database using ADO and SQL
    'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
        Dim cnt As New ADODB.Connection, _
                rst As New ADODB.Recordset, _
                dbPath As String, _
                tblName As String, _
                rngColHeads As Range, _
                rngTblRcds As Range, _
                colHead As String, _
                rcdDetail As String, _
                ch As Integer, _
                cl As Integer, _
                notNull As Boolean
        'Set the string to the path of your database as defined on the worksheet
        dbPath = ActiveSheet.Range("B1").Value
        tblName = ActiveSheet.Range("B2").Value
        Set rngColHeads = ActiveSheet.Range("tblHeadings")
        Set rngTblRcds = ActiveSheet.Range("tblRecords")
        'Concatenate a string with the names of the column headings
        colHead = " ("
        For ch = 1 To rngColHeads.Count
            colHead = colHead & rngColHeads.Columns(ch).Value
            Select Case ch
                Case Is = rngColHeads.Count
                    colHead = colHead & ")"
                Case Else
                    colHead = colHead & ","
            End Select
        Next ch
     
         'Open connection to the database
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & dbPath & ";"
     
     
     
     
     
        'Begin transaction processing
        On Error GoTo EndUpdate
        cnt.BeginTrans
        'Insert records into database from worksheet table
        For cl = 1 To rngTblRcds.Rows.Count
            'Assume record is completely Null, and open record string for concatenation
            notNull = False
            rcdDetail = "('"
            'Evaluate field in the record
            For ch = 1 To rngColHeads.Count
                Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                        'if empty, append value of null to string
                    Case Is = Empty
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                            Case Else
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                        End Select
                        'if not empty, set notNull to true, and append value to string
                    Case Else
                        notNull = True
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                            Case Else
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                        End Select
                End Select
            Next ch
            'If record consists of only Null values, do not insert it to table, otherwise
            'insert the record
            Select Case notNull
                Case Is = True
                    rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
                Case Is = False
                    'do not insert record
            End Select
        Next cl
    EndUpdate:
        'Check if error was encounted
        If Err.Number <> 0 Then
            'Error encountered.  Rollback transaction and inform user
            On Error Resume Next
            cnt.RollbackTrans
            MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
        Else
            On Error Resume Next
            cnt.CommitTrans
        End If
        'Close the ADO objects
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        On Error GoTo 0
    End Sub

  2. #2
    Not tried it myself, but wouldn't this be a better connection

    Code:
    cnt.Open "Provider=PervasiveOLEDB;" & _
               "Data Source=" & dbPath & ";"

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Just for clarification, Tommy is trying to convert this article to Pervasive SQL.

    I haven't ever used pervasive, but being that the routine uses ADO to inject the records, I would assume that only the change of connection string to what Bob supplied would be sufficient.

    Let us know if that does/doesn't work, Tommy.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4

    Progress Maybe

    i think i may be making progress ... i am not getting an error with the connection screen .Here is what i am using

    cnt.Open "Provider=PervasiveOLEDB;Data Source=demodata;Location=" & dbPath & ";"

    i am getting errors on the insert , i think it is because pervasive syntax is different than Access

    right now i would like to see if i am even connecting with the databse at all. I would like to cut the code down right now so that it will do a Select statement .

    Select * From Import

    i can get the select statement into the code i am having trouble assiging it to a cell start displaying the results too. any ideas ?

    Thanks for the replies and help so far

  5. #5
    if this helps any for the problem with the insert command


    this is a pervasive SQL Insert command

    Insert INTO Import(Hours,Major,Minor,gpa) VALUES(33,'pe','music',4.0);

  6. #6

    It Now works !!!

    Ken and bob thanks so much for the help and code. With a few tweaks this code works with a Pervasive Database. Maybe this can will help somebody else. Thanks again guys

    Here is the code:


    Code:
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To add record to a Pervasive database using ADO and SQL
    'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
        Dim cnt As New ADODB.Connection, _
                rst As New ADODB.Recordset, _
                dbPath As String, _
                tblName As String, _
                rngColHeads As Range, _
                rngTblRcds As Range, _
                colHead As String, _
                rcdDetail As String, _
                ch As Integer, _
                cl As Integer, _
                notNull As Boolean
        'Set the string to the path of your database as defined on the worksheet
        dbPath = ActiveSheet.Range("B1").Value
        tblName = ActiveSheet.Range("B2").Value
        Set rngColHeads = ActiveSheet.Range("tblHeadings")
        Set rngTblRcds = ActiveSheet.Range("tblRecords")
        'Concatenate a string with the names of the column headings
        colHead = " ("
        For ch = 1 To rngColHeads.Count
            colHead = colHead & rngColHeads.Columns(ch).Value
            Select Case ch
                Case Is = rngColHeads.Count
                    colHead = colHead & ")"
                Case Else
                    colHead = colHead & ","
            End Select
        Next ch
     
         'Open connection to the database
     
        ' cnt.Open "Provider=PervasiveOLEDB;Data Source=demodata;Location=" & dbPath & ";"
     
     
        cnt.Open "Provider=PervasiveOLEDB;Data Source=demodata;Location=" & dbPath & ";"
     
        'Begin transaction processing
        On Error GoTo EndUpdate
        cnt.BeginTrans
        'Insert records into database from worksheet table
        For cl = 1 To rngTblRcds.Rows.Count
            'Assume record is completely Null, and open record string for concatenation
            notNull = False
            rcdDetail = "('"
            'Evaluate field in the record
            For ch = 1 To rngColHeads.Count
                Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                        'if empty, append value of null to string
                    Case Is = Empty
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                            Case Else
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                        End Select
                        'if not empty, set notNull to true, and append value to string
                    Case Else
                        notNull = True
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                            Case Else
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                        End Select
                End Select
            Next ch
            'If record consists of only Null values, do not insert it to table, otherwise
            'insert the record
            Select Case notNull
                Case Is = True
     
     
     
     
            'rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
     
     
           ' Dim sSQL As String
            'sSQL = "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail
            'rst.Open sSQL, cnt
     
     
            Dim sSQL As String
            sSQL = "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail
            MsgBox "Query: " & sSQL
            rst.Open sSQL, cnt
     
     
                Case Is = False
                    'do not insert record
            End Select
        Next cl
    EndUpdate:
        'Check if error was encounted
        If Err.Number <> 0 Then
            'Error encountered.  Rollback transaction and inform user
            On Error Resume Next
            cnt.RollbackTrans
            MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
        Else
            On Error Resume Next
            cnt.CommitTrans
        End If
        'Close the ADO objects
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        On Error GoTo 0

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi Tommy,

    Sorry, been out all day and not able to get back. Glad you got it sorted out.

    PS... really? The connection string refers to PervasiveOLEDB. (with the period?) That seems odd to me...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Me too Tommy, we are some hours ahead and when you posted last night I was drinking, then it was bed whilst you were still grappling I see the INSERT syntax is the same for Pervasive as (most?) other dbs.

  9. #9
    Ken, the period is not required after OLEDB in the connect string. just a typo, glad you caught that .

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    No worries. I edited the code to remove the extra period.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •