Results 1 to 17 of 17

Thread: Microsoft.ACE.OLEDB problem

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Microsoft.ACE.OLEDB problem

    I am using database connection string:
    Code:
    conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & BasePath & "" & ";Persist Security Info=False;"
    The code works on Win7. When I run the program on Win10, I get an error message # 3706.
    ACEOLEDB.DLL is located in the folder: C: \ Program Files \ Common Files \ Microsoft Shared \ OFFICE15 \.
    But this file is not in the folder: C: \ Program Files (x86) \ Common Files \ Microsoft Shared \ OFFICE15 \
    Changing the connection string to:
    Code:
    conn = "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" & BasePath & "" & ";Persist Security Info=False;"
    causes the same error.
    To check the version of the library, I used the code:
    Code:
    Option Explicit
    Private Type GUIDs
        Data1 As Long
        Data2 As Integer
        Data3 As Integer
        Data4(0 To 7) As Byte
    End Type
    
    Private Declare Function CLSIDFromProgID Lib "ole32" (ByVal lpszProgID As Long, rclsid As GUIDs) As Long
    
    Public Function IsOLEObjectInstalled(Name As String) As Boolean
    Dim mGuid As GUIDs
    On Error Resume Next
    IsOLEObjectInstalled = CLSIDFromProgID(StrPtr(Name), mGuid) = 0
    End Function
    
    Private Sub Form_Load()
    MsgBox "Microsoft.ACE.OLEDB.15.0 - " & IsOLEObjectInstalled("Microsoft.ACE.OLEDB.15.0")
    MsgBox "Microsoft.ACE.OLEDB.12.0 - " & IsOLEObjectInstalled("Microsoft.ACE.OLEDB.12.0")
        End
    End Sub
    On Win7, the code shows:
    "Microsoft.ACE.OLEDB.15.0 - False"
    "Microsoft.ACE.OLEDB.12.0 - True"
    On Win10, the code shows:
    "Microsoft.ACE.OLEDB.15.0 - True"
    "Microsoft.ACE.OLEDB.12.0 - True"
    What am I doing wrong and how do I make the program work on Win10?

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Microsoft.ACE.OLEDB problem

    You don't mention how you deployed ACE to the machines. However it sounds like you are using 64-bit Windows 10 and failed to deploy 32-bit ACE to support VB6 programs.

    The ole32 call is probably running afoul of WOW64 virtualization and misreporting (e.g. you are misinterpreting) what the registry actually contains.

    Best bet: run away from ACE. It is basically a novelty that buys you little of substance. Much of its new stuff is quirky and only accessible in part via DAO and the rest via MS Access's private hooks into the ACE Engine.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by dilettante View Post
    You don't mention how you deployed ACE to the machines. However it sounds like you are using 64-bit Windows 10 and failed to deploy 32-bit ACE to support VB6 programs.
    Yes. System Win10X64. I don't know who installed Office.
    The program works with ADODB.Recordset. The only problem is getting it to work on different computers.
    Looked at the ACEOLEDB.DL file with a viewer. Apparently it cannot be used like Activx with a manifest.

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Microsoft.ACE.OLEDB problem

    You can implement IsOLEObjectInstalled using built-in CreateObject function like this

    Code:
    Option Explicit
    
    Public Function IsOLEObjectInstalled(Name As String) As Boolean
        On Error Resume Next
        IsOLEObjectInstalled = Not CreateObject(Name) Is Nothing
    End Function
    
    Private Sub Form_Load()
        Debug.Print IsOLEObjectInstalled("Microsoft.ACE.OLEDB.15.0")
        Debug.Print IsOLEObjectInstalled("Microsoft.ACE.OLEDB.12.0")
    End Sub
    CreateObject will fail on stale registry entries too like in your case above.

    You cannot possible make your program work ok when the client's machine does not have ACE installed at all.

    It appears ACE was unsuccessfully uninstalled so the files were removed but the registry entries are still present.

    cheers,
    </wqw>

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by wqweto View Post
    You cannot possible make your program work ok when the client's machine does not have ACE installed at all.

    It appears ACE was unsuccessfully uininstalled so the files were removed but the registry entries are still present.
    Thanks. The program now reports "False" in both cases.
    Most likely, you are right, there have been some changes in the system and there are no X32 libraries in it.

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by Argus19 View Post
    Thanks. The program now reports "False" in both cases.
    So, if your goal is to use a SingleFile-Desktop-DB "without deployment-hassle" with your App, you have two choices:
    1) use the JET4-OleDB-Driver (which comes preinstalled and requires no deployment on your end)
    2) use SQLite (which allows deployment of the necessary DBEngine-Files in a regfree manner)

    Olaf

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Re: Microsoft.ACE.OLEDB problem

    Schmidt, The database is created from the sample code from the "planet-source-code" site.
    The program has already been written and I really do not want to completely rewrite it.

  8. #8
    Fanatic Member Episcopal's Avatar
    Join Date
    Mar 2019
    Location
    Brazil
    Posts
    547

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by Schmidt View Post
    use the JET4-OleDB-Driver (which comes preinstalled and requires no deployment on your end)

    Olaf
    I've never worked with it.... and taking advantage of the question, how is the speed?

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Microsoft.ACE.OLEDB problem

    There is no "JET4-OleDB-Driver" though there is a Jet 40. OLEDB Provider and even an old fashioned ODBC Driver.

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by dilettante View Post
    There is no "JET4-OleDB-Driver" though there is a Jet 40. OLEDB Provider...
    Nope, MS never produced a Jet-DBEngine in version 40: https://en.wikipedia.org/wiki/Micros...atabase_Engine

    The OleDB-Connectionstring for the one I mean, is: "Provider=Microsoft.Jet.OLEDB.4.0;..."

    Olaf

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by Episcopal View Post
    I've never worked with it.... and taking advantage of the question, how is the speed?
    Certainly (much) slower than SQLite, but decent enough for normal purposes.

    Olaf

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by Argus19 View Post
    Schmidt, The database is created from the sample code from the "planet-source-code" site.
    The program has already been written and I really do not want to completely rewrite it.
    Do you have a (PSC-Github)-Link or a Zip-File for that original database-project?

    Olaf

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by Schmidt View Post
    Do you have a (PSC-Github)-Link or a Zip-File for that original database-project?
    I collected .zip and text information from 7 PSC CDs onto one DVD, created a database in .accdb format and wrote a program to work with the information. Unfortunately, a lot of textual information contains links to sites that are no longer working and very outdated information. I gradually change them to more relevant information. It remains to replace about 70 points. I can give a link to an intermediate version of .iso.

  14. #14
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Microsoft.ACE.OLEDB problem

    You can use MS Access to convert your ACCDB format database to MDB format. As long as you aren't using weird ACE features like multi-valued columns that should be seamless.

    Your program should need little change aside from modifying the connection string Provider setting.

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Re: Microsoft.ACE.OLEDB problem

    Quote Originally Posted by dilettante View Post
    You can use MS Access to convert your ACCDB format database to MDB format.
    Thought the .accdb format is more modern.
    Is Provider = Microsoft.Jet.OLEDB.4.0 supported on Win10 and Win11?

  16. #16
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Microsoft.ACE.OLEDB problem

    Modern how?

    The people behind MS Access took the Jet 4.0 engine, tweaked a bunch of stuff up, then added a few features only usable by MS Access and SharePoint.

    "Modern" doesn't buy you very much. The only important thing was they fixed some signed values internally so they can read and write to twice as big a file. Do you really need a 4GB database though?

    Jet 4.0 is a part of Windows 10. Since there is no Windows 11 yet (only some preview sucker-bait that may well blow up in a lot of fools' faces down the road) it is hard to answer what Windows 11 might do once it actually comes out.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2020
    Posts
    370

    Re: Microsoft.ACE.OLEDB problem

    Thanks.
    I will try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width