VB6 application not connecting to Access database 2016 in windows 10

[kfh75]

Hi

I have a peculiar problem. I have developed an application using VB6 & Access 2016 on windows 10. Application connects to database on my development machine without any problem. However i had issues earlier connecting to the database on my development machine, which i resolved after installing the latest mdac.

However when I deploy the application package (By using application and deployment wizard) on another machine having windows 10 or windows 8, I am not able to connect to database. After installing VB6 on these machines, i got the connection error '3706'. I have tried installing MDAC 2.8, but it is not getting resolved.

Please help me to identify the cause of the issue.


Many Thanks
Faisal

[dilettante]

MDAC has been gone for quite a long time, and all supported versions of Windows ship with Windows DAC instead. These are protected OS components so you can't "install" old crap versions of things over them.

Error 3706 is most commonly "Provider cannot be found.It may not be installed properly."


Since you have told us very little, we'd have to guess. Could it be that you are trying to use the ACE 16.0 Provider on machines where it was never installed?

The ACE components are not part of MDAC, DAC, or Windows and are not directly deployable as parts of an application. Users who do not have Access 2016 installed must manually acquire and run the Access 2016 Runtime installer or else your package must include the entire thing and run it as a sub-install.

Microsoft Access 2016 Runtime


You should not be fiddling around trying to install MDAC on a current supported version of Windows. Fortunately Windows will protect itself, chuckle at you, and ignore your attempts to break the system.

[gibra]

This is the download to install the database engine, enough to connect databases .accdb.

A description of Access Database Engine 2010 Service Pack 1
https://support.microsoft.com/en-us/kb/2460011

Seem to be the latest engine released.

see also
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center
https://www.microsoft.com/en-US/down....aspx?id=13255

[kfh75]

Thanks Delittante & Gibra for responding. I have tried your solutions but it didn't work. Let me explain what i am doing:

1. I am using Microsoft.ACE.OLEDB.12.0 as provider. I developed vb6 application using this provider on my win 10 machine. After packaging it to another machine of win 10, the database is not getting connected. However Access 2016 is already installed there.

2. To try once again, I have made one Hyper-V machine and installed Win7 with service pack1 and installed access 2016 run time. The vb application didn't work. I installed access 2016, again the vb application didn't work.

Is there something i am missing or anything strange i am doing? Thanks for your help and getting me out of this trouble.

Regards
Faisal

[dilettante]

The ACE 12.0 provider may not work with ACE 16.0 format .ACCDB files. I really wouldn't expect it to.

If you want to use .ACCDB formats you have to stay in sync, there is no stability and no upward compatibility. There should be backward compatibility, i.e. Access 2016 and the ACE 16.0 Provider should be able to create and use 12.0 format files. But this does not occur by magic, you have to specify the engine type when you create the database.

I can't find anywhere that ACE OLEDB Engine Type values are documented.

In any case if you are using ACE 12.0 format .ACCDB files then you want to install and work against the ACE 12.0 Provider. Whether that can coexist in a machine where MS Access 2016 has been installed I couldn't say.


If your program is requesting the ACE 12.0 Provider and you haven't installed it, then of course you will get that error 3706. Pretty obvious. But that isn't what you claimed in post #1 above, you said you were using MS Access 2016, which implies ACE 16.0 instead.

If you are actually using ACE 12.0 you can probably just ignore the fact that MS Access 2016 is installed at all, and deploy the ACE 12.0 Provider with your program.

[dilettante]

Better yet, run away from ACCDB as fast as you can!

Stick to the proven, preinstalled with Windows, native Jet 4.0 MDB format instead.

ACCDB and other "modern" (cough, cough, choke!) MS Access formats are unstable and a long term nightmare. They buy you very little and can cost you many gray hairs. The rate of churn is only overshadowed by that of .Net Frameworks.

[kfh75]

Seems the problem is getting zeroed in now. Yes I am using access 2016, which is installed on my development machine. And I am using ACE 12.0 as provider to connect to database. Database is connected without any issues. But when i use ACE 16.0 on my development machine, i am getting the same '3706' error.

ACE OLEDB Engine Type values is not being used while connecting. How to identify which provider is being installed in the machine. Seems even after installing access 2016 runtime, the driver is not getting updated in the machine.

Regards
Faisal

[dilettante]

ACE OLEDB Engine Type values is not being used while connecting.

Oh it is, you are just letting that property default.

A test here shows that ACE still calls this property "Jet OLEDB:Engine Type" and the value I get using ACE 12.0 is 6, though I have no idea what this constant's name is.

For example 5 is named JET_ENGINETYPE_JET4X, i.e. Jet 4.0, but I can't find the constants in any Windows SDK header file and only in MSDN documentation. That has not been updated to reflect a value of 6 or later.

JET_ENGINETYPE_DBASE3 = 10, so I suppose they had begun using values between 5 and 10 for ACE formats. But they are running out of room fast.


You can make a "junk.udl" file and open it in Explorer to get to the properties definition dialog. The Provider tab there should show all of the OLEDB Providers installed on the machine.

[adix]

you can detect the installed Drivers via the registry.

