Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ALTER TABLE / ALTER COLUMN doesn't work in access

Posted on 2007-07-23
19
Medium Priority
?
3,922 Views
Last Modified: 2009-05-03
Hello  all,

For some reason the following syntax doesn't work for me , I'm trying to alter the column name "col" that will get a default value of  "0" , however, it just doesn't work. I'm using the exact syntax that microsoft using in http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx#acintsql_alttable  . I'm using Access 2003

what is the problem in the following syntax:

ALTER TABLE Table1
   ALTER COLUMN col INT DEFAULT 0;


Thanks in advance ,
Eyal
0
Comment
Question by:eylkrn
  • 7
  • 4
  • 4
  • +1
19 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19546536
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
0
 

Author Comment

by:eylkrn
ID: 19546635
Hi Patrick,

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

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19546679
try using DAO

    CurrentDb.Execute "alter table table1 alter column col number"
    CurrentDb.TableDefs("table1").Fields("col").DefaultValue = 0
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19546694
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
0
 

Author Comment

by:eylkrn
ID: 19546739
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.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19546786
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.
0
 

Author Comment

by:eylkrn
ID: 19546830
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
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19546846
eyal,

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

:)

Regards,

Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19546856
eyal,

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

Regards,

Patrick
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19546867
eylkrn,
how are you executing the sql codes you posted?
0
 

Author Comment

by:eylkrn
ID: 19547018
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
0
 

Author Comment

by:eylkrn
ID: 19547029
capricorn1, please see my comment above to Patrick :)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19547130
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;"
0
 

Author Comment

by:eylkrn
ID: 19547150
capricorn1,
yes. but it doesn't matter, any value I put there doesn't work.

Regards,
Eyal
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 19547205
Hello eylkrn,

The sample code works directly in Access. Try this:

* (Tools | Options), [Tables/Queries], check "SQL Server Compatible Syntax (ANSI 92)"
* Create new query, cancel "Add Table" and switch to SQL view
* Copy-paste the example from microsoft

If you have a table tblCustomers with a field Address, it will work as expected.

Without the compatibility mode, DEFAULT is not recognized as a Jet-SQL reserved word and the query fails with an error message. But other similar data definition queries do work directly within Access.

Cheers!
(°v°)
0
 

Author Comment

by:eylkrn
ID: 19547264
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
0
 
LVL 58

Expert Comment

by:harfang
ID: 19547270
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°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 19547291
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°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 19547341
> 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°)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question