default constraint

[Patty O'Dors]

is there any SQL that can be fired off against Access that will cause it to
apply a default to a particular column?

using

alter table mytable add constraint myconstraint default (0) for mycolumn

doesn't seem to work, either over ADO or within Access.

Any ideas?

 

[Brendan Reynolds]

I'm not sure whether this can be done with SQL, but it can certainly be done
with either DAO or ADOX ...

Public Sub SetDefault()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
Set fld = tdf.Fields("TestLong")
fld.Properties("DefaultValue") = 0

End Sub

Public Sub SetDefault2()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("tblTest")
Set col = tbl.Columns("TestLong")
col.Properties("Default") = 1

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Patty O'Dors]

mmmmm.... ok thanks

Can ADOX be used with SQL server aswell?
Can it also be used to retrieve the text for the default?

Basically I'll expand on what I'm trying to do...
recreate a table in destination database, DTS the data into it - I'm OK with
all that.
But then I also want to recreate the indexes and constraints on the
destination table as they are on the source table - obviously, some of the
tables are being copied from SQL server to Access as you may have guessed.

What would be a good generic solution?

It's a bit annoying as Access can add a check constraint using DDL SQL -
but it can't add a default one! uh?

Cheers

 

[Patty O'Dors]

Hi
I tried using ADOX, but it doesn't seem to be very good at reading properties!
Whenever I have an ADOX.Property object, it's got a name, but retrieving its
value gives an error.
Here's the sample code I've got so far:

Dim cat_s As New ADOX.Catalog, cat_a As New ADOX.Catalog

cat_s.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);Integrated
Security=SSPI"
cat_a.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
source=C:\Documents and Settings\Me\My Documents\db1.mdb"

Dim t As ADOX.Table
For Each t In cat_s.Tables

If t.Name = "testtable" Then
Dim c As ADOX.Column
For Each c In t.Columns
Dim p As ADOX.Property
Debug.Print c.Name & vbCrLf & String(Len(c.Name), "=")

For Each p In c.Properties
On Error GoTo Hell
Debug.Print vbCrLf & p.Name;
Debug.Print " : " & p.Value
NextProperty:
Next

Next
End If
Next

Exit Sub
Hell:
Err.Clear
Resume NextProperty


The error handler needless to say always fires.

Any idea?

 

[Brendan Reynolds]

For working with the structure of a Jet database, I much prefer DAO over
ADOX. I'll have to leave it to someone with more SQL Server experience to
advise on that part of the problem.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Douglas J. Steele]

It would probably help if you actually looked at the error, so that we could
know what the problem is...

Does the error handler fire for every property, or just some of them?

 

[Brendan Reynolds]

I can reproduce that. The code will run without error if I print just the
Name of the property, but fails on any attempt to print the Value.

I can understand why you would prefer to use one technology for both data
sources, but I suspect this may not be possible. I suspect you may need to
use one technology (SQLDMO, perhaps?) to work with the SQL Server tables and
another (I recommend DAO) to work with the Jet tables.

Here's my test code, fails with error 3251 if the commented line is
uncommented. I used 'On Error Resume Next' to resume testing the next
property after an error, confirming that the error occurs with all
properties.

Public Sub ListSqlProps()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim prp As ADOX.Property

On Error GoTo ErrorHandler
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pubs;Data Source=(local)"
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn
Set tbl = cat.Tables("authors")
Set col = tbl.Columns(0)
For Each prp In col.Properties
Debug.Print prp.Name
'Debug.Print CStr(prp.Value)
Next prp

ExitProcedure:
On Error Resume Next
cnn.Close
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Next

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

It is not within my power to fix ADOX.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Patty O'Dors]

It seems like the exact same VB code works for Jet, so it must be something
to do with the fact that the SQLOLEDB provider cannot provide that
information, but the Jet one can.

 

[Brendan Reynolds]

That's true. So it was imprecise of me to imply that the problem was solely
with ADOX. But the end result is the same - the combination of ADOX and the
SQL OLEDB provider just do not seem to provide the functionality that you
are looking for. This does not come as a big surprise to me, as I believe
(though this is speculation on my part) that the main purpose of ADOX was to
encourage developers working with Jet databases to use ADO rather than DAO.
SQL Server developers were not, in my opinion, the main target of ADOX.
Unless someone else has another suggestion, then as far as I can see the
only workaround is to not use ADOX. SQL DMO may be an alternative, but as I
indicated earlier, I do not have as much experience with SQL Server as with
Jet, and it would be better if someone with more SQL Server experience could
advise you on that issue.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Patty O'Dors]

That's true. So it was imprecise of me to imply that the problem was solely

with ADOX. But the end result is the same - the combination of ADOX and the
SQL OLEDB provider just do not seem to provide the functionality that you
are looking for.

It may be the case that using ODBC instead of OLEDB will enable it to gain
the information about the column properties. I'll try it.

This does not come as a big surprise to me, as I believe
(though this is speculation on my part) that the main purpose of ADOX was to
encourage developers working with Jet databases to use ADO rather than DAO.
SQL Server developers were not, in my opinion, the main target of ADOX.
Unless someone else has another suggestion, then as far as I can see the
only workaround is to not use ADOX. SQL DMO may be an alternative

SQL DMO would be great - but it doesn't work with Jet at all.

 

[Wart]

Patty,
Try this code with a reference to Microsoft ActiveX Data Objects 2.X Library
in your project. It gives the value for the field.
Dim cnn As New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
SecurityInfo=False;Initial Catalog=pubs;Data Source=(local)"
Dim lrsSchema As New ADODB.Recordset
Dim lField As ADODB.Field
Dim lProp As ADODB.Property
Set lrsSchema = cnn.OpenSchema(adSchemaColumns)
For Each lField In lrsSchema.Fields
For Each lProp In lField.Properties
Debug.Print lProp.Name
Debug.Print lProp.Value

Next
Next

cnn.Close


HTH,
CF

 

[Brendan Reynolds]

You don't want the properties of *those* fields, Patty, you want the values.
For example, the following will list the default values of each field in the
'authors' table in the 'pubs' database.

Public Sub ListSqlProps()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

On Error GoTo ErrorHandler
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pubs;Data Source=(local)"
Set rst = cnn.OpenSchema(adSchemaColumns)
rst.Filter = "TABLE_NAME = 'authors'"
Do Until rst.EOF
Debug.Print rst.Fields("COLUMN_NAME")
Debug.Print rst.Fields("COLUMN_DEFAULT")
rst.MoveNext
Loop

ExitProcedure:
On Error Resume Next
rst.Close
cnn.Close
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Next

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Patty O'Dors]

Can the properties be changed through this method, or is it read-only?

 

[Brendan Reynolds]

The recordset appears to be read only. I don't know if there is any way to
change that. I suspect the answer is probably no, but if anyone else knows
differently, I hope they will not hesitate to say so.

As I've said before, while I can understand why you would like to use one
technology both to read the properties of the SQL Server tables and set the
properties of the Jet tables, I suspect that may not be possible. You may
have to use one technology, such as SQL DMO or ADO OpenSchema to read the
properties of the SQL Server tables, and a different technology (I recommend
DAO) to set the properties of the Jet tables.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.