Using ADO in Excel/Access 2007

[cstimart]

I am would like to use the following code from examples I was point to. However, the "Jet OLEDB:Allow Zero Length" crashes as Jet OLEDB isn't used in 2007. Is there a comparable property in ACE?

Set .ParentCatalog = cat
    .Properties("Nullable") = False 'Required.
    .Properties("Jet OLEDB:Allow Zero Length") = False
End With

 

[Norie]

Is that the whole code?

What are you trying to set this property for?

 

[cstimart]

My code, create and Access 2007 database and creates the Fields. This section is setting the "Required" property of the field to "No".

Here's the section(s) that apply:

    Dim tbl As ADOX.Table

    Set tbl = New ADOX.Table
    tbl.Name = "tblDate_Data"
    
    With tbl.Columns
        .Append "Date1", adDate
                With ![Date1]
                    Set .ParentCatalog = cat
                    .Properties(3) = True   'Nullable
                End With
    End With
    cat.Tables.Append tbl

 

[Norie]

Why do you use Properties(3) in that code but Properties("Nullable") in the first code?

 

[cstimart]

Why do you use Properties(3) in that code but Properties("Nullable") in the first code?

Another person and I were trying to figure it out, and we had tried both the name and the ordinal.

 

[Norie]

Why don't you just create the table using some other method?

I don't think I've ever seen ADOX being used to create a table, all its fields and all the properties of the fields.

 

[cstimart]

It's been a couple day process that I have almost finished...

I started with this, which has code to create the database/table....

Sometimes you can see it anyway. The alternative, if you're feeling brave, is SQL.

By using ADO to connect to Access you can retrieve the data efficiently. That includes using cell contents as parameters in the queries, and it's my preferred way to go because you get more control.

See how you go with this tutorial.

Denis

Then, when the fields were "required", my code crashed when empty cells appear. Then I was pointed to the following to change the property.

Allen Browne gives many examples about using ADOX here:
http://allenbrowne.com/func-adox.html

There is an example of a required field in his function CreateTableAdox()
.

Now, my process is working almost perfect. The only problem is that the access db stays locked/connected when it's complete. I have narrowed the issue down to this section because if I remove the with ![Date1] ....end with portion, the db doesn't stay locked (but then the code crashes when there's an empty field.

Currently, when finished, I have to exit excel and open/close the access file to unlock it.

 

[Norie]

I'm sorry but I can't really help.

It seems to be quite a convoluted way to create tables actually but if it's what you need it's what you need.

 

[xenou]

I guess one option is to create an Access 2003 database...I'm still pretty much using mdb's about 95% of the time (well, actually, closer to 100% of the time).

For what it's worth, I ran Allen Browne's CreateTableAdox() function (unchanged) with no problems in an Access 2010 database (accdb file format).

Do you know why you get a crash just because a record has a Null value instead of a date somewhere?

 

[cstimart]

I guess one option is to create an Access 2003 database...I'm still pretty much using mdb's about 95% of the time (well, actually, closer to 100% of the time).

For what it's worth, I ran Allen Browne's CreateTableAdox() function (unchanged) with no problems in an Access 2010 database (accdb file format).

Do you know why you get a crash just because a record has a Null value instead of a date somewhere?

OK...I converted my code to create/utilize an .mdb file. However, I am still having the issue with the file remaining locked when the code is completed.

Sub CreateDB_and_Table()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim fld As ADODB.Field
    Dim sDB_Path As String

    With Application
        .EnableCancelKey = xlDisabled
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    'Delete DB is already exists
    On Error Resume Next
        Kill sDB_Path
    On Error GoTo 0

    'Create new DB
    Set cat = New ADOX.Catalog
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDB_Path & ";"
    
    'Create Table
    Set tbl = New ADOX.Table
    tbl.Name = "tblCUWR_Data"
    
    'Append the Columns
    With tbl.Columns
        .Append "Date1", adDate
                With ![Date1]
                    Set .ParentCatalog = cat
                    .Properties("Nullable") = True
                    .Properties("Jet OLEDB:Allow Zero Length") = False
                End With
    End With
    cat.Tables.Append tbl
    
    'Close the Connection
    Set tbl = Nothing
    Set cat = Nothing

End Sub

If I remove the blue portion, the mdb file does not stay locked, but then the "required" is still set to yes (which I don't want).