Move Records Fast

[MrMajik]

In a one (Parent) to many (Child) relationship the Child table contains around 500,000 records. This is opened as an ADODB connection.

The primary key in the Parent table is the Customer number.

I am looking for the fastest way to scan the Child table for records with the same customer number and move them to a backup table.

Any suggestions?

Thank you.

 

[KenReay]

Hi

If you only want data from the child table why do a join?, just SELECT ... FROM tblChild WHERE CustomerId = 'xxx..';

If your table is in SQL Server (Say) then making the query a stored Procedure will make it go quicker that just submitting the SQL

Regards
Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[MrMajik]

Hi Ken;

I am more of a VB programmer than a database developer so the string you posted makes sense but I do not know how to use it. I checked in MS Access XP Help for clarification but the only thing there on SELECT is for Select Case...

Can you post an example of how you would code this?

Thank you,
Steve

 

[Rolliee]

Open a new form whose recordset source is the query and you will be able to move back and forth thru the records that have the common ID.


rollie@bwsys.net

 

[KenReay]

Hi

As Rollie has indicated you can make a query in the querybuilder, then base a form on that query, or if you want to move through the record set in code you need something like:

Dim Db As Database
Dim Rs as DAO.Recordset
'
Set Db = CurrentDb()
Set Rs = Db.OPenRecordset("SELECT ...blah, blah&quot
Do Until Rs.EOF
... you code in here
rs.movenext
Loop
rs.close
set rs = nothing
set db = nothing

Please note I have shown code for DAO, if you are using AccessXP, then ADO is the default access method, so you need to adapt the above code to an ADO connection (which if you are a vb programmer I assume you know how to do) or include a reference to DAO in your Access project and use code ashown above.

Regards

Ken Reay
Freelance Developer
kenneth.reay@talk21.com