Help with ADO

[jmersing]

I have never used ADO for manipulating records, I would like to learn how to.


Assuming I have the following tables and fields:

tblEmp empnum, empname
tblShift StartTime Endtime
tblskill taskID,Status
tblPlan taskid, required_hours, required_time, priority
tbllRules (all boolean connected to a form) Active, Required, Prioitzie,
ApplySort etc.

The high level logic would be:
Insert the employee into a table if the task is active
and hours are required
and the employee is available during the required time
and the employee can perform the skill
and all of the rules (boolean attributes) have been met
Sort the recordset different priorities i.e. preference, seniority, priority
Keep adding until the required hours are met, then move to the next
task/employee


If someone could help me with skeleton of this procedure I think I can piece
the rest of it together. The part I'm most concerned with is the logic that
loops through all criteria and then inserts the records. I need to understand
how to use variables, and how to loop/edit records based on criteria.

I assume someone has tackled a project like this before, maybe someone could
provide some code with similar logic?
Thanks

 

[Robert Morley]

If you've used DAO before, the logic is nearly identical. The only major
shift in thinking with simple recordset operations is how you open a recordset.

Instead of setting a recordset variable to a recordset returned by
CurrentDB(), you create the recordset as a new object and THEN open it:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
'or just Dim rs As New ADODB.Recordset if you prefer

With rs
.Open "MyTableOrView", CurrentProject.Connection
Do Until .EOF
'Do your stuff
.MoveNext
Loop
.Close
End With
Set rs = Nothing 'pedantic, but often encouraged

If you're a newbie to both DAO and ADO, then I'd suggest looking at
something like this to get started:
http://msdn2.microsoft.com/en-us/library/ms807730.aspx

It's a bit more complex than necessary, as it's intended as a reference, but
it's certainly thorough. There are probably other ADO primers out there if
this is too detailed; maybe someone else will post one, cuz it's been too
long since I learned it for me to have any off-hand.


Rob

 

[jmersing via AccessMonster.com]

That makes sense Robert, so its just like opening a table or query but
virtual, you don't actually see it. I looked at the link also, thanks.

Now on your "Do your stuff" line

What if your stuff has multiple criteria from multiple recordsets. Do you
open all of the recordsets at once, and if so can you give me an example of
what a routine that had several open recordsets and was checking criteria in
each one look like?

Something like this:

select a task from the plan table to find a task that requires hours to be
worked in a a given time period (Dim as need)

select an available employe from the shift table whose shift time is in the
time range I'm trying to create (Dim as available)

check the skill table to see if the employee is qualified to do the task (Dim
as qualified)

check the preference table to select qualified employees by a custom sort
order (Dim as Prefer)

check the seat table for a seat that the task can be performed in (Dim as
openseat)

Insert a record or row in the schedule table that is the result of all of
this criteria (My.rst.Update.Add?)

Repeat until the need is satisfied (Loop)

I think if I understood how to create 1 iteration of this logic I could do it.
But I get hung up mostly on syntax and looping logic.

If my example is too complicated just disregard, I do appreciate your help.
Thanks

If you've used DAO before, the logic is nearly identical. The only major
shift in thinking with simple recordset operations is how you open a recordset.

Instead of setting a recordset variable to a recordset returned by
CurrentDB(), you create the recordset as a new object and THEN open it:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
'or just Dim rs As New ADODB.Recordset if you prefer

With rs
.Open "MyTableOrView", CurrentProject.Connection
Do Until .EOF
'Do your stuff
.MoveNext
Loop
.Close
End With
Set rs = Nothing 'pedantic, but often encouraged

If you're a newbie to both DAO and ADO, then I'd suggest looking at
something like this to get started:
http://msdn2.microsoft.com/en-us/library/ms807730.aspx

It's a bit more complex than necessary, as it's intended as a reference, but
it's certainly thorough. There are probably other ADO primers out there if
this is too detailed; maybe someone else will post one, cuz it's been too
long since I learned it for me to have any off-hand.

Rob

I have never used ADO for manipulating records, I would like to learn how to.

[quoted text clipped - 25 lines]

provide some code with similar logic?
Thanks

 

[Robert Morley]

