trying to set default properties with ALTER TABLE help?

[Navarone]

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.

'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)

[si_the_geek]

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:

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

[Navarone]

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.

[dee-u]

How about you try it with OLEDB provider?

[Navarone]

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

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?

'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)

[brucevde]

Its always helpful to post the error information...

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

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

[Navarone]

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?