[dba-VB] Splitting a name

John:
No doubt there are many more cleverer answers then this and this may be
tooooo slow for your needs.

Function jc()
  ' will take the 1st series up to space as Xname, right series back to
space as zname and bit in the middle as yname
  Const conQuote As String = """"
  Dim sql As String
  sql = "update tblTest set "
  sql = sql & "Xname = left(Searchname,instr(Searchname," & conQuote & " " &
conQuote & ")-1) ,"
  sql = sql & "Yname = Mid(searchname, InStr(searchname, " & conQuote & " "
& conQuote & ") + 1, InStrRev(searchname, " & conQuote & " " & conQuote & ")
- InStr(searchname, " & conQuote & " " & conQuote & ")),"
  sql = sql & "Zname = mid(Searchname,instrrev(Searchname," & conQuote & " "
& conQuote & ")+1)"
  CurrentDb.Execute (sql)
End Function

Max
 

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, April 19, 2008 5:08 PM
To: VBA
Subject: [dba-VB] Splitting a name

I have a table with eight million records, with a single name field, and I
have a library that splits the name into components.  What I need now is a
generic method of getting the name out of a table and updating a specific
set of fields back in the table.  It has to do so QUICKLY since I have to
update 80 million names.

In this case I have a single table

Name

NamePrefix
LastName
FirstName
MiddleName
NameSuffix

Name already exists and contains a name in a consistent format LName FName
MName.

The other fields I have added to my table.

So what I am looking for is a general strategy.  I assume I will open a read
recordset with the PKID and the name field, then iterate through this
recordset performing the split.

Once I do that should I:

Build a dynamic SQL Update query and execute that?

Build a stored procedure that I pass the PKID and the name parts to which
then updates the data?

Use an ADO recordset to do this?

some other method entirely?

I am assuming at this point that because of the sheer size of the data I do
not want to try and do this in an ado recordset.  Eight million records
sitting in data at one time does not sound fun.

All ideas gratefully accepted.

--
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com