pseudo php/java code to vba translation help

[Kage Musha]

I'm familiar with php and java but not with VBA, now I've got pseudo code which I need to translate into VBA. I've translated abit but I'm stuck now.

I'm getting an error with the Recordset also am I using the right thing as replacement of the PHP mysql_result?

I've translated this sofar

Private Sub Command26_Click() 
Dim cnn As Connection 
Dim strSQL As String 
Dim no_rows 
Dim no_choices, max_courses, p, i As Integer 
Dim Recordset As New ADODB.Recordset 

    no_choices = 4   'the number of choices each student has 
    max_courses = 2  'the number of courses a stundent is allowed (and has to) select p = 0 
    Do While i <= no_choices 
         p = p + 1 
     
    Set cnn = CurrentProject.Connection 

    Set Recordset = Command.Execute("SELECT _fd_id, choice" + p + " FROM N_Data_Tbl") 
    Set no_rows = Command.Execute("SELECT COUNT(*) FROM N_Data_Tbl") 


    ReDim all_fd_ids(no_rows) As Integer 'Array of all _fd_id's 
    ReDim all_choices(no_rows) As String 'Array of Strings to save all 'p' choices... 

    i = 0 
    Do While i <= no_rows 'this fills the arrays with the data from the query 
        i = i + 1 
       all_fd_ids(i) = Recordset(i, 0) 
       all_choices(i) = Recordset(i, 1) 
    Loop 
    Loop 
    

End Sub

And this is the Pseudo Code

/* DON'T FORGET THAT THIS IS PSEUDO-CODE ITS NOT VB OR VBA !!! MORE LIKELY SOMETHING LIKE JAVA / PHP 
   It just tells you, what you should do to achieve what you want!*/

int no_choices=4;	//the number of choices each student has
int max_courses=2;	//the number of courses a stundent is allowed (and has to) select

for(int p=0;i<no_choices;p++)
{
	mysql_query("SELECT _fd_id, choice" + p + " FROM N_Data_Tbl");

	int 	no_rows = mysql_num_rows();	

	int	all_fd_ids[no_rows];	//Array of all _fd_id's
	String 	all_choices[no_rows];	//Array of Strings to save all 'p' choices...
	
	for(int i=0;i<no_rows;i++)	//this fills the arrays with the data from the query
	{
		all_fd_ids[i]  = mysql_result(i,0);
		all_choices[i] = mysql_result(i,1);	
	}

	for(int i=0;i<no_rows;i++)
	{
		mysql_query("SELECT MaxStudents FROM Course WHERE CourseName = '" + all_choices[i] + "'");
		int MaxStudents = mysql_result(0,0);
		mysql_query("SELECT * FROM Rel_SelectedCourses WHERE CourseName = '" + all_choices[i] + "'");
		if (MaxStudents > mysql_num_rows())	// if there are free places left (the maximum number of stundents is higher then the number of stundents which are assigned to the course) assing the student to the course
		{
			mysql_query("SELECT * FROM Rel_SelectedCourses WHERE _fd_id=" + all_fd_ids[i]);
			if (mysql_num_rows()<max_courses)
			{
				//the following line inserts 1 line to the Rel_SelectedCourses table containing the currently selected student and choice (selected by the index "i")
				mysql_query("INSERT INTO Rel_SelectedCourses (_fd_id, CourseName) VALUES (" + all_fd_ids[i] + ", '" + all_choices[i] +"')";
			}
		}
	}
}


////////////////////////////////  OUTPUT ROUTINE  ///////////////////////////

int max_courses=2;	//should be a global variable

mysql_query("SELECT _fd_id, gname, iname, lname FROM N_Data_Tbl ORDER BY _fd_id");

int no_rows = mysql_num_rows();

int 	all_fd_ids[no_rows];	//Array of all _fd_id's
String 	all_names[no_rows][3];	//2-dimensional Array, to save all needed strings... in this case names

for(int i=0;i<no_rows;i++)	//fills the upper arrays
{
	all_fd_ids[i] = mysql_result(i,0);
	all_names[i][0] = mysql_result(i,1);				
	all_names[i][1] = mysql_result(i,2);				
	all_names[i][2] = mysql_result(i,3);				
}

String	selected_courses[no_rows][max_courses];

for(int i=0;i<no_rows;i++)
{
	mysql_query("SELECT * FROM Rel_SelectedCourses WHERE _fd_id=" + all_fd_ids[i] + " ORDER BY _fd_id");
	num_rows=mysql_num_rows();
	for(p=0;p<num_rows;p++)
	{
		selected_courses[i][p]=mysql_result(1,p);
	}
}

//we have filled all arrays now with the data we need to achieve the following output:
// gname | iname | lname | course1 | course2

for(int i=0;i<no_rows;i++)
{
	print all_names[i][0];
	print all_names[i][1];
	print all_names[i][2];
	for(p=0;p<max_courses;p++)
	{
		print selected_courses[i][p];
	}
	print endline;
}

//Ofcourse you will have to put this in a table instead... this is kept dynamic, so that you are able to change the number of courses for each stundent, if you don't need this you can still keep it - it also works for 2 courses well ;)


 example for JOIN: "SELECT N_Data_Tbl.gname, N_Data_Tbl.iname, N_Data_Tbl.lname, Rel_SelectedCourses.CourseName FROM N_Data_Tbl, Rel_SelectedCourses WHERE N_Data_Tbl._fd_id=Rel_SelectedCourses._fd_id" <--- would be a possible but bad join.... doesn't give it well formated

 

[Kage Musha]

I'm not asking anyone to translate the entire code, only help me with the Recordset stuff

 

[kousotsu]

if you submit a .zip with your sample db, it might be easier to get help. just by looking at the code isn't necessarily the best way to get a solution - there might be an easier method by querying, etc..

 

[Mile-O]

I changed the title of this thread as nothing is urgent here.