Differences in VB6.0 SP6 Refeernces for .mdb and .accdb databases with Office 2007

[brianmcnamee]

I use VB 6.0 SP6 to generate my Access Databases for Office 2007. When I use dbVersion30 I get a .mdb with all my Text Fields set to Zero Length = NO (and I can't save a record that doesn't fill all the Table's fields) and although I can change the settings to YES I can't SAVE the result. If I convert the .mdb to .accdb then I can change the setting and SAVE the result but I can't seem to find the right VB 6.0 SP6 References to use the database! Any suggestions? Alternatively, can anyone provide the VB 6.0 SP6 References to apply when using a .mdb or .accdb database generated with Office 2007?

[June7]

Unless it is different for mdb file, AllowZeroLength set to NO prevents empty string saved into field. Are you trying to save empty string into fields causing record commitment to fail? I NEVER allow empty string in text fields.

Are you saying you can't save the table design change? What code are you using for this design change?

[brianmcnamee]

I just didn't want to bother filling EVERY Field in the table. I am resorting to do that with putting a blank into the unused fields of table for a record which does not contain those fields. But that just makes the code look messy. There's clearly a difference between how the .mdb (dbVersion30) and .accdb (dbVersion40) databases behave with respect to the AllowZeroLength option. Apparently both are supported by Office 2007, but I can't find the right references in VB6.0 SP6 to OPEN the .accdb even though it looks good in Access!. Maybe, if somebody could tell me how the set the AllowZeroLength option to Yes in my TableDEf/CreateField Statements (which is not covered in my VB Professional Features book) I wouldn't have bother with this!

[June7]

Have to use TableDefs to modify AllowZeroLength. I have code that creates table with CREATE TABLE and adds fields with ALTER TABLE then sets AllowZeroLength and DefaultValue properties. Here is extract.

    'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
    Set db = DBEngine.OpenDatabase("folderpath\filename")
    'change text field AllowZeroLength default Yes to No
    db.TableDefs("tablename").Fields("fieldname").AllowZeroLength = False
    'change number field so 0 is not DefaultValue
    db.TableDefs("tablename").Fields("fieldname").DefaultValue = ""

[brianmcnamee]

June7, Exactly what I was looking for! Except of course, I want it to be True. If your source records do not always contain the same fields you have in your Table you wind up with a lot of NULLs which is not an error in my case, but the database engine will not let you store the NULL if AllowZeroLength = False is set. On the other hand, if your source data records are a match with your Table definitions not entering data in a fields could be an error. And the entries in my Tables are populated from many sources which report a large variety of data, so my Table is a join of all the source data sets. Thank you!