Add field to ACCESS with default value
I would like to insert a new field with a default value using Visual C ++ code. I wrote this:
CADODatabase pDB;
String strConnessione = _T("Provider=Microsoft.Jet.OLEDB.4.0;""Data Source=");
strConnessione = strConnessione + "MioDatabase.mdb";
pDB.SetConnectionString(strConnessione);
pDB.Open();
query.Format("ALTER TABLE TBProva ADD Fattore Double Default 0;");
pDB.Execute(query);
but this is not true. How can i do this? Can any of you write me the correct code?
In JET-SQL, you should be more specific with the syntax and add the word COLUMN to the "ALTER TABLE" clause. Exemple:
strSql = "ALTER TABLE MyTable ADD COLUMN MyField DECIMAL (28,3);"
strSql = "ALTER TABLE MyTable ADD COLUMN MyText TEXT(3);"
According to the help, you can define a default, but I've never tried it. This syntax is only valid in Access / JET database. I don't think it will work if you reach your access table via ODBC or ADO.
In such situations, you can change the structure of the database in other ways. You will find an example here. This was done with the DAO object model, but can be easily switched to the general ADO object model to be used with the ADODB connection.
EDIT: One "implicit ADO solution" (which means using ADODB join, but not ADO table objects, etc.) could be this:
(for connection string examples check here )
Dim myConnection AS ADODB.connection
set myConnection = New ADODB.connectionString
myConnection.connectionString = 'here is your connection string'
myConnection.open
myConnection.execute "ALTER TABLE myTable ADD Column MyField DECIMAL (12,3);"
myConnection.close
If you are working with an active mdb file, just write:
CurrentProject.Connection.Execute "ALTER TABLE myTable ADD Column MyField DOUBLE;"
What is it.
If you prefer to use ADO objects, add the ADOX library to your source code and use the Table.columns.append
. You will find here here
source to share
You are missing a keyword COLUMN
, for example.
ALTER TABLE TBProva ADD COLUMN Fattore Double Default 0;
In fact, I think you might be missing a part NOT NULL
for example.
ALTER TABLE TBProva ADD COLUMN Fattore DOUBLE DEFAULT 0 NOT NULL;
I say this because Jet does not support the use of the keyword DEFAULT
in SQL DML, so the only time it is applied DEFAULT
is when the column is declared as NOT NULL
and the column name is omitted from the column list in INSERT
. If the column was NULLable, how would you force the engine to apply the default ?!
source to share