from a recordset to array (perhaps possible?)

[krzysrb]

hello all

i have just a simple question about the little thing i have been playing around with lately. I wanna try to make a schedule for this place i am working at, and i have all the preferred times of the workers in a DB table, and i have the following code and please if ya can, please point out some obvious mistakes that i am making. this thing is been pushin me up the wall for a week now, and this is the best i could come up with (a lil sad i know)

Option Explicit

Private Sub form_Activate()
'counters for loops
Dim i As Integer
Dim n As Integer

'arrays that are used for loops and to hold
'the schedule
Dim days(1 To 7) As String
Dim Time4Work(1 To 31) As Double
Dim work(1 To 7, 1 To 31) As String

'database related declaration
datSchedule.Visible = False
datSchedule.DatabaseName = "C:\db1.mdb"

Dim SQLStr As String



days(1) = "Monday"
days(2) = "Tuesday"
days(3) = "Wednesday"
days(4) = "Thursday"
days(5) = "Friday"
days(6) = "Saturday"
days(7) = "Sunday"

Time4Work(1) = 8 '8am
Time4Work(2) = 8.5 '8:30am
Time4Work(3) = 9
Time4Work(4) = 9.5
Time4Work(5) = 10
Time4Work(6) = 10.5
Time4Work(7) = 11
Time4Work(8) = 11.5 '...
Time4Work(9) = 12
Time4Work(10) = 12.5
Time4Work(11) = 13
Time4Work(12) = 13.5
Time4Work(13) = 14
Time4Work(14) = 14.5
Time4Work(15) = 15
Time4Work(16) = 15.5 '3:30pm
Time4Work(17) = 16
Time4Work(18) = 16.5
Time4Work(19) = 17
Time4Work(20) = 17.5
Time4Work(21) = 18
Time4Work(22) = 18.5
Time4Work(23) = 19
Time4Work(24) = 19.5
Time4Work(25) = 20 '...
Time4Work(26) = 20.5
Time4Work(27) = 21
Time4Work(28) = 21.5
Time4Work(29) = 22
Time4Work(30) = 22.5
Time4Work(31) = 23 '11pm


'SQL Statement to fill in the 2D array
'the first index is the number that corresponds to the
'day, second is the
'number that corresponds to the Time4Work. ie, the
'student available to work
'on Tuesday between 8 and 8:30 will be at the position
'work(2,1)

'For i = 1 To 7
' For n = 1 To 31
' if n=31 then
' sqlstr = SELECT [First Name] FROM [times] WHERE [Day] = days(i) AND ( [Start Time] <= 22.5 'handles the 22.5 till 23 shift
' datSchedule.RecordSource = sqlstr
' work(i, n) = datschedule.recordsource
' else
' sqlstr = SELECT [First Name] FROM [times] WHERE [Day] = days(i) AND ( [Start Time] <= Time4Work AND [End Time] >= Time4Work(n+1)
' datSchedule.RecordSource = sqlstr
' work(i, n) = datschedule.recordsource
' end if
' Next n
' Next i

End Sub

sorry for messiness, and as always i appreciate any help and suggestions you guys provide me with.

thanks
vlada .:have i mentioned that i am still a newbie to this game? :.

 

[Som76]

Hi

I have a solution with ADODB.

Dim con as New Adodb.Connection
Dim rs as New Adodb.Recordset
dim work as Variant

with con
.ConnectionString = &quot;...&quot;
.Open
end with

wirh rs
.Open &quot;Select ....&quot;,Con
if not .Eof then
work = rs.GetRows 'Returns all the records into an array
end if
End with
set rs = nothing
set con = nothing

' Now the work is converted to array and u can get ur result

 

[krzysrb]

hey Som76,

Your idea worked better than my gibberish, and i got the code to store it all into the variant-type variable. but how do i make a leap from the variant into array of strings is what bugs me now. i know that the values are in there, but how to get them out now is my problem.

here is the code i modified like you suggested:
Option Explicit

Private Sub Form_Activate()

Dim CONN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim WORK As Variant


CONN.Open _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & &quot;Data Source= C:\db1.mdb ;&quot;


With RS
.Open &quot;SELECT [First Name] FROM [times]&quot;, CONN
If Not .EOF Then
WORK = RS.GetRows
End If
End With

Set RS = Nothing
Set CONN = Nothing

End Sub

any help is a good help
vlada