Use ADO to change case in existing SQL Dbase

[AlexIT]

I have not tried this yet, because I need to take the database offline to do it...but here is what I have put together:

Set conn = New adodb.Connection
conn.Open "DSN=ERP;" & "TRUSTED_CONNECTION=YES"
statement = "SELECT DB_ItemID, DB_ItemName, DB_UnitMeasure, DB_Classification, DB_ItemTypeCode, DB_ProdFam, DB_ProdModel FROM DBASE ORDER BY DB_ItemID"
Set rs = conn.Execute(statement, , adCmdText)

row = 0
col = 7
ReDim aERP(15000, 7)

ItemID = 0
ItemName = 1
UnitMeasure = 2
Classification = 3
ItemTypeCode = 4
ProdFam = 5
ProdModel = 6

Do While Not rs.EOF
For i = 0 To col - 1
If rs.Fields(i).Value <> vbNullString Then
aERP(row, i) = StrConv(rs.Fields(i).Value, vbUpperCase)
End If
Next i
rs.Edit
rs!DB_ItemID = aERP(row, ItemID)
rs!DB_ItemName = aERP(row, ItemName)
rs!DB_UnitMeasure = aERP(row, ItemUnitMeasure)
rs!DB_Classification = aERP(row, Classification)
rs!DB_ItemTypeCode = aERP(row, ItemTypeCode)
rs!DB_ProdFam = aERP(row, ProdFam)
rs!DB_ProdModel = aERP(row, ProdModel)
rs.Update
row = row + 1
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Of course this is only changing the case of those fields that have been entered incorrectly over the years, and (hopefully) not changing anything else.

Any suggestions? Comments?

Thanks for your help,
Alex

 

[fredericofonseca]

if your database allows for the lower/upper functions why not update the records directly with the sql command instead of using the slower Recordset method.

The following would work in SQL Server (and others)

Set conn = New adodb.Connection
conn.Open "DSN=ERP;" & "TRUSTED_CONNECTION=YES"
statement = "update DBASE set DB_ItemID = upper(DB_ItemID) ..."

conn.Execute statement ...


As for your comment that you need to shutdown the database.

You are the second person that on two days mentions that "I can not do it because it's production".
This is just against all good development practices. If you are developing something, you either have a copy of production to test, or you have a test environment with less data where you test your code.

So on your particular case setup another database or table name, copy the prodution data (or part of it) into the new table and then TEST IT THERE before going to production.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[AlexIT]

Thanks, I have not used the upper/lower functions in SQL before. I will have to use the recordset method partially anyway (to correct things like Each instead of EA. and base-board instead of baseboard, etc.) but anything I can do to speed up part of the cleanup is good.

I have to make an offline copy to test but I need two layers of management approval before I can get that done. Then after an independent auditor reviews my code, verifies the changes performed on the offline copy, and signs off on the idea I am golden...I can imagine the time something like this must take with a company with more than 30 people.