by Anil Iyengar (4 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Wed 28th June 2000
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Combines 2 different tables i.e Oracle or Access using Array.
API Declarations
‘THE MEMP IS THE MASTER TABLE AND THE OTHER 2 ARE THE CHILD.
‘BOTH TATTEND AND TALLWDED HAVE NO RELATION WHATSOEVER.
‘I HAVE TO COMBINE THEM BOTH USING THE EMPNO WHICH I HAVE ASSUMED TO
‘BE COMMON AND SO IT IS ORDERED BY THEM.
‘YOU CAN ALSO USE ANY TABLE THAT WAY.
‘JUST MAKE SURE WHICH TABLE YOU TO LINK
‘Put this in the general declaration of the form
Dim con As New ADODB.Connection’the connection variable
‘These are the recordset variables .i have used 6 to just show how each of them work
‘you can even reduce it .But I feel it is good to use the no. of recordset below and then
‘set it to nothing later in the Form_Unload ( )
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs1a As New ADODB.Recordset
Dim rs2a As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
‘3 Array element of Variant type.Each array’s function is described below
Dim Arr1() As Variant
Dim Arr2() As Variant
Dim Arr3() As Variant
Dim i, j As Integer
‘These variables keep the count of records.
Dim rs1cnt, rs2cnt, rs3cnt As Integer
Dim max As Integer
VBcoders
‘Here we open the connection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;_
Data Source=C:\MyDocuments\Payroll\PAYROLL.mdb;"_
con.Open
Call Getrows( )’Call the function
End Sub
Private Sub Form_Unload(Cancel As Integer)
‘Here on the form unload we bring all the array value back 0
Erase Arr1()
Erase Arr2()
Erase Arr3()
End Sub
Function Getrows()
On Error Resume Next
‘just error handled it cause it gave me an error ,just comment the above line to see the error ‘message
‘get the empno from memp table
Set rs3 = con.Execute("select empno from memp")
Arr3() = rs3.Getrows’let the array hold the records
Set rs3 = con.Execute("select count(*) from memp")’get the count of records from memp
rs3cnt = rs3(0) ‘store the no. of records in this variable
For u = 0 To rs3cnt – 1 ‘now check which empno it is
Set rs1a = con.Execute("select count(*) from t_attend where empno ='" & Arr3(0, u) & "'")
‘get count of this table using array element as parameter.
Set rs2a = con.Execute("select count(*) from t_allwded where empno ='" & Arr3(0, u) & "'")
‘get count of this table using array element as parameter.
rs1cnt = rs1a(0)’store the count
rs2cnt = rs2a(0)’store the count
Set rs1 = con.Execute("select * from t_attend where empno ='" & Arr3(0, u) & "'")
‘get the records for the empno using the array element as parameter
Set rs2 = con.Execute("select * from t_allwded where empno ='" & Arr3(0, u) & "'")
‘get the records for the empno using the array element as parameter
Arr1() = rs1.Getrows’store it in array
Arr2() = rs2.Getrows’store it in array
If rs1cnt > rs2cnt Then’now compare the count of records from both the tables
max = rs2cnt’store the highest 1
Else
max = rs1cnt
End If
For i = 0 To max – 1 ’now loop for rows
For j = 0 To 7 ‘loop for cols for table #1 (i.e TATTEND Table)
Debug.Print Arr1(j, i), ‘I have used it to print this in the Immediate Window
Print #1, Arr1(j, i), ‘This is used to print the output in a text file.
Next j
For k = 0 To 7’loop for cols for table #2 (i.e TALLWDED Table)
Debug.Print Arr2(k, i), ‘I have used it to print this in the Immediate Window
Print #1, Arr2(k, i), ‘This is used to print the output in a text file.
Next k
Debug.Print
Print #1,print
Next i
If rs1cnt > rs2cnt Then ‘if record count of table #1 is greater
For i = max To rs1cnt - 1
For j = 0 To 7
Debug.Print Arr1(j, i),
Print #1, Arr1(j, i),
Next j
Debug.Print
Print #1,
Next i
End If
If rs2cnt > rs1cnt Then ‘if record count of table # 2 is greater
‘now if the table #1 has no records then this line prints records
‘in the same columns as that of table #2 leaving space between the 2
‘tables .
For i = max To rs2cnt - 1
For j = 0 To 7
Debug.Print ,
Print #1, ,
Next j
For j = 0 To 7
Debug.Print Arr2(j, i),
Print #1, Arr2(j, i),
Next j
Debug.Print
Print #1,
Next i
End If
Next u
End Function
‘Here I have used the ‘Open File’ statement and ‘Kill File’ statement so that I could get the data
‘printed to a text file and print a report .I also think the Getrows( ) functions gets the records according ‘to the rows and no. of ‘columns in your table so I haven’t used the LBOUND or UBOUND for the ‘array to keep a count of it because of Getrows( ).
‘Here instead of printing it in a text file or the Immediate window we can create a table using all the columns u need and then can
‘create a VIEW sort of thing and print a report that shows u exactly like the Immediate window because the text file would be rather
‘tedious formatting every row to their specific length, as we cannot set the textwidth for each array element as we don’t know what it ‘stores in it. So you can use the INSERT statement to insert all the data in the table .Try using the INSERT statement between the for ‘loop which handles the columns.
‘Use the INSERT statement like “INSERT INTO TABLE <table_name>(column_name) VALUES(‘” & arr2(0,i) & “’)”
‘where arr2 is the array and 0 is the column and (i) is the row data”
‘I haven’t tried doing this but if u have after checking this code out, please inform me .I will be it tiring it out too.
‘If you think anything else is possible and if the code is not correct, then please let me now.
‘This code wouldn’t be possible if it wasn’t for my friend Jitesh Thaker who solved the bug.
‘For any details please contact me at [email protected] or my friend [email protected].
