Getting a recordset out of a table

[boogi]

Hi,
I want to create a new record in table yyy for each id from table players.
So actually I thought I should get a recordset of the desired players.id and then do For Each record in recordset bla bla bla...
The problem is that I don't know how to get that recordset.

Is the id correct? if so, how do I do it...

Yaniv.

 

[Golom]

Assuming DAO

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset ( "tblPlayers" )
Do Until rs.EOF
   CurrentDB.Execute "INSERT INTO yyy ( ID ) " & _
                     "VALUES ( " & rs![ID] & ")"
   rs.MoveNext
Loop

or even easier

CurrentDB.Execute _
   "INSERT INTO yyy ( ID ) " & _
   "Select Players.ID  From Players LEFT JOIN yyy " & _
   "          ON Players.ID = yyy.ID " & _
   "Where yyy.ID IS NULL "

BTW - For Each doesn't work because a recordset is not an enumerated collection of records.

 

[boogi]

Thanks for the quick reply...
I got a problem using DAO. I get this error: User-defined type not defined for DAO.Recordset.
I do manager however to use ADO, can you tell me if there's anything special I should do for using DAO?

 

[RoyVidar]

In VBE - Tools | References, set a reference to the Microsoft DAO 3.6 Object Library.

Roy-Vidar

 

[boogi]

Thanks very much, you've been very helpful