ADOX default value with Access 2000

[Robert Mooney]

I have read the various messages relating to this subject
and applied the suggested patches/procedures. I still
receive error 3421. There appears to still be a problem
with Access 2000 and VB.

[Steven Bras]

If you'll elaborate we may be able to assist.

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

[Will Goldring]

Same here. Still getting error 3421 when trying to set the deafult value -
can read default
if one is already set. Help!

"Steven Bras" <stevenb...@microsoft.com> wrote in message
news:yrRKyc9...@cppssbbsa01.microsoft.com...

[Will Goldring]

Same here. Still getting 3421 with all suggested patches and fixes...

"Robert Mooney" <r...@thomasamerica.com> wrote in message
news:1d6ea01c137d5$274ffdc0$b1e62ecf@tkmsftngxa04...

[Will Goldring]

I'm also still having problems with default despite upgrading jet 4.0 with
SP5.
Now, while I can read the default value of a column, I still can't set it.
I get an error msg stating that the type is wrong for the application.
The col is typed as adwvarchar and I'm trying to set the default to a fixed
length
string, e.g., "foo".

"Steven Bras" <stevenb...@microsoft.com> wrote in message
news:yrRKyc9...@cppssbbsa01.microsoft.com...

[Robert Mooney]

Here are my steps to reproduce the error:

Using Microsoft Visual Basic 6.0 (SP4)

1. Start new standard exe

2. Under References check:
Microsoft ActiveX Data Objects 2.5 Library
Microsoft ADO Ext. 2.1 for DLL and Security

3. Run the following code:

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim col As ADOX.Column
Dim tbl As ADOX.Table

Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = "bobtest.mdb"
cn.Open

Set cat = New ADOX.Catalog
cat.ActiveConnection = cn

Set tbl = New ADOX.Table
tbl.Name = "new_files"
Set col = New Column
Set col.ParentCatalog = cat
col.Name = "stringField"
col.Type = adVarWChar
col.DefinedSize = 255
col.Properties("Default").Value = "some string value"
tbl.Columns.Append col

cat.Tables.Append tbl

[Robert Mooney]

See my September 8 posting in this thread.

>.
>

[Robert Mooney]

I've posted additional information. Any progress?

Robert Mooney
Manager of Information Systems
The Thomas Group

>.
>

[Steven Bras]

I've researched the issue further and find that the latest cases we have
with this error have been resolved by downloading and applying MDAC 2.6,
from http://www.microsoft.com/data.

An alternative is to execute an ALTER TABLE statement, which eliminates the
need for ADOX entirely. The syntax is:

ALTER TABLE MyTable ALTER COLUMN MyColumn SET DEFAULT 'DefaultValue'

This should be supported by the Jet 4.0 provider.

Either of these should resolve the problem for you. Good luck!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided AS IS with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

[Robert Mooney]

Trying to create/append columns to an Access 2000 database
generates Error 3421 if a default value is specified for
the column. What is wrong? Here are my steps to

[Steven Bras]

OK, I think I have it this time (I believe I may have responded to your
post on this subject a while back). The correct syntax to set the Access
default value is:

col.Properties("Default").Value = Chr(34) & "some string value" & Chr(34)

Access expects literals to be quoted; in the Access application, this also
causes an error. You must use quotes around your string literals.

Hope this helps!

[Robert Mooney]

The problem persists. I've included current code at the
end of this message.

The error that occurs is:

Run-time error '3421':
Application uses a value of the wrong type for the current
operation.

Checking the VarType(col.Properties("Default").Value)
returns value of 0. This may be a clue to the problem.

Bob Mooney

-----------------------
Private Sub Form_Load()

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim col As ADOX.Column
Dim tbl As ADOX.Table

Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString

= "h:\reports\track_dwg_files\bobtest.mdb"
cn.Open

Set cat = New ADOX.Catalog
cat.ActiveConnection = cn

Set tbl = New ADOX.Table
tbl.Name = "new_files"

Set col = New ADOX.Column ''''added "ADOX."

Set col.ParentCatalog = cat
col.Name = "stringField"
col.Type = adVarWChar
col.DefinedSize = 255

Debug.Print VarType(col.Properties
("Default").Value) ''''returns value of 0
col.Properties("Default").Value = Chr(34) & _
"some string value" & Chr(34) '''' added "Chr(34)"
tbl.Columns.Append col

cat.Tables.Append tbl

End Sub

>.
>

[Steven Bras]

I ran your code on my MDAC 2.7 (not yet released) machine, and it ran
without error.

I then ran it on my MDAC 2.5 sp2 machine, and the 3421 error occurred. I
applied the Jet service pack 5, and the error persisted. I then updated
MDAC to 2.6 sp1, and the error was eliminated.

You can download this from http://www.microsoft.com/data; I believe the key
is the updated Jet OLEDB Provider.

Good luck!

[Robert Mooney]

I have a problem with ADOX. I have applied suggestions
from this newsgroup but the problem persists. I've

Bob Mooney

cat.Tables.Append tbl

End Sub

>Steven Bras, MCSD
>Microsoft Developer Support/Visual Basic WebData
>This posting is provided AS IS with no warranties, and
confers no rights.
>You assume all risk for your use. © 2001 Microsoft
Corporation. All rights
>reserved.
>

>.
>
.

[Ramesh]

Hello Bob.
What version of MDAC and Jet do you have on this machine?
I have seen this problem with MDAC 2.5, but MDAC 2.6 and Jet 4 ServicePack
5 shoudl solve this problem.
If you cannot upgrade your MDAC there is another workaround which you could
use.
you can execute an ALTER TABLE command to
accomplish the same functionality. For example, to add a default value to
MyColumn in MyTable, use the following command:

ALTER TABLE MyTable ALTER COLUMN MyColumn SET DEFAULT 'DefaultValue'

This will set the default value for the column.

Hope this helps.

Ramesh Thyagarajan
Microsoft Support.