Example using ado

[TimoVJL]

This is just an example using ado

Code: [Select]

#define UNICODE
#define _UNICODE
#define WIN32_LEAN_AND_MEAN
#include <windows.h>
#define INITGUID
#include <ole2.h>
#include <stdio.h>
#include <tchar.h>

#include "msado15.h"

#pragma comment(lib, "user32.lib")
#pragma comment(lib, "ole32.lib")
#pragma comment(lib, "oleaut32.lib")

TCHAR *GetADOErrorCode(DWORD nErr);

int main(int argc, char **argv)
{
HRESULT hr;
Recordset15 *pRst = NULL;

hr = CoInitialize(NULL);
hr = CoCreateInstance(&CLSID_Recordset, NULL, CLSCTX_ALL, &IID_Recordset15, (void **)&pRst);
if (hr)
MessageBox(0, TEXT("Error CoCreateInstance()"), 0, 0);
else
{
VARIANT v1, v2;
VariantInit(&v1);
v1.vt = VT_BSTR;
v1.bstrVal = SysAllocString(L"SELECT * FROM test");
VariantInit(&v2);
v2.vt = VT_BSTR;
v2.bstrVal = SysAllocString(L"Driver={Microsoft Access Driver (*.mdb)};Dbq=test.mdb;");
// STDMETHOD(Open)(THIS,VARIANT,VARIANT,enum CursorTypeEnum,enum LockTypeEnum,LONG);
hr = pRst->lpVtbl->Open(pRst, v1, v2, adOpenKeyset, adLockUnspecified, adCmdText);
SysFreeString(v1.bstrVal);
SysFreeString(v2.bstrVal);
//hr = pRst->lpVtbl->Open(pRst, v1, v2, 1, -1, -1);
if (!hr) {
Fields* pFlds;
Field* pFld;
long nRecs = 0;
hr = pRst->lpVtbl->get_RecordCount(pRst, &nRecs);
_tprintf(TEXT("nRecs = %d\n"), nRecs);
hr = pRst->lpVtbl->get_Fields(pRst, &pFlds);
if (!hr) {
long nFlds;
VARIANT vIdx;
hr = pFlds->lpVtbl->get_Count(pFlds, &nFlds);
_tprintf(TEXT("nFlds = %d\n"), nFlds);
VariantInit(&vIdx);
vIdx.vt = VT_I4;
for (int i = 0; i < nFlds; i++) {
vIdx.lVal = i;
hr = pFlds->lpVtbl->get_Item(pFlds, vIdx, &pFld);
if (!hr) {
BSTR bstrTmp;
VARIANT v3;
VariantInit(&v3);
v3.vt = VT_BSTR;
v3.bstrVal = bstrTmp;
hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
if (!hr) {
_tprintf(bstrTmp);
SysFreeString(bstrTmp);
}
hr = pFld->lpVtbl->get_Value(pFld, &v3);
if (!hr) {
if (v3.vt == VT_BSTR) {
_tprintf(TEXT(" %ls\n"), bstrTmp);
SysFreeString(bstrTmp);
} else {
VariantChangeType(&v3, &v3, VARIANT_LOCALBOOL, VT_BSTR);
_tprintf(TEXT(" %ls\n"), bstrTmp);
SysFreeString(bstrTmp);
}
}
pFld->lpVtbl->Release(pFld);
}
}
pFlds->lpVtbl->Release(pFlds);
}
} else
_tprintf(TEXT("%Xh %ls\n"), hr, GetADOErrorCode(hr));
pRst->lpVtbl->Release(pRst);
}
CoUninitialize();

//MessageBox(0, TEXT("Done"), 0, 0);
return 0;
}Let me know, if there is something wrong.

[czerny]

If the database doesn't exist, I get: 80004005h Unspecified Error
Shouldn't be used in this case:  800A0F0Bh Database does not exist?

[TimoVJL]

A simple ADO test, not useful
Those who have MS Office or AccessDatabaseEngine installed can test it with test.mdb

[jj2007]

Looks good, Timo, and works fine, but how do you get the names of tables after OpenSchema? I tried this:

Code: [Select]

hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtCriteria, vtMissing, &pRst);
if (!hr) {
printf("Recordset tables=%i\n", pRst);
printf("Count=%i\n", pRst->GetRows);
pRst->lpVtbl->Release(pRst); // release the _Recordset
printf("Recordset close\n");
Unknown field 'GetRows' of '_Recordset (aka struct _Recordset)'
 

[TimoVJL]

something like this:

Code: [Select]

hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtCriteria, vtMissing, &pRst);
if (!hr) {
Fields* pFlds;
Field* pFld;
long nRecs = 0;
hr = pRst->lpVtbl->get_RecordCount(pRst, &nRecs);
printf("nRecs = %d\n", nRecs);
hr = pRst->lpVtbl->get_Fields(pRst, &pFlds);
if (!hr) {
long nFlds = 0;
hr = pFlds->lpVtbl->get_Count(pFlds, &nFlds);
printf("nFlds = %d\n", nFlds);
VARIANT vIdx;
VariantInit(&vIdx);
vIdx.vt = VT_I4;
for (int i = 0; i < nFlds; i++) {
vIdx.lVal = i;
hr = pFlds->lpVtbl->get_Item(pFlds, vIdx, &pFld);
if (!hr) {
BSTR bstrTmp;
VARIANT v3;
VariantInit(&v3);
v3.vt = VT_BSTR;
v3.bstrVal = bstrTmp;
hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
if (!hr) {
printf("%ls\n", bstrTmp);
SysFreeString(bstrTmp);
}
}
pFld->lpVtbl->Release(pFld);
}
pFlds->lpVtbl->Release(pFlds);
}
// done
pRst->lpVtbl->Release(pRst);
printf("Recordset close\n");
}

