add field using VB 1

[kap1]

Visual Basic 6 application with an Access 2000 dbs.

I need to write code to add two new fields to an existing table in the Access dbs using Visual Basic 6. This code will be sent to all existing customers to run.

I started with code like this:

Dim conModify As ADODB.Connection
Set conModify = New ADODB.Connection
conModify.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath    
strSQL = "ALTER TABLE AssetA1 ADD fieldA Byte;"
conModify.Execute strSQL
strSQL = "ALTER TABLE AssetA1 ADD fieldB Integer;"
conModify.Execute strSQL

How do I set the Default Value to "0" for fieldA?

When looking at the table design in Access fieldB has the field size set to "long integer" instead of just "integer". How can I set it to just "integer" using VB?
Thanks for any assistance.
Kim

 

[PHV]

strSQL = "ALTER TABLE AssetA1 ADD COLUMN fieldB SMALLINT"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kap1]

Thank you, that worked for the field size.

Can anyone answer how to set the default value for an Access field using VB?

 

[Golom]

That's a bit more complex. Using ADO you need to set a reference to Microsoft ADO Ext. 2.8 for DDL and Security (aka ADOX) and then

Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = cn
cat.Tables.Item("myTable").Columns("myColumn").Properties("Default") = 0

 

[abaldwin]

Are you wanting to set the values after you create the field just one time or are you relying on this field to default during future adds?

If you are just wanting to add the field and then populate it with zeros simply exectutre the above ALTER statements and then execute another sql string

UPDATE AssetA1 SET fieldB = 0



Andy Baldwin

"Testing is the most overlooked programming language on the books!