Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

verify if record exist before updating (migration vb program)

Posted on 2009-04-01
3
Medium Priority
?
425 Views
Last Modified: 2013-12-25
Hi Guys

I have vb program to migrate records from old db to a new model.
This is the situation
tbl-Old
ID  CustID     Location
1          1        Newark
2          1        Denver
3          1        NY
4          1        NY
5          1        Newark


New-Model
ID   CustID   Location
1        1          Newark
2        1          Denver
3        1          NY

This is how it should be transfer, so before i update the new record, i have to verify if the CustID and Location already exist
I have the code that will transfer everything without comparing or verifying is it exist

Any comments  
0
Comment
Question by:titorober23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24042457
Fire SEELCT CustID,  Location FROM tbl-Old and New-Model separately WHERE ID = 1, 2, 3 etc. compare them in code using Cursor/ResultSet/Iterator and if same then do not migrate else go ahead. For this if databases are different does not matter in single Access, different access in different systems as long as you maintain separate connection objects/contexts from application to them.

The other way is to let the code be as is developed and just introduce error/exception handling code where by if constraint violation prevents the migration, then dummy handle the vilation error/exception. It is not just a question of CustID and Location already existing but here more a question of PK ID in source and target both being present before data migration takes place.

If only one connection is possible at a time like in stored procedures, then link the databases/their tables like we have db link in Oracle, nicknames in DB2 and four part name in SQL Server and Sybase i.e. server.db.schema/owner.object.
0
 

Accepted Solution

by:
titorober23 earned 0 total points
ID: 24051600
I did a function that will check if the record already exist , if so it won't add a new record, thsi si the code for the function

Function AlreadyIn(ByVal POldID As Long, ByVal NLoc As String) As Boolean
    Dim rsNLOT As DAo.Recordset

    Set rsNLOT = CurrentDb.OpenRecordset("SELECT * FROM [LocationDetails] WHERE (([Type]='Branch') AND ([CustID] = " & POldID & ") AND ([Location]='" & NLoc & "'))")
   
    'Iterate through records from old table
    If rsNLOT.EOF Then
        AlreadyIn = False
        Exit Function
    Else
        rsNLOT.MoveLast
        AlreadyIn = True
        Exit Function
    End If
    rsNLOT.Close
End Function
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24052078
Good. Fire a select query from both old and new tables while passing columns as parameters to your function so that if boolean returned is TRUE, then do not carry out the migration of these records else do so for the rest.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question