[Vortex]

Hi Timo,

Thanks, both of your applications are processing correctly the database file on Windows 7 Sp1 64-bit.

[jj2007]

Hi Timo,
This works fine so far, but it returns as names
 TABLE_CATALOG
 TABLE_SCHEMA
 TABLE_NAME
 TABLE_TYPE
etc. - the labels. What I am really interested in is the value of TABLE_NAME, i.e. the sheet's actual name, e.g. main.

What am I doing wrong here? It compiles with TheValue, but I get error 800a0bcd (EOF or BOF) and a nullstring

Code: [Select]

hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
if (!hr) {
VARIANT TheValue;
VariantInit(&TheValue);
TheValue.vt = VT_BSTR;
printf("Item %ls\n", bstrTmp);
hr = pFld->lpVtbl->get_Value(pFld, &TheValue);
printf("val %x %ls\n", hr, TheValue.bstrVal);
SysFreeString(bstrTmp);
}

Btw googling for "get_Fields" "get_name" "get_value" "get_RecordCount" puts this post on rank #2 - congrats

[John Z]

Hi TimoVJL,

A simple ADO test, not useful
Those who have MS Office or AccessDatabaseEngine installed can test it with test.mdb

How to test? I tried as posted and tried using the full path to test.mdb.
All I get is:

Code: [Select]

C:\Users\John\Downloads\Pelles\msado>msado_test2_64.exe
adStateClosed
Error count 0
I can open test.mdb with Access and see the table and entries.

Well, worth mentioning this is WIN11

John Z

[jj2007]

> using the full path

Hi John,

Try escaping the backslashes: D:\\PellesC\\test.mdb should work, at least it does on my Win10 machine.

@Timo: please see reply #6

[TimoVJL]

Hi John Z,
The msado_test2_64.exe won't work, as 64-bit don't have "Microsoft.JET.OLEDB.4.0" working connection string.
For x64 it's something like "Microsoft.ACE.OLEDB.12.0"
If you have 32-bit MS Office, it doesn't help.

[John Z]

Thanks TimoVJL, got it, looks good with the non-64 bit version
running Windows 11 Home, 23H2, OS Build 22631.2861
results:

Code: [Select]

C:\Users\John\Downloads\Pelles\msado>msado_test2.exe
nRecs = -1
nFlds = 9
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED
Recordset close
Thank jj2007 - I did double \ because it would not compile otherwise.  Cheers

John Z

[TimoVJL]

With this version:

Code: [Select]

nRecs = -1
TABLE_NAME      test

Recordset close

[jj2007]

Code: [Select]

nRecs = -1
TABLE_NAME      JJWasHere

Recordset close
Compliments, Timo - you are a genius

I had played around with the OpenSchema needs a SAFEARRAY problem, without success. Thanks a lot for looking into this.

Code: [Select]

BSTR bstrConStr = SysAllocString(L"Data Source=test.xls;Extended Properties='Excel 8.0;HDR=NO'");
Yields the name of an Excel sheet. Excellent

Same for dbf:

Code: [Select]

BSTR bstrConStr = SysAllocString(L"Data Source=.;Extended Properties='dBASE IV'");
The first line causes a linker error for me ("Unresolved external symbol '_GetADOErrorCode'"):

Code: [Select]

//printf("hr=%Xh %ls\n", hr, GetADOErrorCode(hr));
//ADOError(pCon);

[TimoVJL]

That ADOErrors.c is in msado_test2_WS1.zip amd msado_test2_WS1.zip, if you need it.

[jj2007]

Here is a list of the connection strings that work for me on Windows 10:

Code: [Select]

; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=test2000.mdb', 0
; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=testjj5.xls;Extended Properties="Excel 8.0"', 0
; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=testjj5.xls;Extended Properties="Excel 8.0;HDR=NO"', 0
; MyCon$ db 'Provider="Microsoft.JET.OLEDB.4.0";Data Source=test2000.mdb', 0 ; ok
; MyCon$ db 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.;Extended Properties=dBASE IV;', 0 ; ok

Note that the table name for Excel files is kind of boring: it always returns Database
For dBase, in contrast, you can only specify the folder, not a specific file, and it picks files from that folder randomly. Maybe it can be solved with SELECT * from specificfile.xls, I'll have to test that.

Thanks a lot, Timo, for all the work you put into this