Archived Support Site

This support site is archived. You can view the old support discussions but you cannot post new discussions.

Home → Support →

OLEDB Issues

Delboy's Avatar

Delboy

Apr 06, 2015 @ 01:43 AM

Hello, I have been stuck on this problem for over 4 days now and would really appreciate help. I am trying to work with a microsoft access database which is in the .accdb format. I created this in MS Access 2013 and cannot go down to mdb because my application requires the new attachment field type in .accdb.
There are two main issues,
1. I now must use "PROVIDER=Microsoft.ACE.OLEDB.12.0" rather than "PROVIDER=Microsoft.JET.OLEDB.4.0" to query the database. I had to install Microsoft Access Database Engine 2010 Redistributable before it worked on my computer (which is windows 8.1 64-bit). Initially i got the error, The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine, so i uninstalled AccessDatabaseEngine_x64.exe and installed AccessDatabaseEngine.exe (32-bit) and it worked on my localhost. However when deploying on apphb I get the error , The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine, so I don't know how to resolves this issue. I don't really know much about dll's but i was hoping one solution would be to install Microsoft Access Database Engine 2010 Redistributable on the apphb server. I thought apphb would already have this sort of stuff because it is likely developers using .NET would use these resources.

2. I am also using Microsoft.Office.Interop.Access.Dao to handle the images i have stored in the attachment fields of my database and need help to get this running on apphb as well.

