By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Sep 10 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim cn As ADODB.Connection
  3. Dim cmd As ADODB.Command
  4.  
  5.  
  6. Sub MoveRecords()
  7.     Set cn = New ADODB.Connection
  8.     With cn
  9.             .Provider = "Microsoft.Jet.OLEDB.4.0" ' OLEDB file that is required to connect to MS Access 2003 or less.
  10.             .Properties("Data Source").Value = "E:\My Documents\db7.mdb" 'Full Path to Data Source
  11.             .Properties("Persist Security Info").Value = False
  12.             .Open
  13.     End With
  14.  
  15.     Set cmd = New ADODB.Command
  16.     With cmd
  17.             Set .ActiveConnection = cn
  18.             .CommandText = "INSERT INTO [Section] ( RNO, StudentName )" & _
  19.                            "SELECT Student.Rno, Student.StudentName " & _
  20.                            "FROM Student LEFT JOIN [Section] ON Student.Rno = Section.RNO " & _
  21.                            "WHERE (((Section.RollNo) Is Null));"
  22.             .CommandType = adCmdText
  23.             .Execute
  24.     End With
  25. End Sub
  26.  
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.
Attached Files
File Type: zip db7.zip (9.4 KB, 313 views)
Nov 17 '08 #2

NeoPa
Expert Mod 15k+
P: 31,342
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!
Nov 17 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
Nov 17 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.