trying to set default properties with ALTER TABLE help?

I have read through about a 4 pages of threads trying to find a solution and it could be there isnt one. I am using ODBC provider to connect to my Access database. I need to add two fields and set one field default settings to 'FALSE'.

The following gives me a syntax error in constraint clause. I have tried a variety of diffrent ways to do this, but I am beginning to think, you cant set the default settings.

I would appreciate any help.

VB Code:

'modify table
    Call CreateConnection(objConn)
        objConn.Execute "ALTER TABLE Documents ADD n_mp3 TEXT(50) NOT NULL"
        objConn.Execute "ALTER TABLE Documents ADD n_wavBol TEXT(50) CONSTRAINT DEFAULT 'False'"
    Call CloseConnection(objConn)

I only found 11 threads for Access (SQL varies by DBMS ;) ), and the suggestion each time was similar to what you have, except for one minor change...

Have you tried without the Constraint keyword?
eg:

VB Code:

objConn.Execute "ALTER TABLE Documents ADD n_wavBol TEXT(50) DEFAULT 'False'"

Yes I have tried this but it does not add the field and I get an error

-2147217900,"[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.","This error created on:",#2006-01-19 07:53:49#

:)

--edited

I don't know if this makes any difference but the database is MS Access 2000 file format.

How about you try it with OLEDB provider?

I tried but I ran into problems and didn't know how to fix them. In my module I have this set up;

VB Code:

Sub CreateConnection(objConn As Connection)
 
' CONNECTION STRING FOR THE DATABASE
'    AccessConnect = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
'                    "DBQ=Westfield.MDB;" & _
'                    "DefaultDir=" & App.Path & ";"
 
AccessConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=Westfield.mdb;" & _
                 "DefaultDir=" & App.Path & ";"
                     
                     'MsgBox AccessConnect
 
    ' Create Connection Object and open it
    Set objConn = New ADODB.Connection
 
    objConn.CursorLocation = adUseClient
 
    ' Trap any error/exception
    On Error GoTo AdoError
 
    objConn.ConnectionString = AccessConnect
    objConn.Open
 
    Exit Sub
 
    ' ADO Error/Exception Handler
AdoError:
    ErrNumber = Err.Number
    ErrSource = Err.Source
    ErrDescription = Err.Description
 
    'AdoErrorEx List1, objConn
 
End Sub
 
Sub CloseConnection(objConn As Connection)
 
    objConn.Close
    Set objConn = Nothing
 
End Sub

When I run the application I get an error when I call the objConn and bombs out in the With OBJRS statement on the .ActiveConnection = objConn in the code below. I am not sure why. Maybe I am missing something in the References? Do you know why this might happen?

VB Code:

'connect to database, generate ID, add ID to text box
    Call CreateConnection(objConn)
 
    'MsgBox objConn.State
    Set objRs = New ADODB.Recordset
 
    With objRs
        .ActiveConnection = objConn
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = "select * from Campaign where 2=3"    '2=3 basically creates a empty record set
        .Open
    End With
 
    '  ADDING RECORDS FROM TEXTBOX TO DATABASE
    With objRs
 
        .AddNew
 
        '///// ADD ID TO DATABASE
        gID = generateID()
        'MsgBox "this" & gID
        .Fields!c_id = gID
 
        '//// ADD ID to Text box
        'frmIntro.txtCPID.Text = checkstr(.Fields!c_id)
        frmIntro.txtGlobalID.Text = checkstr(.Fields!c_id)
        GlobalID = checkstr(.Fields!c_id)
 
        .Update
    End With
 
    objRs.Close
    Set objRs = Nothing
 
    Call CloseConnection(objConn)

Its always helpful to post the error information...

Remove the DefaultDir portion of the connection string, it is not a valid setting in OLEDB...

VB Code:

AccessConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & App.Path & "\Westfield.mdb;"

Ok, befor I change all my data connections (I will need some help with this, because I have always used the ODBC provider). I need to know if switching to this new OLEDB is:

1. is it going to do what I want and,
2. will I have any problems updating my app on the clients pc.
3. Is there any other way to modify the table structure using the ODBC provider?