NOT NULL WITH DEFAULT and ACCESS

[gigit]

Hi to all.
I want to set a numeric column in an Access d.b., to NOT NULL WITH DEFAULT as in SqlBase
Asking for the correct syntax in an Access forum, i got that:
"ALTER TABLE Pippo ALTER COLUMN c_nome > Int NOT NULL DEFAULT 0"
and
"CREATE TABLE Pippo (c_nome Int NOT NULL DEFAULT 0)"
but I get an error when trying it from SqlTalk or SqlWindows.
ACCESS:-3551[Microsoft][Driver ODBC Microsoft Access] Syntax error in CREATE TABLE.

I am connected via ODBC with connection string in the Sql.ini file.
REMOTEDBNAME=MANAGER,Driver=Microsoft Access Driver (*.mdb);Dbq=G:\database\access\manager.mdb;MaxBufferSize=2048;PageTimeout=5;UID=admin;


They told me to use Currentproject.Connection.Execute "ALTER TABLE Pippo ALTER COLUMN c_nome Int NOT NULL DEFAULT 0" but how substitute this sintax to Gupta?

Thank you.

[Jeff Luther]

My guess is that it's the Access/ODBC router itself -- as shown in the error -- that's is balking at the syntax. (It must parse out the string, it seems.) You might try the create/alter of your table with that syntax from directly in Access and see if that works.

If it works in Access, then likely ODBC doesn't support that. Though I'm a bit surprised the router parses the string, rather than just letting it pass through to the DB. If it does not work in Access, something else is wrong. Is that syntax dependent on a certain version of Access?

[gigit]

Hi Jeff. Thank you for your answer.

Following access forum hints, I did that. I created and executed one sub like this from inside Access:

Sub a1()
CurrentProject.Connection.Execute "CREATE TABLE Pippo (c_nome Int NOT NULL DEFAULT 0)"
End Sub

and it runs. The table has been created with predefined value 0. Now the problem is that my customers don't have Access as a part of Office. I only deploy the d.b..
So?

[Jeff Luther]

So... It sounds like you may have to get a runtime license for each application with DB you release.

This link: http://office.microsoft.com/en-us/acces ... 61033.aspx
would seem a good place to start looking for how to do that. FYI, I searched the string: ms access runtime license
and that was the first link which appeared.

[gigit]

Many thanks, Jeff.
But I think I will override the problem in an other way.
Is there a free edition of SqlBase like SqlServer Express?

[gigit]

Hi. In the Access forum I got this answer.
The DEFAULT statement is correctly interpreted only in ADODB connections. so I must try to connect via ADODB "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\database\access\manager.mdb"
Thanks.

[Jeff Luther]

Thanks for the update! That info. might help someone else sometime.

P.S. "Is there a free edition of SqlBase" -- no.