Thanks in advance

  1. Support Staff 1 Posted by rune on Apr 06, 2015 @ 09:13 PM

    rune's Avatar

    Hi,

    You're right, this provider has not been installed on the application servers running your application. It's fairly rare that developers use MS Access databases in AppHarbor's environment -- mostly because it relies on the local file system, which is ephemeral and wiped on each deployment. This means that any changes you make to your database file will actually be deleted when you or AppHarbor redeploy your application.

    I'd be curious to hear about your use case for the use of MS Access databases. In particular, do you only need to read from the database file? I.e. does it contain all the data your application needs?

    In most cases I think you'll be better off storing data in a SQL Server database as that allows for better scalability and durability. That being said, it's likely possible to include the required binary/DLLs in your repository. You can then reference the file in your repository instead, and bin-deploy it by setting the Copy to Output Directory property to Copy Always.

    Best,
    Rune

  2. 2 Posted by Delboy on Apr 07, 2015 @ 01:03 AM

    Delboy's Avatar

    Yes it contains all the data my application needs to function.

    I would like to make a request for this driver be installed, it's only about 20MB in size.

    Also, at least for the time being, I will stick with MS Access because I'm unfamiliar with the other options and need to complete this project urgently.

  3. 3 Posted by Delboy on Apr 07, 2015 @ 02:21 AM

    Delboy's Avatar

    Also, not to be rude, but you didn't really give me any specific help on the two main points I stated out.
    I copied the ACEOLEDB.dll into my bin folder and set it to copy always but I still get the error, The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
    I don't know if it is a 32-bit/64-bit problem and even if it was, apphb has not really made it clear the specifications of the server computers and where these configuration settings (such as 64-bit or 32-bit active solution configuration) can be found. Because on my pc in visual studio I set this to x64, however when I check the build in apphb it always says any cpu. So if it is a 32-bit/64-bit issue can you run me through some steps to take in order to resolve this problem.

  4. Support Staff 4 Posted by rune on Apr 07, 2015 @ 07:00 PM

    rune's Avatar

    Hi,

    AppHarbor will build your application based on the build configuration that matches the environment name. The environment name is "Release" by default, but can be configured on the application's "Settings" page.

    If you need to use binaries with a specific bitness I'd recommend that you create a solution configuration for AppHarbor's environment. Appharbor's servers run 64-bit Windows Server 2012 R2, but your application will run in 32-bit mode. It can be a bit tricky to get assemblies requiring a specific bitness to work correctly in both environments, but this will also allow you to test the application locally (or configure IIS with this configuration while debugging). You will likely need to configure the "Release" solution configuration to build projects that require these binaries to use 32-bit dlls and be built for 32-bit architecture.

    Using Access databases in this way is not currently supported, but I'd be happy to help take a look at your solution. Is it ok if I inspect your code and solution file configuration?

    Best,
    Rune

  5. 5 Posted by Delboy on Apr 07, 2015 @ 10:48 PM

    Delboy's Avatar

    Yes please inspect the code and solution file configuration, hopefully this will help you better understand my problem

  6. Support Staff 6 Posted by rune on Apr 08, 2015 @ 01:19 PM

    rune's Avatar

    Hi,

    I took a look and I can see that you haven't referenced the binary anywhere in your project file. This means that the file is not actually copied to the output directory. You can see this by inspecting the build output, which can be downloaded on the log page for each build.

    You might have to fiddle around with this a bit since it's not supported and it might not even be possible, but people are often able to solve this type of problem by bin-deploying the dependencies.

    For starters though I'd recommend taking the following steps:

    1. Move the third-party dependencies to a different folder than bin - it can cause quite a bit of trouble/confusion. You can for instance move the dependencies to a folder called lib in the root of your repository, and reference the files in that location.
    2. Reference the file ACEOLEDB.dll file as described above (copy to output directory).
    3. Verify that the file is actually copied to the output after the application is built by AppHarbor.

    Best,
    Rune

  7. 7 Posted by Delboy on May 03, 2015 @ 01:46 AM

    Delboy's Avatar

    Ok i am now using the MS SQL Server add-on provided on apphb and am still having some issues.
    When I try to connect to the server in visual studio and add my database (C1 Learning System.accdb), I keep getting the error,
    "create database permission denied in database 'master'"

    Is this apphb restricting me and if so how do I use my current db or import it into the sql server. I need OLEDB providers for some of my code to work because the database file is in .accdb format (ms access 2007 onwards)

    Please also tell me if I have to use this new .mdf format and how to convert my current db into that.

  8. Support Staff 8 Posted by rune on May 04, 2015 @ 06:29 PM

    rune's Avatar

    Hi,

    Sounds great! You can't create the database yourself, but it shouldn't be necessary either - the database has already been created on the server. You should be able to use it with the connection string parameters that are injected by AppHarbor, or fetch it from the appSettings configuration variables that are displayed on the applications "Configuration Variables" page.

    You can also find the database name and connection string by accessing the Sequel Server add-on's administration page (click the add-on logo on your application page and then follow the "Go to SQL Server" link on the add-on plan page).

    Best,
    Rune

  9. 9 Posted by Delboy on May 05, 2015 @ 04:05 AM

    Delboy's Avatar

    ok so using SQL Server 2014 Management Studio I was able to import the data from my accdb database into the database on the server.
    however, after deploying the website and going on a feature where the connection to the database is opened, I still get the error:
    [InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.]

    this problem would be resolved so easily if you just installed the driver, https://www.microsoft.com/en-us/download/details.aspx?id=13255

  10. Support Staff 10 Posted by rune on May 05, 2015 @ 10:39 PM

    rune's Avatar

    Hi,

    This sounds more like a configuration issue - I don't see any reason why the database connection would need Access drivers when you're using SQL Server.

    We try to keep the runtime stack on AppHarbor as light as possible to avoid potential compatibility issues between different driver and stacks. It's unlikely that the Access driver will be installed on the servers as this database type is not typically used, or recommended, for cloud environment such as AppHarbor's.

    Best,
    Rune

  11. 11 Posted by Delboy on May 06, 2015 @ 06:52 AM

    Delboy's Avatar

    ok then could you check my MS SQL Server, is it a 32/64 bit issue?
    also in my connection string i have SQLOLEDB as the provider so shouldn't this have Microsoft.ACE.OLEDB.12.0 within it?

  12. Support Staff 12 Posted by rune on May 06, 2015 @ 09:32 PM

    rune's Avatar

    Hi,

    If the database has been converted to a MS SQL Server database there shouldn't be any need for Access-related drivers. The 32/64 bit issue was related to the Access database - so if this is still an issue I think it because the application still includes references to or tries to use the Access database. The clients and providers required for using MS SQL databases are installed on AppHarbor's servers and shouldn't produce this type of issue.

    Best,
    Rune

  13. 13 Posted by Delboy on May 06, 2015 @ 11:22 PM

    Delboy's Avatar

    yes rune so sorry for that. there was indeed parts of the code still referencing the access database so I have replaced the connection string with the SQLOLEDB provider string.
    I am pleased that it is now working but just as I have fixed this bug another arises.
    I have an attachment field in my accdb which I presume was included when I imported that data into the MS SQL server using MS SQL Management Studio. I do not know how this attachment field is handled but the problem now is that this SQL Query:

    SELECT Count(*) AS QuestionNo FROM [Question Bank] WHERE (QxnImg.filename <> 'Title.png') AND (QxnImg.filename <> 'head.png')

    produces this error:

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "QxnImg.filename" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "QxnImg.filename" could not be bound.

  14. Support Staff 14 Posted by rune on May 07, 2015 @ 05:52 PM

    rune's Avatar

    Hi,

    Glad you were able to fix the Access driver issue. I'm not sure you can use attachment in the same way with SQL Server - have you been able to get this up and running locally with a SQL Server instance?

    Generally I'd recommend storing files and images in a file storage service rather than the database. AWS S3 is a very reliable, highly-available and fast service for storing files that can significantly decrease the complexity related to serving such files. You can also store binary data in SQL Server databases, but it'd require converting them from the current attachment value.

    Best,
    Rune

  15. rune closed this discussion on May 03, 2018 @ 11:28 PM.

Discussions are closed to public comments.
If you need help with AppHarbor please start a new discussion.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac