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

 

[xenou]

This is certainly a mystery. I'm getting the same results. Some ideas:

1) if you close Excel and re-open it you seem to release the lock on the database (not pretty but a workaround)

2) Use DAO instead of ADO (I've had good results with DAO before - but that's basically starting over, unfortunately).

3) Create a template DB and then you only need to copy the template DB file rather than building new a database from scratch.

4) Or continue researching this error - I found a number of sites where others report similar problems (no clear answer either - some folks give precisely these steps and report success, other report that it's not working for them).

5) Or use a default value so that the required field works (i.e., you won't need to change the field property).

I'm very curious as to how you got to creating databases in scratch from your original post (in the original thread) where you only needed to update a parameter value. This seems like an awfully long sidetrack and I don't see how this solves any problems for you (even if it did work). But of course it's up to you if you don't want to explain what you're up to - it would only be interesting to here what the plan is here (admittedly, I have my doubts - creating db's in code is generally more trouble than its worth and I would only do it as a necessity).

 

[cstimart]

I suppose I could have a standard access template available for the rest of the code I am using...I'd just have to find the way to delete all the records upon completion of the code, rather than killing the file completely.

 

[Norie]

It's pretty straightforward to delete all the data from a database.

There are various ways you can do it.

For example one way would be to simply create a blank database and import everything to it apart from the data.

That can be done via Get External data... where you can set to import only table definitions and can specify to keep relationships.

Of course that's done manual but I'm sure there are other methods that will do something similar with code.

 

[xenou]

I was trying at this again today and this seems to work:

At the end of the create table procedure, instead of just:

[COLOR="Navy"]Set[/COLOR] cat = [COLOR="Navy"]Nothing[/COLOR]

Try:

[COLOR="Navy"]Set[/COLOR] cat.ActiveConnection = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] cat = [COLOR="Navy"]Nothing[/COLOR]

 

[cstimart]

AWESOME!!! Thanks for the help!