This project has moved. For the latest updates, please go here.
1
Vote

Any one reverse engineer an existing Access DB

description

I started down the path of using EF Tools and how to reverse engineer an existing database for code first approach. Unfortunately, I could not figure out how to setup a usable access provider. Using the OLE provider did not work when I tried to reverse engineer. I got this error:

System.Data.ProviderIncompatibleException: The store provider factory type 'System.Data.OleDb.OleDbFactory' does not implement the IServiceProvider interface. Use a store provider that implements this interface.
at System.Data.Entity.Design.Common.MetadataUtil.GetProviderServices(DbProviderFactory factory)
at System.Data.Entity.Design.EntityStoreSchemaGenerator.CreateStoreSchemaConnection(String providerInvariantName, String connectionString, Version targetEntityFrameworkVersion)
at System.Data.Entity.Design.SsdlGenerator.EntityStoreSchemaGeneratorDatabaseSchemaLoader.CreateStoreSchemaConnection(String providerInvariantName, String connectionString, Version& storeSchemaModelVersion)
at System.Data.Entity.Design.EntityStoreSchemaGenerator..ctor(String providerInvariantName, String connectionString, String namespaceName)
at Microsoft.DbContextPackage.Handlers.ReverseEngineerCodeFirstHandler.ReverseEngineerCodeFirst(Project project)

BTW - To support migrations I'll be using this approach

I'm not married to this approach of re-engineering. Any ideas other than typing in all the mappings by hand?

comments

bubibubi wrote Jan 26 at 6:24 AM

Actually DB First approach does not work because the EF generates too complex queries for Microsoft Access.

About accessing an access database using the server Window the Jet provider works fine. The provider offers a proper connection to a Microsoft Access database and implements the IServiceProvider interface.

I don't know if this is enough to generate the migrations starting from an Access Database but is a starting point.
To do this you have to start from the Jet entity framework provider source code and install the DDEX provider (there are some hints here https://jetentityframeworkprovider.codeplex.com/wikipage?title=Enabling%20DB%20First&referringTitle=Documentation ).

Another approach could be that you upgrade the database to SQL Server (there are some tools around, I remember the upsize tool from Microsoft). Then you can use this tool.

Hope it helps,
Umberto

raytrask wrote Jan 26 at 2:40 PM

Great idea on the upsize approach. I figure I can upsize, then use the mapping code for the Access Database!

Thanks,
Ray

RickyTad wrote Mar 14 at 1:59 PM

But generating the code-first model from an existing MsAccess *.mdb database should actually work, is that correct ?
When trying to execute the "Reverse engineer Code First" Option from an existing database by using the EF Power Tools VisualStudio extension, the Jet provider does not appear in the Data Provider picklist (when selecting "Microsoft Access database file" as data source).
Has anyone a step by step description how to get it working ?

bubibubi wrote Mar 14 at 3:26 PM

Hi Ricky,
actually it does not work.

EF generates too complex queries for Microsoft Access. I mean, EF, calls the Jet EF Provider and at the end EF provider generates the query that EF is asking for but then, running it on Access, it does not work.

raytrask wrote Mar 14 at 4:09 PM

Hey Ricky,

I used upsize then Reverse Engineer approach recommended by Umberto.

0) Get EF Tools extension installed (you may need to use this trick from Stack Overflow).

1) Upsize MSAccess to a temp SQLServer database.
2) Run the Reverse Engineer Code First option on the temp SQLServer db (using the SQLServer connection).
3) Change the connection from SQL Server to your original MSAccess DB as specified in Jet EF setup docs.

Its worked great so far.

For the short term, I turned off migration support due to conflicts with hand built legacy DDL migration code. I'm hoping to move the approach mentioned in the first post eventually.
    {
        public Context() : base("show")
        {
            //Disable initializer, of the database so it can be managed by Delphi DDL calls.

            Database.SetInitializer<Context>(null);
        }
        public DbSet<ShowClass> ClassXRefs {get; set;}

    }

RickyTad wrote Mar 20 at 1:18 PM

Thank you for the support. I have now the some c# entity classes corresponding to an existent Ms Access database.
Is there anywhere available some Entity Framework code-first c# sample code (or complete project), about how to perform read/write operations on a MS Access database, using the JetEntityFrameworkProvider ?

RickyTad wrote Mar 20 at 1:20 PM

I forgot to mention that the MsAccess database has the *.mdb file format.

bubibubi wrote Mar 20 at 2:04 PM

You can find tons of code around. The code is the same for every other provider.
About settings connection string you can have a look to tests.
You can also have a look to this video https://www.youtube.com/watch?v=mI0un8jjqL8