Add Field Onto Access Table

[GarstonMassive]

Hi guys,

I'm trying to add a new field to a table in a local Access database. Is this possible and if so what's the syntax?

Thanks in advance.

 

[PHV]

Execute a DDL instruction like ALTER TABLE ... ADD COLUMN ...

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

 

[GarstonMassive]

Thanks for your reply PHV. I'm actually an Access developer and I'm trying to do this in a VB Script which I know hardly anything about.

Can you give me a few more pointers please even if it's a link to more details.

Cheers.

 

[PHV]

How do you connect to your Access DB from VBScript ?

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

 

[GarstonMassive]

I have used some VB Script before to do something in the DB and this is how I connected:

Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase "C:\Folder\MyDat.mdb"
Set db = objAccess.CurrentDB

Hope this helps.

 

[PHV]

You may try either:
objAccess.DoCmd.RunSQL "ALTER TABLE ... ADD COLUMN ..."
or:
db.Execute "ALTER TABLE ... ADD COLUMN ..."

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

 

[GarstonMassive]

Thanks PHV I've got it to add a new column but not sure how to set a default value of zero to this Number type field.

A nudge in the right direction please.....

 

[GarstonMassive]

What's wrong with this syntax:


db.Execute "ALTER TABLE Table1 ADD COLUMN fldNumber LONG DEFAULT 0

 

[barny2006]

phv,
if you have more than one column that you want to add, do you separate them with a comma? like this?
ALTER TABLE Table1 ADD COLUMN field1, field2, field3......

 

[PHV]

GarstonMassive,
JetSQL lacks the DEFAULT clause ...

if you have more than one column that you want to add
You may add only one column per alter table instruction.

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

 

[GarstonMassive]

PHV thanks for all your help on this matter.