# vbscript to change definition on dbf file



## jeffreygregg (Dec 14, 2011)

I'm trying to update a dbf file using vbs. I currently have a 4 digit employee number i.e 1000. i want to add the store number in front of that i.e 114. so i end up with 1141000. I'm close with the below code but the table definition has the employee field to only 5 digits. I can't seem to figure out how to change it in vbs. Please help as soon as you can. much appreciated.

Function OpenDBFConn(Path)
Dim Conn: Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""DBASE IV;"";" 
Set OpenDBFConn = Conn
End Function

'Open connection For DBF files In Folder
Dim DBConn
Set DBConn = OpenDBFConn("C:\Documents and Settings\Jeff\desktop\prpst\")

dim sn
sn = 114
DBConn.Execute "update prpst114 set employee = " & sn & " employee where employee > 999"

Dim prpst114
Set prpst114 = DBConn.Execute("Select * from [prpst114#DBF]")

msgbox ("Complete for store 114")


----------



## AlbertMC2 (Jul 15, 2010)

Hi

I do not know dBase or its particular flavour of SQL but from searching on the internet you might want to try something like:

```
DBConn.Execute "ALTER TABLE prpst114 ALTER COLUMN employee TYPE CHAR[7]"
```
You might want to increase CHAR[7] for future changes. You do not want to have the same problem in a few months time.

*PLEASE *make a backup of your database before doing this. After reading up on this it seems that changing the structure of a field, if it is populated with data, might cause loss of data. As I mentioned I do not know dBASE so do not know the rules of what you can change and how you can change it.
I am also presuming your table name is *prpst114*
You will want to run this *before *executing the update.


----------

