Results 1 to 6 of 6

Thread: Check for Existing Records Before doing Insert

  1. #1

    Check for Existing Records Before doing Insert



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

    I am using a script of Ken's to insert data from my Excel to a table in my pervasive database. It works great , but i have come to realize that i need to check and make sure
    that there are no existing records in my databse table that matches the data that i am about to insert. Long story short i do not need duplicate records in my database. I'll do my best to provide enough info.


    Info:

    my Name Range tblHeadings in Excel will have a column named Router_Number. This is the only column i need to check against the field Router_Number in my databse table. Of course there will be a Field in my database table named Router_Number. To sum it up the code works it's way down the Router_number column in my Excel Sheet and looks for duplicates or same numbers in the Router_Number field in my database table. I've tried doing a select query but just can't seem to get it to work. can someone help?

    Thanks,

    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;User ID=Administrator;Password=Imnumber03;Location=lab-server;Cache Authentication=False;Encrypt Password=False;Mask Password=False;Persist Encrypted=False;Persist Security Info=False;Impersonation Level=Anonymous;Mode=ReadWrite;Protection Level=None;Port=1583;Pessimistic Read Lock=False;DirectOnly=False;LocalTCP=False;Initial Catalog=(Default)"
     
        '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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Well, I see you've got a few options here...
    1. You could try and deal with it in the SQL here, testing before you insert the record, or
    2. You could retrieve a list of all of your Router_Number data, and then validate your record against it before you try and insert it via SQL.
    There are some examples of the first option at this site: http://www.dmxzone.com/go?4615

    Personally though, I like the second option better, as it only means one trip to the database, and I can then validate my data before I try and upload it. On the second option, you have a couple of routes to go as well:
    1. Stuff the retrieved list into an Excel range, add a column to your table, run a vlookup against it the retrieved list, and then in your loop you can upload records that need to be uploaded, or
    2. Retrieve the list into an Array (which you need to do anyway), then validate the data in the loop against the array. If the value isn't found then upload it.
    You can find a routine to get you started here: http://www.excelguru.ca/node/23 (You'll need to adjust the connection string to work with pervasive.) The routine itself pushes into a range, but you could stop after you've got the array then check your data against it.

    Gives you some options to play with to start. Let us know which route you decide to go and if you need any help with it.
    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.

  3. #3
    thanks for the information, i was thinking of doing the vlookup option originally but was just making sure there may not be a beter way to go with it. Thanks again for the help.

  4. #4
    Quote Originally Posted by Ken Puls View Post
    Personally though, I like the second option better, as it only means one trip to the database, and I can then validate my data before I try and upload it.
    Trouble with that is that most databases are multi-user, so someone else could have inserted it since you retrieved it.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Would the first option do any better, Bob? The upload is going to happen within seconds of retrieving the data. I'm just saying you'd pull down all your data, validate your set for upload, then upload it in one shot, rather than testing each record as you upload it. In theory, because you test, then upload, there is still the possibility someone could inject a record in the mean time, no?

    I'll grant that it's less likely if you test as you go, but it really depends on the frequency of updates to your database table, and how long you hold the records before pushing those updates back to the DB.
    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.

  6. #6

    Upload data to database from excel

    Quote Originally Posted by Ken Puls View Post
    Would the first option do any better, Bob? The upload is going to happen within seconds of retrieving the data. I'm just saying you'd pull down all your data, validate your set for upload, then upload it in one shot, rather than testing each record as you upload it. In theory, because you test, then upload, there is still the possibility someone could inject a record in the mean time, no?

    I'll grant that it's less likely if you test as you go, but it really depends on the frequency of updates to your database table, and how long you hold the records before pushing those updates back to the DB.
    I read this with interest as I have been trying to doing something similar.

    As I mentioned (to Bob Philips) I found this utility (Excel-to-Database) that does transaction then rollback validation to check the data in each row.

    If a primary key is set on a field (eg Router_Number) then a validation message is returned next to the row - so no need for any extra code. No need for any code at all in fact.

    It would be interesting to here what you think of it

    Excel-to-Database
    http:\\leansoftware.net

Posting Permissions

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