Switching from Corel Paradox to Access 2010

  • Thread starter Thread starter CJC Digital
  • Start date Start date
C

CJC Digital

Recently one of the companies my firm works with has decided to migrate from
Corel Paradox 4.0 to Access 2010. While we haven't had any issues with moving
the tables over to the new system, I had a quick question about VBA
programming as we have a query which segments the fields of each user into
clusters which are compared with other clusters to eliminate duplicates.

Right now our main issue has been trying to get a similar query in VBA
however I haven't had much luck so does anyone else have any suggestions?

Below is a code sample I've found that I'm planning to repurpose but I
haven't been able to integrate it fully:

Sub Delete_Duplicates(strTableName As String)
' Deletes exact duplicates from the specified table.
' No user confirmation is required. Use with caution.
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim varBookmark As Variant

Set tdf = DBEngine(0)(0).TableDefs(strTableName)
strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
' Build a sort string to make sure duplicate records are
' adjacent. Can't sort on OLE or Memo fields,though.
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And _
(fld.Type
<> dbLongBinary) Then
strSQL = strSQL & fld.Name & ", "
End If
Next fld
' Remove the extra comma and space from the SQL
strSQL = Left(strSQL, Len(strSQL) - 2)
Set tdf = Nothing

Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
For Each fld In rst.Fields
If fld.Value <> rst2.Fields(fld.Name).Value Then
GoTo NextRecord
End If
Next fld
rst.Delete
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
End Sub
 
"I had a quick question about VBA
programming as we have a query which segments the fields of each user into
clusters which are compared with other clusters to eliminate duplicates."
- you need to explain this more fully. what does 'fields of each user' mean?
what is a 'cluster'?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Right now I haven't run the code yet because I'm trying to modify the code so
it compares the two duplicate records and removes the one which was updated
earlier.

Our current database system uses a central customer database, however we
also have smaller databases created for events our company holds, along with
a temp database we have for dumping new client information between database
updates.

When we updated the database in Paradox we had a script which would dump all
the records in the central file and then we would sort the database and the
script would create two arrays and then compare the data side by side for
duplicates.

By clustering, I mean that the script would compare the customer info fields
in groups of four or five, so we would have fields a-z and the script would
work a-d, d-h, and along those lines.

Right now I'm trying to modify the sample code to simply delete the older
record, which I think I'll be able to figure out however I wanted to ask as
VBA is very new to me -- generally I deal with PHP and SQL

The other issue is that I'm having trouble with function calls. Right now my
code is in a module, so I've been unable to run my code, but my guess is a
fairly basic function call would work here.
 
Just an update to my post from earlier today, I'm now having trouble with
passing arguments -- I'm not sure where/how/what to declare because I'm just
trying to call the sub function but Access is requiring arguments for the
main function and sub function call.

The code is below:

Function DupDelete(test As String)
test = DeleteDuplicateRecords()
End Function
Sub DeleteDuplicateRecords(strTableName As String)
' Deletes exact duplicates from the specified table.
' No user confirmation is required. Use with caution.
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim varBookmark As Variant

Set tdf = DBEngine(0)(0).TableDefs(strTableName)
strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
' Build a sort string to make sure duplicate records are
' adjacent. Can't sort on OLE or Memo fields,though.
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And _
(fld.Type
<> dbLongBinary) Then
strSQL = strSQL & fld.Name & ", "
End If
Next fld
' Remove the extra comma and space from the SQL
strSQL = Left(strSQL, Len(strSQL) - 2)
Set tdf = Nothing

Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
For Each fld In rst.Fields
If fld.Value <> rst2.Fields(fld.Name).Value Then
GoTo NextRecord
End If
If fld.Value = rst2.Fields(fld.Name).Value Then
If fld.Value("Date_updat") > rst2.Fields("Date_updat") Then
Delete rst2.Value
GoTo NextRecord
End If
If fld.Value("Date_updat") < rst2.Fields("Date_updat") Then
Delete fld.Value
GoTo NextRecord
End If
End If
Next fld
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
Return
Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Function
 
The structure for the DupDelete function should be

Function DupDelete(test As String)

[Some code here]

'Assign a value to the result of the function
DupDelete = [some value]

End Function

You would then use the value in one of several ways

'As a string
Msgbox ("This is a test. The function returned a value of " &
DupDelete("Ding"))

'A part of a SQL statement
INSERT INTO tblTest (txtComment) VALUES DupDelete("Ding")

'Testing for a value
If DupDelete("Ding") > 0

'Function returns True
If DupDelete then

'Function returns False
If Not DupDelete then

If you're wanting to create a function that test if the record is a
duplicate and should be deleted, you can spin out the logic from the main
sub along the lines of the example below. In this scenario, the rst1 and
rst2 declarations make the variables available to all functions and subs in
the module. As such, you could skip using parameters in the Function.
Function will be able to see them and work with them accordingly.

I have never had the pleasure of searching for duplicates, however if you
nest loops for a recordset, it could take time. I would *HIGHLY* recommend
adding something that communicates to your users the status. In the past,
I've used a striped down form with two labels that I've refressed to give
them a status. Its just a matter of adding
[Forms]![frmStatus]![Label1].caption = "Checking record " & i & " of " &
intRecordCountRst1 & " record(s)" followed by
[Forms]![frmStatus]![Label1].repaint. Where i is incremented on each pass of
the loop and intRecordCountRst1 is a record count of the recordset you're
looping through.

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

Sub FindDuplicates

[Open rst1 and rst1]

[Loop through the records]

[For each in rst1]

[For each in rst2]

If isDuplicate() then...

[Next]

[Next]

End sub

Function IsDuplicate()

[Code here]

IsDuplicate = True (or conversely use isDuplicate = False, depending on how
you design the function)

End function
 
Back
Top