Microsoft Excel can open DBASE IV, i.e. *.dbf files, but...

[jj2007]

The ODBC documentation is horribly confused, but with trial and error we may resolve its mysteries. Attached a testbed - extract to a folder and run it. Hit Escape+Return to exit.

Queries that work:
select * from World_Countries
select * from World_Co
select * from otherdb3
select * from otherdb4
select * from otherdb4WhatEverSuitsYou

Queries that don't work:
select * from World_C
select * from otherdb2
select * from otherdb

There is even a logic in it, but it's pretty weird. Good old DOS 8.3 times ;-)

[jj2007]

New testbed. I've managed to open and read DBF (III and IV), XLS files and MDB files

Usage: drag a dbf, xls or mdb file over the exe; for mdb files, you need to adjust the query to main, i.e. take away the numbers.

One oddity is that dbf and xls files require a path to the folder, while mdb files need a filename (the testbed handles this automatically).

Question: can xls files (testjj2.xls, testjj3.xls) be read with the tool if the user has not MS Office installed?

Can I have some feedback please for other Windows versions? I have tested in on Win10 and a WinXP VM (where only Excel 3.0 works).

P.S. Below results of C:\WINDOWS\SysWOW64\odbcad32.exe, for admin and ordinary user.

[TimoVJL]

https://answers.microsoft.com/en-us/msoffice/forum/all/i-need-to-locate-and-download-the-odbc-driver-for/27563edf-fb35-4415-ba68-e0de5b95b963
https://stackoverflow.com/questions/42782449/missing-accdb-database-drivers#43434633

EDIT:

Code Select Expand

#define WIN32_LEAN_AND_MEAN
#include <windows.h>
#include <stdio.h>
#pragma comment(lib, "user32.lib")

int __cdecl main(void)
{
    HKEY hKey, hKey2;
    DWORD dwIdx, dwRC, dwType;
    char szSub[260], szValue[260], szTmp[260];

    hKey = 0;
    szValue[0] = 0;
    if (!RegOpenKey(HKEY_LOCAL_MACHINE, "SOFTWARE\\ODBC\\ODBCINST.INI", &hKey))
    {
        dwIdx = 0;
        while (RegEnumKey(hKey, dwIdx++, szSub, sizeof(szSub)) == ERROR_SUCCESS)
        {
            wsprintf(szTmp, "SOFTWARE\\ODBC\\ODBCINST.INI\\%s", szSub);
            if (!RegOpenKey(HKEY_LOCAL_MACHINE, szTmp, &hKey2))
            {
                dwRC = sizeof(szValue);
                dwType = REG_SZ;
                if (RegQueryValueEx(hKey2, "Driver", NULL, &dwType, (BYTE *)szValue, &dwRC))
                    ;
                //puts(szTmp);
                //puts(szValue);
                printf("%s\t%s\n", szTmp, szValue);
                RegCloseKey(hKey2);
            }
        }
        RegCloseKey(hKey);
    }
    return 0;
}

EDIT: perhaps useless to download, as those have bugs.

unable to load odbcji32.dll

[jj2007]

Thanks, Timo

As much as possible, I want to stick with components that are already installed by default. So far I can read older xls, dbf and mdb files with standard components.

[sinsi]

A few discoveries
 - a DBF file is not a database, but a database table
 - most versions of ODBC truncate the filename to 8.3, so it might work if you use the short name
 - downloading the FoxPro9 OLEDB driver seems to open any DBF, including the list that didn't work

A direct download link is https://github.com/VFPX/VFP9SP2Hotfix3/raw/master/VFPOLEDBSetup.msi

[TimoVJL]

old 32-bit version
VFPODBC.msi

EDIT:
Using it directly, don't have install it.

Code Select Expand

"DRIVER={Microsoft Visual FoxPro Driver};SourceType=DBF;DBQ=xxxx;"

[jj2007]

A few discoveries
 - a DBF file is not a database, but a database table

Right.

- most versions of ODBC truncate the filename to 8.3, so it might work if you use the short name

Unfortunately, it's not that easy.

- downloading the FoxPro9 OLEDB driver seems to open any DBF, including the list that didn't work

A direct download link is https://github.com/VFPX/VFP9SP2Hotfix3/raw/master/VFPOLEDBSetup.msi

As written above, I want to stick with components that are already installed by default, so that potential users don't shy away from using the macros.

Currently, I am looking into OLE DB. A can of worms, of course, but I am confident that I can come up with a read sheet xyz from test.xls into the Sql$() array in less than 100 lines of underlying code.

Happy New Year to everybody, and thanks for all the suggestions

[sinsi]

As written above, I want to stick with components that are already installed by default, so that potential users don't shy away from using the macros.

On a clean install of Windows 7 I could open 2/3 MDBs and 2/4 XLSs with your proggie but I had to make a User DSN for each type - the user and system lists were both empty.

[TimoVJL]

What drivers come in clean installation ?
MDAC ?

EDIT:
Microsoft OLE DB Provider for Jet and Jet ODBC driver are available in 32-bit versions only

[sinsi]

Win7 has the odbc jet drivers for mdb and xls but they don't work woth office 97

[jj2007]

As written above, I want to stick with components that are already installed by default, so that potential users don't shy away from using the macros.

On a clean install of Windows 7 I could open 2/3 MDBs and 2/4 XLSs with your proggie but I had to make a User DSN for each type - the user and system lists were both empty.

Interesting. My Win7-32 VM, which should be "clean" as there is no Office installed, has no user or system DNS, but I can read Access files from '97 to 2002 (maybe higher, I don't have samples at hand). Plus Excel 3.0 (but not 4.0 or 5.0), and DBASE III + IV (but not II).

Microsoft Office 97 (version 8.0) is the fifth major release for Windows of Microsoft Office, released by Microsoft on November 19, 1996

Office 97 still has some friends. I stick to Office 2003, and feel a bit odd to use a software that is over 20 years old. Just can't get used to ribbon interfaces

Happy New Year, sinsi and stoo   (we have 5h40' left)

[stoo23]

Happy New Year, sinsi and stoo   (we have 5h40' left)

  Thanks, hope you had a good one.