You can do this sort of thing in a number of ways. Typically, you want to
keep resources to a minimum if you can do so without sacrificing code
speed/readability, so where possible, you'd want to save results to
variables and close the recordset as soon as possible. That said, your code
will suffer dramatic speed decreases if you continuously close and re-open
recordsets, so if you HAVE to leave them all open to get what you want, then
by all means do so.

Looking at your outline, I think you'd want something like the code below;
it's completely off the top of my head, so no guarantees, and since there's
a lot of things I don't know about your design & process, there's a lot of
assumptions being made. You might get significantly better performance out
of a query that joins some of your tables, but since I don't have a good
feel for your design, I've opted for the straight-forward approach that
opens multiple tables; it can be customized to fit your needs better as you
gain a better understanding of the process. Your statements are
interspersed to some degree to make it clear what's handling what. Watch
for line-wrapping; all my code should be indented; if it's not, then it
belongs on the previous line.

'Pre-pending "rs" to your suggested variable names to avoid possibility
'of overlapping with a reserved word.
Dim rsNeed As ADODB.Recordset
Dim rsAvailable As ADODB.Recordset
Dim rsQualified As ADODB.Recordset
Dim rsPrefer As ADODB.Recordset
Dim rsOpenSeat As ADODB.Recordset

select a task from the plan table to find a task that requires hours to be
worked in a a given time period (Dim as need)

Set rsNeed = New ADODB.Recordset
rsNeed.Open "Plan Table", CurrentProject.Connection, adOpenStatic,
adLockOptimistic, adCmdTable
Do Until rsNeed.EOF

select an available employe from the shift table whose shift time is in the
time range I'm trying to create (Dim as available)

Set rsAvailable = New ADODB.Recordset
rsAvailable.Open "SELECT * FROM [Employee Table] WHERE ShiftTime
BETWEEN [SomeStartTime] AND [SomeEndTime]", CurrentProject.Connect,
adOpenStatic, adLockReadOnly, adCmdText

check the skill table to see if the employee is qualified to do the task (Dim
as qualified)

Set rsQualified = New ADODB.Recordset
rsQualified.Open "SELECT * FROM [Skill Table] WHERE (EmployeeID = "
& rsAvailable!EmployeeID.Value & ") AND Skill = (" &
rsNeed!SkillRequired.Value & ")", CurrentProject.Connection, adOpenStatic,
adLockReadOnly, adCmdText

check the preference table to select qualified employees by a custom sort
order (Dim as Prefer)

Set rsPrefer = New ADODB.Recordset
rsPrefer.Open "SELECT * FROM [Prefs Table] ORDER BY " &
strSortOrderFromSomewhere, CurrentProject.Connection, adOpenStatic,
adLockReadOnly, adCmdText

check the seat table for a seat that the task can be performed in (Dim as
openseat)

Set rsOpenSeat = New ADODB.Recordset
rsOpenSeat.Open "SELECT * FROM [Seat Table] WHERE SeatOpen = 1",
CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

Insert a record or row in the schedule table that is the result of all of
this criteria (My.rst.Update.Add?)

rsNeed.Add
rsNeed!EmployeeID.Value = rsAvailable!EmployeeID.Value
rsNeed!Seat.Value = rsOpenSeat!Seat.Value
...etc.
rsNeed.Update

Repeat until the need is satisfied (Loop)

Loop 'LOL



Rob

 

[Robert Morley]

D'oh!

Just after I wrote that, I realized that because I was thinking of it in
terms of a one-shot deal, I wrote the code fairly poorly in the end,
violating most of what I said about not opening and closing things
unnecessarily. I also neglected a fairly obvious "MoveNext"...sort of
necessary until you want to process the same record over and over again for
no good reason! <LOL>

You'd definitely be better off moving all the Set statements to a block at
the top of your code, and you'd probably also be better off moving the Open
statements to the top of your code as well, minus the WHERE statements for
the most part, then just using rsWhatever.Filter to filter each recordset as
you go through the loop.

Set rsWhatever = New ADODB.Recordset
rsWhatever.Open "SELECT * FROM [Some Table]",
CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
'*** OR ***
rsWhatever.Open "Some Table", CurrentProject.Connection, adOpenStatic,
adLockReadOnly, adCmdTable

