ADO Recordset Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help in ADO syntax for a particular function. I have a table (table1)
which is setup in a flat table format. It tracks employees, particular
events, and the dates the events occur on. It has this format:

Emp_No, EventType1, EventType1Date1, EventType1Date2, EventType2,
EventType2Date1.......

I need to insert rows into another table (table2) which has this format:

Emp_No, EventType, EventDate

I need ADO syntax of how to evaluate the recordset formed from table1 and
insert the rows into table2. Not every employee has an entry for every event
type or event type date. There only should be rows in table2 when an employee
has an occurence of a particular event.

Here is the ADO code I have so far. Can anyone help me fill in the part to
evaluate the table1 recordset and create the table2 records? Thanks

Dim cn As ADODB.Connection, vacc As ADODB.Recordset
Set cn = CurrentProject.Connection
Set vacc = New ADODB.Recordset
With vacc
.ActiveConnection = cn
.Open Source:="SELECT * FROM table1"
 
Brett S. said:
I need help in ADO syntax for a particular function. I have a table (table1)
which is setup in a flat table format. It tracks employees, particular
events, and the dates the events occur on. It has this format:

Emp_No, EventType1, EventType1Date1, EventType1Date2, EventType2,
EventType2Date1.......

I need to insert rows into another table (table2) which has this format:

Emp_No, EventType, EventDate

I need ADO syntax of how to evaluate the recordset formed from table1 and
insert the rows into table2. Not every employee has an entry for every event
type or event type date. There only should be rows in table2 when an employee
has an occurence of a particular event.

Here is the ADO code I have so far. Can anyone help me fill in the part to
evaluate the table1 recordset and create the table2 records? Thanks

Dim cn As ADODB.Connection, vacc As ADODB.Recordset
Set cn = CurrentProject.Connection
Set vacc = New ADODB.Recordset
With vacc
.ActiveConnection = cn
.Open Source:="SELECT * FROM table1"
Click to expand...

Brett S.,

Aircode/Pseudocode ("With" block omitted for brevity), obviously
untested.

Open Recordset1
Open Recordset2
rs1.MoveFirst
Do Until rs1.eof
rs2.AddNew
rs2.Fields("Emp_No") = rs1.Fields("Emp_No")
rs2.Fields("EventType") = rs1.Fields("EventType1")
rs2.Fields("EventType") = rs1.Fields("EventTypeDate1")
rs2.update
rs2.AddNew
rs2.Fields("Emp_No") = rs1.Fields("Emp_No")
rs2.Fields("EventType") = rs1.Fields("EventType2")
rs2.Fields("EventType") = rs1.Fields("EventTypeDate2")
rs2.update
etc.

rs1.MoveNext
Loop
rs1.close
rs2.close
rs1 = nothing
rs2 = nothing
etc.

If the number of columns involved in the de-normalized table is
excessive, then put the .AddNew/.Update segment inside a loop, put
string variables into the .Fields Properties of rs1,
"rs1.Fields(strColumnName)", and then change the names of the fields
programmatically via string manipulation, or even referrencing the
columns by number, if there's a controllable pattern.


Sincerely,

Chris O.
 
Brett S.,

Aircode/Pseudocode ("With" block omitted for brevity), obviously
untested.

Open Recordset1
Open Recordset2
rs1.MoveFirst
Do Until rs1.eof
rs2.AddNew
rs2.Fields("Emp_No") = rs1.Fields("Emp_No")
rs2.Fields("EventType") = rs1.Fields("EventType1")
rs2.Fields("EventType") = rs1.Fields("EventTypeDate1")
rs2.update
rs2.AddNew
rs2.Fields("Emp_No") = rs1.Fields("Emp_No")
rs2.Fields("EventType") = rs1.Fields("EventType2")
rs2.Fields("EventType") = rs1.Fields("EventTypeDate2")
rs2.update
etc.

rs1.MoveNext
Loop
rs1.close
rs2.close
rs1 = nothing
rs2 = nothing
etc.

If the number of columns involved in the de-normalized table is
excessive, then put the .AddNew/.Update segment inside a loop, put
string variables into the .Fields Properties of rs1,
"rs1.Fields(strColumnName)", and then change the names of the fields
programmatically via string manipulation, or even referrencing the
columns by number, if there's a controllable pattern.


Sincerely,

Chris O.
Click to expand...

Yes, there is a typo, I forgot to change the EventType to EventDate in
the third line of the .AddNew/.Update segments.
 
Thanks Chris. This is very helpful. I also discovered last night how to
accomplish this task by using variables and putting them into a string of SQL
syntax to be exectued. However, this may be a cleaner way to accomplish this
task.
 
Back
Top