[RESOLVED] How to find a record with same lastname and firstname and copy their ID Numbers?

I have a two(2) access databases, one(1) for the source and the other one(1) is for the destination dbase. The table of a source database and destination database have the same three(3) fields or columns(e.g. FirstName,LastName, ID_Number). The source database ID_Number field/column has value while in destination database ID_Number field/column is blank and only the FirstName and LastName has. I just want to find the records in database(source) with that same LastName and FirstName and if found then their ID_Numbers values will be copied to database(destination).The scenario is in just one click of a button all those records(from source) with same names in (destination) ,their ID_Numbers will be copied to database(destination) ID_Number field/column.

Quote:

---

Originally Posted by ronelpisan

The scenario is in just one click of a button

---

It's one click of a button from the user's perspective but it's a multistage process from the developer's perspective. Have you given any thought to what those stages are and how to implement each one or are you just waiting for someone else to do the whole thing for you? Helping with actual problems is one thing but you're just basically asking us to do your work for you. Maybe give it a go and post back when you run into a specific issue. Break the problem down into the smallest parts possible and tackle each part individually. That way, you may be able to handle some parts on your own and you can ask about a part you have a problem with while you get on with other parts.

you could Import the DBase File to your Access Database and compare there

here a Link how to Import, goto Post#6
http://www.vbforums.com/showthread.p...-to-a-dbf-file

Quote:

---

Originally Posted by jmcilhinney

It's one click of a button from the user's perspective but it's a multistage process from the developer's perspective. Have you given any thought to what those stages are and how to implement each one or are you just waiting for someone else to do the whole thing for you? Helping with actual problems is one thing but you're just basically asking us to do your work for you. Maybe give it a go and post back when you run into a specific issue. Break the problem down into the smallest parts possible and tackle each part individually. That way, you may be able to handle some parts on your own and you can ask about a part you have a problem with while you get on with other parts.

---

Sorry I did not included my initial input. Here is my code but it only works when they are in the same/inside database. What I want is it will also work in another database. Any suggestion please in my connection string.

Code:

---

Dim connstring As String
        connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=sample.accdb"
        Dim conn As OleDbConnection = New OleDbConnection(connstring)
        Dim updatescardexpasdate1 As String = "UPDATE Table1 INNER JOIN Table2 ON Table1.FirstName = Table2.First_Name AND Table1.LastName = Table2.Last_Name  SET Table1.ID_Number = Table2.ID_Number"
        Dim cmdupdate As New OleDbCommand(update, conn)
        conn.Open()
        cmdupdate.ExecuteNonQuery()
        conn.Close()

---

In MSSQL the databases can be linked and then in the join you would fully qualify the database/table names. Does Access allow for linked databases?

A more basic question... Can the Last_name, First_name combination be duplicated? If so then how do you decide which one to use?

No. All combinations of names are unique in values.

I will try to link it.

Quote:

---

Originally Posted by ronelpisan

I will try to link it.

---

why not just use SQL

Code:

---

SELECT * INTO...

---

from one Database to the other
then perform your Update and Delete the Imported Table once you don't need it anymore with

Code:

---

Drop Table...

---

Quote:

---

Originally Posted by ChrisE

why not just use SQL

Code:

---

SELECT * INTO...

---

from one Database to the other
then perform your Update and Delete the Imported Table once you don't need it anymore with

Code:

---

Drop Table...

---

---

He said with a click of a button so I took that to mean from an application. That solution sounds more like a manual operation to me, albeit it could be done in an application.

Quote:

---

Originally Posted by TysonLPrice

He said with a click of a button so I took that to mean from an application. That solution sounds more like a manual operation to me, albeit it could be done in an application.

---

well I don't think it will matter if you Link a Table to the other Access DB (which can be done with ADOX) or just
manually copy that Table for the Update and delete it again.

I'm just wondering why or how the Data got in a mess? or is it no mess and he just want's to sort and rearange the Data once

Quote:

---

Originally Posted by ChrisE

you could Import the DBase File to your Access Database and compare there

here a Link how to Import, goto Post#6
http://www.vbforums.com/showthread.p...-to-a-dbf-file

---

Thanks. Importing and linking the database make it works.