ALTER TABLE / ALTER COLUMN doesn't work in access

Eyal,

How are you trying to run this?  As indicated in the article, you need to run it in code.
These sorts of operations are not available in the Access UI.

Patrick

Hi Patrick,

I am not using Access UI. I am using ADO with VB.

try using DAO

    CurrentDb.Execute "alter table table1 alter column col number"
    CurrentDb.TableDefs("table1").Fields("col").DefaultValue = 0

no need to reference Microsoft Dao x.x object library,

    CurrentDb.Execute "alter table table1 alter column col number"
    CurrentDb.TableDefs("table1").Fields("col").DefaultValue = 0

Hi capricom,

I know that DAO works but I want to do it with SQL. what is the problem to do with it ADO?
Why the way that Microsoft tell you to do it doesn't work? I don't understand.

eyal said:
>>what is the problem to do with it ADO?
>>Why the way that Microsoft tell you to do it doesn't work?

Please post the code you tried.

hi matthewspatrick,
I've posted the code on my first post:

ALTER TABLE Table1
   ALTER COLUMN col INT DEFAULT 0;

this is the code that microsoft suggest and I tried it also:

ALTER TABLE tblCustomers
   ALTER COLUMN Address TEXT(40) DEFAULT Unknown

http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx#acintsql_alttable

eyal,

Sorry if there was confusion.  I meant, "please post the VBA code you tried to execute."

:)

Regards,

Patrick

eyal,

Also, please post the error message you get, if any.

Regards,

Patrick

eylkrn,
how are you executing the sql codes you posted?

Hi Patrick,

Thanks for your help.
this is the error message:

Run-time error '-2147217900 (80040e14)':

[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.

this the code I'm using:

Option Explicit
' Add refs:
' Microsoft ActiveX Data Objects 2.x Library - ADODB
' Microsoft ADO Ext. 2.x for DDL and Security - ADOX
Dim myCatalog As ADOX.Catalog
Dim myDataBase As ADODB.Connection
Private Sub Command1_Click()
    Call CreateMDBfile  ' Create MDB file
End Sub
Sub CreateMDBfile()
Dim mySql As String

Set myCatalog = New ADOX.Catalog

If Dir(App.Path & "\myDB.mdb", vbDirectory) = "myDB.mdb" Then Kill App.Path & "\myDB.mdb"
myCatalog.Create ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\myDB.mdb;")

Set myDataBase = New ADODB.Connection

myDataBase.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
                & App.Path & "\myDB.mdb" & " ; DefaultDir=" & App.Path & ";"

mySql = "CREATE TABLE Table1;"
    myDataBase.Execute (mySql)
mySql = "ALTER TABLE Table1 ADD COLUMN MedicineID AUTOINCREMENT CONSTRAINT MedicineID PRIMARY KEY;"
    myDataBase.Execute (mySql)
mySql = "ALTER TABLE Table1 ADD COLUMN VisitID TEXT(50) DEFAULT 0;"
    myDataBase.Execute (mySql)
 
myDataBase.Close
Set myDataBase = Nothing
Set myCatalog = Nothing

End Sub

Regards,

Eyal

capricorn1, please see my comment above to Patrick :)

do you really want to have a default value of 0 in field VisitID which is a Text type data

"ALTER TABLE Table1 ADD COLUMN VisitID TEXT(50) DEFAULT 0;"

capricorn1,
yes. but it doesn't matter, any value I put there doesn't work.

Regards,
Eyal

Hi Markus,

Ok, it works!
However, How can I enable this option in the VB code? I am creating this DB in VB , how can I check this option? or is there "other similar data definition queries " to DEFAULT?

Regards,
Eyal

Additional note:

If you don't like cap's DAO solution, you can try the full ADO package, namely add a reference to ADOX. See:

Microsoft ADO Extensions 2.1 for DDL and Security
http://msdn2.microsoft.com/En-US/library/aa163991

This can be used to change the default values of plain Jet databases (without the compatibility mode).

Cheers!
(°v°)

If you intend to upgrade to SQL Server soon, you should check the compatibility mode once and for all in Access, and leave it alone. If you intend to use just Access (i.e. a Jet database), you should *not* check it and *not* use ANSI 92 data definition queries. Instead, use ADOX or DAO for that.

(°v°)

> How can I enable this option in the VB code?

    SetOption "ANSI Query Mode", True

Make sure you compact and repair the database after that. If it contains any code, you should also re-compile it. Note that this affects *all* queries, so that many will not work anymore.

(°v°)