How to insert datas from one table to another using ADO connection

[imagetvr]

Hello

I am using windows XP

I need a small programme in Visual basic 6 with Access 2000

I have two tables for example one table named student other table named section

In student table rno is primary key. In section table Rollno is primary key


Both tables have datas, I want to insert datas(multiple rows) from student to section.

The coditions is unique datas only (ie. some datas already in section while inserting
datas from student to section the existing datas should be eliminated they wont be inserted)
( by comparin rno with rollno)( i avoid duplicate appending)

Please me code for inserting tables using ADODB recordset




A.Senthil kumar

[PianoMan64]

Hello

I am using windows XP

I need a small programme in Visual basic 6 with Access 2000

I have two tables for example one table named student other table named section

In student table rno is primary key. In section table Rollno is primary key


Both tables have datas, I want to insert datas(multiple rows) from student to section.

The coditions is unique datas only (ie. some datas already in section while inserting
datas from student to section the existing datas should be eliminated they wont be inserted)
( by comparin rno with rollno)( i avoid duplicate appending)

Please me code for inserting tables using ADODB recordset




A.Senthil kumar

Hey A.Senthil kumar,

The first issue that you're going to have to get around is that the RNO and RollNo CAN NOT be used as a method to compare if an item has been added to the table or not. The reason is, if for some reason that the numbers get out of sync, then you don't have any way of locating that particular record.

You would be better off to have another field as the primary key and store RNO number into the Section table.

That way if you want to do a lookup and find out if that item has been added to the section table, then you would be able to locate them.

In order to copy a records from one table to another, the ADO code for that is as follow:

 
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
 
 
Sub MoveRecords()
    Set cn = New ADODB.Connection
    With cn
            .Provider = "Microsoft.Jet.OLEDB.4.0" ' OLEDB file that is required to connect to MS Access 2003 or less.
            .Properties("Data Source").Value = "E:\My Documents\db7.mdb" 'Full Path to Data Source
            .Properties("Persist Security Info").Value = False
            .Open
    End With
 
    Set cmd = New ADODB.Command
    With cmd
            Set .ActiveConnection = cn
            .CommandText = "INSERT INTO [Section] ( RNO, StudentName )" & _
                           "SELECT Student.Rno, Student.StudentName " & _
                           "FROM Student LEFT JOIN [Section] ON Student.Rno = Section.RNO " & _
                           "WHERE (((Section.RollNo) Is Null));"
            .CommandType = adCmdText
            .Execute
    End With
End Sub
 

Hope this gives you some kind of clue as to what you need to do.

Joe P.

p.s. I've included the sample data file that works with the code that I've provided. just change the path to the location of your my documents folder and you'll be able to see what I mean.

[NeoPa]

As a general rule, we don't simply provide code upon request. Particularly when someone asks for it without indicating what, if anything, they have attempted so far.

Having said that, some sample (illustrative) DAO code can be found at Basic DAO recordset loop using two recordsets. You will need to extract the concept and do the main work yourself of course, but this gives a good starting point.

Welcome to Bytes!

[missinglinq]

This thread deals with a Visual Basic front end, and as such, needs to be addressed in that forum. Since a duplicate thread has already been posted there, I'm simply going to close this thread.

In the future, please do not post multiple copies a thread.

From FAQs

Do Not Double Post Your Questions

Double posting is where you start a thread on a topic and then for some reason start another thread on exactly the same topic in the same forum or in another forum on the same site. Please do not do this because

1. It makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.
2. It swamps the forum with your problem resulting in less attention for the other threads.

Look over your question, using some native intelligence, and decide which forum your tread belongs in.

Thank you for your attention in this matter.

Welcome to Bytes!

Linq ;0)>