-
Jul 27th, 2021, 11:51 AM
#1
Thread Starter
Hyperactive Member
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?
-
Jul 27th, 2021, 12:27 PM
#2
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.
-
Jul 27th, 2021, 12:54 PM
#3
Thread Starter
Hyperactive Member
Re: Microsoft.ACE.OLEDB problem
Originally Posted by dilettante
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.
-
Jul 28th, 2021, 03:09 AM
#4
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>
Last edited by wqweto; Jul 28th, 2021 at 05:59 AM.
-
Jul 28th, 2021, 04:38 AM
#5
Thread Starter
Hyperactive Member
Re: Microsoft.ACE.OLEDB problem
Originally Posted by wqweto
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.
-
Jul 28th, 2021, 12:49 PM
#6
Re: Microsoft.ACE.OLEDB problem
Originally Posted by Argus19
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
-
Jul 28th, 2021, 01:26 PM
#7
Thread Starter
Hyperactive Member
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.
-
Jul 28th, 2021, 06:03 PM
#8
Re: Microsoft.ACE.OLEDB problem
Originally Posted by Schmidt
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?
-
Jul 28th, 2021, 07:25 PM
#9
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.
-
Jul 28th, 2021, 11:54 PM
#10
Re: Microsoft.ACE.OLEDB problem
Originally Posted by dilettante
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
-
Jul 28th, 2021, 11:57 PM
#11
Re: Microsoft.ACE.OLEDB problem
Originally Posted by Episcopal
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
-
Jul 28th, 2021, 11:58 PM
#12
Re: Microsoft.ACE.OLEDB problem
Originally Posted by Argus19
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
-
Jul 29th, 2021, 04:36 AM
#13
Thread Starter
Hyperactive Member
Re: Microsoft.ACE.OLEDB problem
Originally Posted by Schmidt
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.
-
Jul 29th, 2021, 09:54 AM
#14
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.
-
Jul 29th, 2021, 01:36 PM
#15
Thread Starter
Hyperactive Member
Re: Microsoft.ACE.OLEDB problem
Originally Posted by dilettante
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?
-
Jul 29th, 2021, 01:46 PM
#16
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.
-
Jul 29th, 2021, 02:10 PM
#17
Thread Starter
Hyperactive Member
Re: Microsoft.ACE.OLEDB problem
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|