Do Until rsNeed.EOF
rsWhatever.Filter "EmployeeID = " & rsAvailable!EmployeeID.Value
'^^^ Whatever the appropriate WHERE condition would've been for
' whichever table you're looking at currently.
rsNeed.MoveNext
Loop

Anyway, play around with it and see how it goes, then if you have specific
questions, you can post your code and we'll take it from there.


Rob

That makes sense Robert, so its just like opening a table or query but
virtual, you don't actually see it. I looked at the link also, thanks.

Now on your "Do your stuff" line

What if your stuff has multiple criteria from multiple recordsets. Do you
open all of the recordsets at once, and if so can you give me an example of
what a routine that had several open recordsets and was checking criteria in
each one look like?

Something like this:

select a task from the plan table to find a task that requires hours to be
worked in a a given time period (Dim as need)

select an available employe from the shift table whose shift time is in the
time range I'm trying to create (Dim as available)

check the skill table to see if the employee is qualified to do the task (Dim
as qualified)

check the preference table to select qualified employees by a custom sort
order (Dim as Prefer)

check the seat table for a seat that the task can be performed in (Dim as
openseat)

Insert a record or row in the schedule table that is the result of all of
this criteria (My.rst.Update.Add?)

Repeat until the need is satisfied (Loop)

I think if I understood how to create 1 iteration of this logic I could do it.
But I get hung up mostly on syntax and looping logic.

If my example is too complicated just disregard, I do appreciate your help.
Thanks

If you've used DAO before, the logic is nearly identical. The only major
shift in thinking with simple recordset operations is how you open a recordset.

Instead of setting a recordset variable to a recordset returned by
CurrentDB(), you create the recordset as a new object and THEN open it:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
'or just Dim rs As New ADODB.Recordset if you prefer

With rs
.Open "MyTableOrView", CurrentProject.Connection
Do Until .EOF
'Do your stuff
.MoveNext
Loop
.Close
End With
Set rs = Nothing 'pedantic, but often encouraged

If you're a newbie to both DAO and ADO, then I'd suggest looking at
something like this to get started:
http://msdn2.microsoft.com/en-us/library/ms807730.aspx

It's a bit more complex than necessary, as it's intended as a reference, but
it's certainly thorough. There are probably other ADO primers out there if
this is too detailed; maybe someone else will post one, cuz it's been too
long since I learned it for me to have any off-hand.

Rob

I have never used ADO for manipulating records, I would like to learn how to.

[quoted text clipped - 25 lines]

provide some code with similar logic?
Thanks

 

[jmersing via AccessMonster.com]

Thanks so much Rob, I'll do just that. I really appreciate you taking the
time to explain the logic and I will follow your advice. I'll post back with
my results.

D'oh!

Just after I wrote that, I realized that because I was thinking of it in
terms of a one-shot deal, I wrote the code fairly poorly in the end,
violating most of what I said about not opening and closing things
unnecessarily. I also neglected a fairly obvious "MoveNext"...sort of
necessary until you want to process the same record over and over again for
no good reason! <LOL>

You'd definitely be better off moving all the Set statements to a block at
the top of your code, and you'd probably also be better off moving the Open
statements to the top of your code as well, minus the WHERE statements for
the most part, then just using rsWhatever.Filter to filter each recordset as
you go through the loop.

Set rsWhatever = New ADODB.Recordset
rsWhatever.Open "SELECT * FROM [Some Table]",
CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
'*** OR ***
rsWhatever.Open "Some Table", CurrentProject.Connection, adOpenStatic,
adLockReadOnly, adCmdTable

Do Until rsNeed.EOF
rsWhatever.Filter "EmployeeID = " & rsAvailable!EmployeeID.Value
'^^^ Whatever the appropriate WHERE condition would've been for
' whichever table you're looking at currently.
rsNeed.MoveNext
Loop

Anyway, play around with it and see how it goes, then if you have specific
questions, you can post your code and we'll take it from there.

Rob

That makes sense Robert, so its just like opening a table or query but
virtual, you don't actually see it. I looked at the link also, thanks.

[quoted text clipped - 71 lines]