Recordset update causes

[esteiner]

This is a follow up to thread

http://www.tek-tips.com/viewthread.cfm?qid=1309921&page=1

I was able to receive some great help for my initial problem, but have hit a snag when trying to apply this new method to a similar subprocedure.

I have a second scrubbing procedure that I run. The routine searches for the first numeric character in Field A and updates Field B with the remaining characters. This routine runs in about 3 minutes for 200,000 records. The file takes about 80 minutes to defragment. The number of records will soon increase to over 1 million.

Is there a more efficent method of updating the recordset without the fragmentation? Any help would be greatly appreciated. Below is the code for this second routine.

Private Sub cmdSplitMPN_Click()
On Error GoTo E_Handle
Dim rs As Recordset ' Recordset object
Dim strTableName As String ' Name of table where export records originate
Dim strSQL As String ' SQL String
Dim intRecordNum As Long ' Record Number being dealt with
Dim strMPN As String
Dim I As Integer
Dim strPrefix As String
Dim strBasenum As String
Dim blHasNum As Boolean
Set db = DBEngine(0)(0)

' Create the table name from where the export records will orginate
strTableName = "pull_inv_BRE"
strQuote = Chr$(34)
strSQL = "SELECT ManfPartNum, CorePartNum FROM " & strTableName & ""
Set rs = db.OpenRecordset(strSQL)
With rs
While Not (.BOF Or .EOF)
strMPN = !ManfPartNum
For I = 1 To Len(strMPN)
blHasNum = False
If IsNumeric(Mid(strMPN, I, 1)) Then
'number found, exit
blHasNum = True
Exit For
End If
Next I

If (blHasNum = True) Then
strBasenum = Right(strMPN, Len(strMPN) - I + 1)
.Edit
!CorePartNum = strBasenum
.Update
End If
.MoveNext
Wend
.Close
End With

sExit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Reset
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit

End Sub

Any help would again be appreciated. I am really stuck in the mud here.

Thank you

-Charlie

 

[Golom]

Typed but not tested

Private Sub cmdSplitMPN_Click()
    On Error GoTo E_Handle
    Dim strTableName                As String
    Dim strSQL                      As String
    Set db = DAO.DBEngine(0)(0)

    strTableName = "pull_inv_BRE"
    
    strSQL = "UPDATE [" & strTableName & "] " & _
             "SET [CorePartNumber] = NumPart(manfPartNum) " & _
             "Where manfPartNum LIKE '*[0-9]*' "
             
    db.Execute strSQL
    
sExit:
    On Error Resume Next
    Reset
    Exit Sub
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit

End Sub

Public Function NumPart(fld As String) As String
    Dim n As Integer
    For n = 1 To Len(fld)
        If IsNumeric(Mid(fld, n, 1)) Then
            NumPart = Right(fld, Len(fld) - n + 1)
            Exit For
        End If
    Next
End Function

 

[Golom]

Its probably obvious but the NumPart function should be in a module and not in the form.

 

[esteiner]

Thank you for your help, but I am having a problem with this code. I apologize for my ignorance, but I am new to VBA.

I received the following error:

"Error 3265: Item not found in this collection"

This error usually indicates that the code references a field name incorrectly. I double checked the field names and this is not the problem. I commented out each line of code to isolate the issue. I believe issue is with this line:

"Set db = DAO.DBEngine(0)(0)"

I am using Access 2003. I'm sure that this is an easy fix, but I have not had any luck so far. Your help is greatly appreciated.

 

[Golom]

Try something like

strTableName = "pull_inv_BRE"
    
strSQL = "UPDATE [" & strTableName & "] " & _
         "SET [CorePartNumber] = NumPart(manfPartNum) " & _
         "Where manfPartNum LIKE '*[0-9]*' "

With DAO.DBEngine(0).OpenDatabase("C:\somepath\somedb.mdb")
   .Execute strSQL
End With

The syntax that you have would make db a reference to the first database in DAO's database collection.

 

[esteiner]

Thank you, this solved one problem, but I am still receiving another (I assume syntax) error.

I receive the Error: 3085 Undefined funtion "NumPart" in expression. I created a separate module as you suggested. I checked spelling and syntax and everything seems correct. Any suggestions?

Below is the code for the module that was created:

Option Compare Database

Public Function NumPart(fld As String) As String
Dim n As Integer
For n = 1 To Len(fld)
If IsNumeric(Mid(fld, n, 1)) Then
NumPart = Right(fld, Len(fld) - n + 1)
Exit For
End If
Next
End Function

Thank you again for you help so far. I think that we are very close.

 

[esteiner]

It looks as though I am running into a problem as Access will not allow a user defined function to be used within the query. Have you ever been able to run a query with user define funtion embedded into the SQL statement?

See this MS knowlege base article that I found here:

http://support.microsoft.com/kb/q180810/

Any other help would be appreciated as I have reached the limits of my knowledge.