Add a field in ACCESS with a default value

Question

I'd like to insert a new field with a Default value using Visual C++ Code. I have 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 it isn't correct. How can I do for doing it? Someone of you Can write me the just code? Thank you

Answer 1

Thank you but in Visual C++ how can I do?

Answer 2

In JET-SQL language you have to be more specific with the syntax and add the 'COLUMN' word in the 'ALTER TABLE' sentence. 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 value but I never tried it. This syntax is only valid with Access/JET database. I do not think it will work if you reach your access table through ODBC or ADO.

In such situations, you can change your database structure through other means. You'll find an example here. This was made with DAO object model but can be easily switched to common ADO object model to be used with an ADODB connection.

EDIT: one "implicit ADO solution" (meaning using the ADODB connection but not the ADO tables objects etc" can then be the following:

(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 the active mdb file, just write:

CurrentProject.Connection.Execute "ALTER TABLE myTable ADD Column MyField DOUBLE;"

That's it.

If you prefer to use ADO objects, please add ADOX library to your code source and use the Table.columns.append method. You'll find an example here

Answer 3

Thanks but I use ADO in which manner can I change that code? Please help me

Answer 4

You are missing the COLUMN keyword e.g.

ALTER TABLE TBProva ADD COLUMN Fattore Double Default 0;

Actually, I think you might be missing the NOT NULL part too e.g.

ALTER TABLE TBProva ADD COLUMN Fattore DOUBLE DEFAULT 0 NOT NULL;

I say this because Jet doesn't support the use of the DEFAULT keyword in SQL DML, therefore the only time a DEFAULT will be applied is when the column is declared as NOT NULL and the column name is omitted from column list in an INSERT. If the column were NULLable, how would you get the engine to apply the default?!

Answer 5

Grandissimo.... sei un mito....

ALTER TABLE TBProva ADD COLUMN Fattore DOUBLE DEFAULT 0 NOT NULL;

era la soluzione giusta Grazie mille!