Click here to Skip to main content
15,123,118 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Code Project,

I am currently working on a class library that requires me to output data into an Access file (*.mdb). I have a specified schema given to me for this *.mdb file.

I need to specify the AllowZeroLength property in my newly created database file and I do this using the .NET object Access.Dao.DBEngine. This is my code that uses this:
VB
Private Sub SetZeroLengthFields(fullPath As String)
        Dim dbe As New Microsoft.Office.Interop.Access.Dao.DBEngine
        Dim db As Microsoft.Office.Interop.Access.Dao.Database = _
               dbe.OpenDatabase(fullPath)

        db.TableDefs("SECTIONS").Fields("LINK_ID").AllowZeroLength = True
        db.TableDefs("SECTIONS").Fields("LINK_NAME").AllowZeroLength = True
        db.TableDefs("SECTIONS").Fields("SECT_ID").AllowZeroLength = False
...

The use of this object has a required COM reference in my class library that makes use of the assembly Microsoft.Office.Interop.Access.Dao.

Now when I build the class library and reference it in the main project everything runs smoothly and the access file is created as needed for the specified schema and the data is populated.

The issue occurs when I deploy (through use of a setup project in VS2010) this program onto another computer running a different version of MSOffice to the version I'm developing on (specifically: I'm using MS Office 2010 and the deployed version is MS Office 2013).

When creating the object dbe in my code I get a runtime error:
System.Runtime.InteropServices.COMException(0x80040154): Retrieving the COM class factory for component with CLSID{...} failed due to the following error: Class not registered 

I've noticed that under C:\Windows\assembly I'm using Microsoft.Office.Interop.Access.Dao 14.0.0 whereas the deployed machine is using 15.0.0.

Is there a way I can get around this dependency issue? What is a good way for dealing with such a situation when versions of Office differ?

This is the only place in my code that I make use of the object Access.Dao.DBEngine so I'm thinking for now I'll try and find a way to specify the AllowZeroLength through an sql script instead (if possible) to prevent use of this object.

What I have tried:

I've tried to register my .dll through regsvr32 before I identified the issue was with the Access.Dao.DBEngine object.
Posted
Updated 26-Feb-16 4:09am

Why are you using DAO?? It's been dead for quite a long time now.

The last version of Office to expose it's functionality, oddly enough, was 2010. DAO support doesn't exist in Office 2013. That's why it fails.

You should have been using OleDb and the ACE engine for an Access database.
   
Comments
GetReQ 26-Feb-16 9:10am
   
Oh, I had no idea DAO was now defunct - that makes a lot of sense, thanks.
I've had a look around at trying to define Allow Zero Length through an OleDbCommand but it doesn't seem to be an accessible property (I'll come to this in a second).

Firstly I tried using ADOX.Catalog as a quick solution:
VB
Dim connstring = String.Format("{0} Data Source={1};", provider, fullPath)
Dim cat As New ADOX.Catalog
cat.ActiveConnection = connstring

cat.Tables("SECTIONS").Columns("LINK_ID").Properties( _
   "Jet OLEDB:Allow Zero Length").Value = True
cat.Tables("SECTIONS").Columns("LINK_NAME").Properties( _
   "Jet OLEDB:Allow Zero Length").Value = True
cat.Tables("SECTIONS").Columns("SECT_ID").Properties( _
   "Jet OLEDB:Allow Zero Length").Value = False
...

However there appears to be a known issue when setting ADOX.Catalog.ActiveConnection. When I did this I was thrown an error on this line.

I thus tried to script the schema definition I was given with DBWScript. As the default is Allow Zero Length I used the script in an OleDbCommand to alter columns I know needed changing as such:
VB
Dim cmd As New OleDbCommand
Dim query = _
"ALTER TABLE [SECTIONS] DENY ZERO LENGTH [SECT_ID]; " & _
"ALTER TABLE [SECTIONS] DENY ZERO LENGTH [SECT_NAME]; " & _
...

However this gave me a syntax error when I ran it on the OleDbConnection.

According to this post the Allow/Deny Zero Length property is not accessible through Jet sql.

At this point I've given up. I can't replicate the schema exactly through .NET without causing COM issues. It'll throw up some warnings in the software I'm handing the data over to but it's got to the point where I don't think I can make any progress on it.

Thanks again for the info.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900