Is there a difference between using Provider Access and Provider ODBC?

[goldesel]

Hi,

from what I've gathered, the Access provider is simply using ODBC to connect to a database.
That of course makes me wonder if there are major differences between these two providers or if they behave the same?

These are the ConnectStrings, we are using:

Access:
sConnectionString := 'Provider Name=Access;' +
'User ID=Admin;' +
'Password=123;' +
'Database=' + Database + ';' +
'Login Prompt=False;' +
'ExtendedAnsiSQL=True';

ODBC:
sConnectionString := 'Provider Name=ODBC;' +
'DSN Type=ntConnectionString;' +
'Server="' +
'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
'DBQ=' + Database + ';' +
'uid=Admin;' +
'pwd=123;' +
'ExtendedAnsiSQL=1'
'";' +
'Login Prompt=False';

Would UniDac behave the same with those two or should we expect the ODBC variant to behave differently?

----------------------

Little background:
I'm asking this because a needed module is using an old version of the mso.dll of Office 2003. And since the new version of the mso.dll has the same name, the dll won't be loaded twice. So when unidac tries to connect to a database, we encounter an exception within the mso.dll.
The real problem here is that we'd like to use the Access Database Engine of Office 2010 that IS compatible with the mso.dll of Office 2003. But that is regularly overwritten by the Access Database Engine of Office 2016 or 2019 which are also installed on many of our customers' computers.
So we are simply registering the 2010 version under a new name and then want to use that with the ODBC provider of UniDac. So far it seems to make no difference, but we just want to make sure that we're not missing anything.

[oleg0k]

Hello,
You're right, the "Access provider" and "ODBC" use the same "Microsoft Access Driver" to connect to the database, therefore you get the same level of performance.
However there're slight differences: the Access Provider takes into account certain aspects of MS Access’s behavior (maximum allowed string length, the difference between an empty string and a NULL value, etc.).

wbr, Oleg

[goldesel]

maximum allowed string length

This seems to present a problem. When trying to use a UniQuery with a ftString Parameter, the execution will always fail with "invalid precision value".

Since you use the same driver under the hood, do you mind telling us what needs to be set for this error to be resolved?

Edit: using ParamByName('xy').AsMemo := 'sometext' works (even if the DataType is ftString). Using "AsText" results in that error. Any idea why?

[ertank]

Hello,

Having no idea about structure of your table I will make a guess here.

Access has two string data types as to my knowledge. First one is a short text with max length 255 characters. Other one is a long text. Later being handled as a memo field (aka blob) so you might need to handle such columns accordingly.

https://support.office.com/en-us/articl ... 067361987c

[goldesel]

It's not the underlying column that is the problem here. We've got Text columns with a maximum of 255 characters and we've got Memo columns. Up until now both could be filled with "AsText". With ODBC both need to be filled with "AsMemo" while we still use ftString for Parameters meant for Text columns.

Another strange behavior is that you can't use "Param.Clear" on a String Parameter or the same "invalid precision value" exception will occur. If you want the column to be nulled then the parameter needs to hold an empty string.

[oleg0k]

Hello,
Unfortunately, we haven't been able to reproduce the issue.
To give you a detailed answer, we need to investigate that behavior in our environment: please send us a code sample demonstrating the issue, along with the scripts for creating the database objects and populating them with data (or just the *.mdb file). Also specify the exact error message.
You can post the DDL script and the code sample here on the forum. If you wish to send us the *.mdb file, you can do it through the form on our website: https://www.devart.com/company/contactform.html

wbr, Oleg
DAC Team