DAO to ADO

[Rick Allison]

Here's the DAO code.

' Set the default value for Record Add Date Time!!
Set tdfTableDef = dbs.TableDefs!trelROLevelGroup
tdfTableDef.Fields!RecordAddDateTime.DefaultValue = "=Now()"

Does anyone know where I can find how to convert this to ADO?

I've ready a little about OpenSchema but an example sure would help.

Thanks

 

[Douglas J. Steele]

Given that the DAO code works for you, why change it? Assuming you're
strictly dealing with Jet databases (i.e.: an MDB file), DAO is the better
method.

If you're determined, though, take a look into ADOX. However, also check
http://support.microsoft.com/?id=291194 as there can be problems trying to
set the default value.

 

[Rick Allison]

Douglas,

I need ADO really because DAO does not allow the use of "On Delete Cascade".
I am adding to a database that is scattered across the country. I have used
DAO up until now but because of the limitation I have to switch. It also
means that I can no longer support Access 97. Not that it really matters
but it is a fact.

That's why I wanted to switch to ADO. I've got it working with both. ADO to
do the "On Delete Cascade" and DAO to do the default value.

Is that the best way to go?

Rick

 

[Douglas J. Steele]

??? Whether you use DAO or ADO is completely unrelated to how referential
integrity works! Cascade Deletes are definitely supported in Access 97: I
work almost exclusively in Access 97, and I'm capable of supporting them
without any problems.

 

[Tim Ferguson]

I need ADO really because DAO does not allow the use of "On Delete
Cascade".

Check the properties of the Relationship object and the Relationships
collection.

You could also look at the ALTER TABLE command in SQL and read about the
CONSTRAINT clause.

Hope that helps


Tim F

 

[Steve Schapel]

Doug,

Whereas setting Referential Integrity with Cascade Deletes enabled in
the Relationships Window is independent of libraries referenced, I
believe Rick is referring to running ALTER TABLE or CREATE TABLE
statements within VBA, and as such I believe this will not work with
DAO.

- Steve Schapel, Microsoft Access MVP

 

[Douglas J. Steele]

Thanks, Steve. I never use ALTER TABLE or CREATE TABLE, so you may be right.

 

[TC]

The OP is thinking as follows: "I don't know how to do it from DAO,
therefore, it can't be possible from DAO".

Oops!

TC

 

[Rick Allison]

Steve,

Thanks for clarifying. You are correct in what I want to do.

DAO does not support the "On Delete Cascade". I can write code to handle
the delete of data but why when Referential Integrity does that for you
automatically.

For me the solution is to write ADO. For now I have mixed ADO and DAO
because I still have not figured out how to manage the Default Value on an
existing column in a table via ADO (or ADOX, it that's the way).

Still looking.

Rick

 

[Douglas J. Steele]

DAO may not allow you to specify "On Delete Cascade" in an ALTER TABLE or
CREATE TABLE statement, but you can still establish referential integrity
using DAO.

You do it by using the CreateRelation method, and setting the Attributes
parameter (the 4th parameter of the method) to dbRelationDeleteCascade.

 

[Rick Allison]

Doug,

I cannot thank you enough for this. It would have taken me a long time to
find the CreateRelation method.

I'll be back to let you know how it goes.

Rick

 

[Rick Allison]

Doug et. al.

What's the "db"Parameter to create an Autonumber field in a table?

I switched to TableDef to create tables on the fly but I cannot find how to
create an autonumber field. Best I can find is to create an integer then a
unique index (primary key) but that's it.

Thanks,

Rick

 

[Douglas J. Steele]

You create the Field as dbLong, and then you set its Attributes to
dbAutoIncrField:

Set fld1 = tdf.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField

 

[Rick Allison]

Thanks again. This really helps. I am learning a lot!!