Access.dao.dbengine dependency issues in .net

Question

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 25-Feb-16 5:53am

GetReQ

Updated 26-Feb-16 3:09am

Add a Solution

2 solutions

Solution 2

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.

Posted 26-Feb-16 3:09am

GetReQ

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Accepted Answer · 2016-02-25T05:58:00

Solution 1

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.

Posted 25-Feb-16 5:58am

Dave Kreskowiak

Comments

GetReQ 26-Feb-16 9:10am

Oh, I had no idea DAO was now defunct - that makes a lot of sense, thanks.