dcsimg
Results 1 to 4 of 4

Thread: Prevent Saving Duplicate Data To Database

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    8

    Prevent Saving Duplicate Data To Database

    I am using Visual Basic 6 with MS Access 2007. Here is the scenario,
    i have to copy data from one database to another database. Let rs1 be the first Recordset, from where all the fields to be copied to the second recordset let rs2. There is a field ("Date") on both recordset. Now the condition is that if there already exist the data of a particular date on rs2, then it should skip the operation and goto next row and so on.
    Need help doing this. Thanks in advance

  2. #2
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Prevent Saving Duplicate Data To Database

    i suppose you mean copy data from a table in database1
    to a table in database2
    but only if the value of a date in the table in database1
    does not already exist in the table of database2

    if so, you have to put a unique constraint on the date in the table of database 2

    and then you can run a simple insert into in query

    example (since i do not have vb6 here, example in access)
    Code:
    Sub Test()
        Dim S As String
        S = "insert into Table1 in 'E:\Biblio22.mdb' select * from Table1"
        CurrentDb.Execute S
    End Sub
    do not put off till tomorrow what you can put off forever

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,677

    Re: Prevent Saving Duplicate Data To Database

    the only problem I see with that is what will happen when it encounters the first duplicate date? Will it just stop and throw a constraint error (my guess: yes) or will it keep going (my guess: no). What I would do is create a new table for staging, import the data into the staging table, then copy those records that have a data that is NOT in the current table, and then finally drop the staging table.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Prevent Saving Duplicate Data To Database

    it will always succeed,even if there is nothing to insert
    the database.execute method fails silently, unless you tell it to report the error
    if you want it to return an error if it fails, you have to call it like:
    Code:
    CurrentDb.Execute S,dbFailOnError
    and trap the error

    https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
    do not put off till tomorrow what you can put off forever

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width