a) [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI
in order to get the names

b) [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\{Name}]\Driver
in order to get the filepath to the Driver

c) dependend on the driver and version .. you can use a lookup table to Setup the correct connection string.

But as mentioned - avoid if possible the usage of this 'new' drivers .. or to work with the 'new' database formats.

[dilettante]

We aren't talking about ODBC Drivers here. It isn't 1992 after all.

[DEXWERX]

Better yet, run away from ACCDB as fast as you can!

Stick to the proven, preinstalled with Windows, native Jet 4.0 MDB format instead.

ACCDB and other "modern" (cough, cough, choke!) MS Access formats are unstable and a long term nightmare. They buy you very little and can cost you many gray hairs. The rate of churn is only overshadowed by that of .Net Frameworks.

I couldn't agree more.

[IkkeEnGij]

telling people what not to do, is nice
telling people why not to do it, is nicer
giving people an example of why not to do it; is nicer still

[dilettante]

The "why" is the issue of Provider fragmentation. As for an example, this whole thread covered that.

[gibra]

Thanks Delittante & Gibra for responding. I have tried your solutions but it didn't work. Let me explain what i am doing:

1. I am using Microsoft.ACE.OLEDB.12.0 as provider. I developed vb6 application using this provider on my win 10 machine. After packaging it to another machine of win 10, the database is not getting connected. However Access 2016 is already installed there.

2. To try once again, I have made one Hyper-V machine and installed Win7 with service pack1 and installed access 2016 run time. The vb application didn't work. I installed access 2016, again the vb application didn't work.

The format of the database .accdb (2007-2016) is compatible with all versions (see image below captured from MSAccess 2016) and you just install in 'another machine' the Provider ACE and nothing else.


MS Access 2016 - Options


Therefore you can install 2007 (32bit setup only) version (Windows XP min. compatible)...
Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center
http://www.microsoft.com/en-US/downl....aspx?id=23734

... or install 2010 (both setup 32/64 bit - SP2) version (Windows XP SP3 min. compatible)
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center
https://www.microsoft.com/en-US/down....aspx?id=13255

Doesn't matter if Access 2016 is alread installed, you must install it.

I execute the below test on my Windows 7 SP1 + Office 2013, using a Database2016.accdb create with MSAccess 2016 and work perfectly:

    ' path for database (change path to use other database)
    sDBPath = App.Path & "\" & "Database2016.accdb"
    
    ' open connection
    Set cn = New ADODB.Connection
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sDBPath
        .Open
    End With

[dilettante]

Ahh, makes sense. Same ACE 12.0 Provider used for all .ACCDB files so far then. These all must be Engine Type 6 then.

[kfh75]

Hi Dilettante & Gibra

Thanks for your valuable comments and advice. Yes i was using ACE.12.0 on my development machine, but the same was not working when i am packaging and installing on another machine. ACE.16.0 was not working at all on my development machine. Finally, i took the advice of Dilettante and downgraded the version of 2016 to 2007, and it worked wonderfully on the machine i intended to with ACE.12.0. Even downgraded further to 2003 and worked with JET.4.0
I feel there is still instability with drivers for latest Access version as we ship the vb programs in other machine.
Nevertheless, my issue is resolved at the moment.

Thank You for all your help and time.

Regards
Faisal

[IkkeEnGij]

i suppose it will not work with ACE.23.0 either
i wonder where the use of ACE.16.0 comes from ?, can anyone show a site or something ?
ACE.12.0 will work with .mdb to, from access 2000 upwards

[mfcykowski]

If it is of any value, I had a VB6 program that worked great on Windows 10 PCs with Office 2016. Once the customers upgraded to Office 365, the program was getting the 3706 Provider error. After lots of searching, I finally installed the Microsoft Database Engine 2010 Redistributable and all is well. Program working fine.

[StillVBcrazy]

Thank you all and especially gilbra & mfcykowski,
Your suggestion of installing the Microsoft Database Engine 2010 Redistributable works.

My symptoms were,
VB6 could read accdb database first time, but IDE completely crashed without error messages when run again.
compiled prograsm would open, read, and close ok, even in a loop of 20 iterations.
using Provider=Microsoft.ACE.OLEDB.12.0;

[VB6 Programming]

Better yet, run away from ACCDB as fast as you can!

Stick to the proven, preinstalled with Windows, native Jet 4.0 MDB format instead.

ACCDB and other "modern" (cough, cough, choke!) MS Access formats are unstable and a long term nightmare. They buy you very little and can cost you many gray hairs. The rate of churn is only overshadowed by that of .Net Frameworks.

I can't emphasise enough how much I agree with this comment.

Never use .accdb format. Use .mdb format.

[Zvoni]

I can't emphasise enough how much I agree with this comment.

Never use .accdb format. Use .mdb format.

... or throw Micro s h i t where it belongs, and use sqlite for local, filebased database

[StillVBcrazy]

I would love to avoid using .accdb but I am writing an all purpose application that will be able to read data from various existing databases in external organisations so I cannot choose the DB backend. I can choose my DB that holds all the system vars and that will be mdb.

In an update to the above I had to install the Microsoft Database Engine 2010 Redistributable again after a windows update as my symptoms recurred.