Hello all!
The code presented below intends to split a table containing charges and payments (Imports Table). Charge records should be copied to the Charges Table and payment records to the Payments Table.
A natural primary key does not exist, so its being created based on the concatenation of four fields plus an identifier (001, 002, 003,…,00N) that will serve to identify that a same combination of fields was already present. This construction is by design as quickly identifying these “blocks of one type of charge” will be beneficial for other parts of the analysis.
On the payments side the records will be using a corresponding foreign key to establish the relationship with the charges table.
Loop Imports Table until EOF
If is a charge then it searches for the combination of fields MINUS the identifier (main part of primary key).
(this search is done using a different recordset then the one used for the update)
If no records are found it is the first time that charge is being logged
Logic to add first time primary key record
Else
Logic to add other recurring entries for the same charge
End if
Else
Logic to create a payment record, including correct association to the pertinent charge.
End if
Next record on Imports table
The problem is that the search for the partial primary key always returns zero record. Naturally, that is not correct. If I copy the same SQL expression generate by the code and use it as a independent query, that query finds the previous existing record with the same partial primary key.
For some reason (probably a mistake of mine) the underlying recordset is not being updated correctly so I am not seeing the correct new content of the table. Cant figure out why...
The relevant excerpt of the code is below.
Sub Split_Import_Table()
Dim strSQL As String
Dim msg1 As String
Dim rstImportTable As ADODB.Recordset
Dim rstChargesTable As ADODB.Recordset
Dim rstChargesTableClone As ADODB.Recordset
Dim rstPaymentsTable As ADODB.Recordset
Dim cnnTableConnection As ADODB.Connection
Set cnnTableConnection = New ADODB.Connection
Set cnnTableConnection = CurrentProject.Connection
Set rstImportTable = New ADODB.Recordset
Set rstChargesTable = New ADODB.Recordset
Set rstPaymentsTable = New ADODB.Recordset
rstImportTable.ActiveConnection = cnnTableConnection
rstChargesTable.ActiveConnection = cnnTableConnection
rstChargesTable.CursorType = adOpenDynamic
rstChargesTable.LockType = adLockOptimistic
rstPaymentsTable.ActiveConnection = cnnTableConnection
rstPaymentsTable.CursorType = adOpenDynamic
rstPaymentsTable.LockType = adLockOptimistic
rstImportTable.Open "SELECT * FROM import ORDER BY import.Field1, import.DOS, import.Field2, import.Field3, import.ID;"
Do Until rstImportTable.EOF = True
If (rstImportTable!GrossCharge <> 0) And ((rstImportTable!Pay1) = 0) And ((rstImportTable!Pay2) = 0) And ((rstImportTable!Pay3) = 0) Then
‘The primary key is made of fields concatenated. Wanna locate if that combination already exists in the table
‘ if exists add a new ordinal number at the end of combination
‘ in other words first time receives ‘001”, second time “002”, third time “003” so on so forth
strSQL = "Select tbl_Charges.ID From tbl_Charges Where tbl_Charges.Charge_Key Like ('PrimaryKey*')"
Set rstChargesTableClone = New ADODB.Recordset
rstChargesTableClone.ActiveConnection = cnnTableConnection
rstChargesTableClone.CursorType = adOpenStatic
rstChargesTableClone.LockType = adLockReadOnly
rstChargesTableClone.Open (strSQL)
‘ if record count is less than one is the first time for that PrimaryKey
‘ a new record is created on the table using the “concatenation of fields plus 001” as the primary key
If rstChargesTableClone.RecordCount < 1 Then
'Initial entry for a Charge (First entry for a PrimaryKey)
‘id is never zero so no record is selected
rstChargesTable.Open ("Select * From tbl_Charges where id = 0")
rstChargesTable.AddNew
rstChargesTable!Charge_Key = concatenation of fields & "." & "001"
rstChargesTable!ID = rstImportTable!ID
rstChargesTable!Field1 = rstImportTable!Field1
rstChargesTable!Field2= rstImportTable!Field2
.
.
.
rstChargesTable!FieldN = rstImportTable!FieldN
rstChargesTable.Update
rstChargesTable.Close
Else
'Subsequent entries for a same concatenation of fields
rstChargesTable.CursorType = adOpenKeyset
rstChargesTable.LockType = adLockOptimistic
rstChargesTable.Open ("Select * From tbl_Charges where id = 0")
rstChargesTable.AddNew
‘using record count will give the correct ordinal to add at the end of the concatenation of fields
rstChargesTable!Charge_Key = & "." & String(3 - Len(Trim(Str(rstChargesTableClone.RecordCount))), "0") & Trim(Str(rstChargesTableClone.RecordCount))
rstChargesTable!ID = rstImportTable!ID
rstChargesTable!ID = rstImportTable!ID
rstChargesTable!Field1 = rstImportTable!Field1
rstChargesTable!Field2= rstImportTable!Field2
.
.
.
rstChargesTable!FieldN = rstImportTable!FieldN
rstChargesTable.Update
rstChargesTable.Close
End If
rstChargesTableClone.Close
Set rstChargesTableClone = Nothing
Else
‘It’s a payment; logic of payment record creation is partially removed below
‘as it is not relevant for the problem that I am having
rstPaymentsTable.CursorType = adOpenKeyset
rstPaymentsTable.LockType = adLockOptimistic
rstPaymentsTable.Open ("Select * From tbl_Payments where tbl_Payments.id = 0")
rstPaymentsTable.AddNew
rstPaymentsTable.Update
rstPaymentsTable.Close
End If
rstImportTable.MoveNext
Loop
End Sub
Any help is always apreciated!
Thx,
4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
The code presented below intends to split a table containing charges and payments (Imports Table). Charge records should be copied to the Charges Table and payment records to the Payments Table.
A natural primary key does not exist, so its being created based on the concatenation of four fields plus an identifier (001, 002, 003,…,00N) that will serve to identify that a same combination of fields was already present. This construction is by design as quickly identifying these “blocks of one type of charge” will be beneficial for other parts of the analysis.
On the payments side the records will be using a corresponding foreign key to establish the relationship with the charges table.
Loop Imports Table until EOF
If is a charge then it searches for the combination of fields MINUS the identifier (main part of primary key).
(this search is done using a different recordset then the one used for the update)
If no records are found it is the first time that charge is being logged
Logic to add first time primary key record
Else
Logic to add other recurring entries for the same charge
End if
Else
Logic to create a payment record, including correct association to the pertinent charge.
End if
Next record on Imports table
The problem is that the search for the partial primary key always returns zero record. Naturally, that is not correct. If I copy the same SQL expression generate by the code and use it as a independent query, that query finds the previous existing record with the same partial primary key.
For some reason (probably a mistake of mine) the underlying recordset is not being updated correctly so I am not seeing the correct new content of the table. Cant figure out why...
The relevant excerpt of the code is below.
Sub Split_Import_Table()
Dim strSQL As String
Dim msg1 As String
Dim rstImportTable As ADODB.Recordset
Dim rstChargesTable As ADODB.Recordset
Dim rstChargesTableClone As ADODB.Recordset
Dim rstPaymentsTable As ADODB.Recordset
Dim cnnTableConnection As ADODB.Connection
Set cnnTableConnection = New ADODB.Connection
Set cnnTableConnection = CurrentProject.Connection
Set rstImportTable = New ADODB.Recordset
Set rstChargesTable = New ADODB.Recordset
Set rstPaymentsTable = New ADODB.Recordset
rstImportTable.ActiveConnection = cnnTableConnection
rstChargesTable.ActiveConnection = cnnTableConnection
rstChargesTable.CursorType = adOpenDynamic
rstChargesTable.LockType = adLockOptimistic
rstPaymentsTable.ActiveConnection = cnnTableConnection
rstPaymentsTable.CursorType = adOpenDynamic
rstPaymentsTable.LockType = adLockOptimistic
rstImportTable.Open "SELECT * FROM import ORDER BY import.Field1, import.DOS, import.Field2, import.Field3, import.ID;"
Do Until rstImportTable.EOF = True
If (rstImportTable!GrossCharge <> 0) And ((rstImportTable!Pay1) = 0) And ((rstImportTable!Pay2) = 0) And ((rstImportTable!Pay3) = 0) Then
‘The primary key is made of fields concatenated. Wanna locate if that combination already exists in the table
‘ if exists add a new ordinal number at the end of combination
‘ in other words first time receives ‘001”, second time “002”, third time “003” so on so forth
strSQL = "Select tbl_Charges.ID From tbl_Charges Where tbl_Charges.Charge_Key Like ('PrimaryKey*')"
Set rstChargesTableClone = New ADODB.Recordset
rstChargesTableClone.ActiveConnection = cnnTableConnection
rstChargesTableClone.CursorType = adOpenStatic
rstChargesTableClone.LockType = adLockReadOnly
rstChargesTableClone.Open (strSQL)
‘ if record count is less than one is the first time for that PrimaryKey
‘ a new record is created on the table using the “concatenation of fields plus 001” as the primary key
If rstChargesTableClone.RecordCount < 1 Then
'Initial entry for a Charge (First entry for a PrimaryKey)
‘id is never zero so no record is selected
rstChargesTable.Open ("Select * From tbl_Charges where id = 0")
rstChargesTable.AddNew
rstChargesTable!Charge_Key = concatenation of fields & "." & "001"
rstChargesTable!ID = rstImportTable!ID
rstChargesTable!Field1 = rstImportTable!Field1
rstChargesTable!Field2= rstImportTable!Field2
.
.
.
rstChargesTable!FieldN = rstImportTable!FieldN
rstChargesTable.Update
rstChargesTable.Close
Else
'Subsequent entries for a same concatenation of fields
rstChargesTable.CursorType = adOpenKeyset
rstChargesTable.LockType = adLockOptimistic
rstChargesTable.Open ("Select * From tbl_Charges where id = 0")
rstChargesTable.AddNew
‘using record count will give the correct ordinal to add at the end of the concatenation of fields
rstChargesTable!Charge_Key = & "." & String(3 - Len(Trim(Str(rstChargesTableClone.RecordCount))), "0") & Trim(Str(rstChargesTableClone.RecordCount))
rstChargesTable!ID = rstImportTable!ID
rstChargesTable!ID = rstImportTable!ID
rstChargesTable!Field1 = rstImportTable!Field1
rstChargesTable!Field2= rstImportTable!Field2
.
.
.
rstChargesTable!FieldN = rstImportTable!FieldN
rstChargesTable.Update
rstChargesTable.Close
End If
rstChargesTableClone.Close
Set rstChargesTableClone = Nothing
Else
‘It’s a payment; logic of payment record creation is partially removed below
‘as it is not relevant for the problem that I am having
rstPaymentsTable.CursorType = adOpenKeyset
rstPaymentsTable.LockType = adLockOptimistic
rstPaymentsTable.Open ("Select * From tbl_Payments where tbl_Payments.id = 0")
rstPaymentsTable.AddNew
rstPaymentsTable.Update
rstPaymentsTable.Close
End If
rstImportTable.MoveNext
Loop
End Sub
Any help is always apreciated!
Thx,
4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
