Drop Default Syntax Error

[LaDeana1]

I'm trying to drop the Default on a field in my MSACCESS 2000 table

Here is my Code:

dbs.execute "ALTER TABLE tblJobs ALTER COLUMN JOBNoOnFile_R DROP DEFAULT"

I'm getting syntax error and don't see why. Does anyone know what I'm missing

Thanks for your help,
LaDeana

 

[cjashwell]

It's slightly unusual to use SQL ALTER TABLE syntax with Access, and it will only work with Jet databases. It would be more normal to use DAO methods with TableDefs (or ADO methods with Connections) to make changes to table structures in Access.

Now, are you trying to remove a Default Value setting from a field? If so, you can't do this using the ALTER TABLE syntax, AFAIK.

A simple way to do this in DAO for a field with a datatype of Text would be something like:

Private Sub AlterTable()

Dim dbs As DAO.Database
Set dbs = CurrentDb()

dbs.TableDefs("myTable").Fields("myCol").DefaultValue = ""

End Sub

HTH get you on your way.

 

[LaDeana1]

Thanks cjashwell! Worked perfectly! A star for you. I'm used to using SQL and forget that I can easily go through the jet.