How to run a module in ms access from C++

Question

I have MS Access database, and I want to run this module from a C++ application to export attachments .

If I open Access DB and go to VB and run the module it works as expected. ... cool!

Now I want to run that module externally to do the same thing.

Below has been tested and it works good.

HOW to run this from a C++/MFC side Dll ... Dll is what I'm working with so this is the preferred. I have tried to get it to work with DAO but I was not successful .. I assume that is phased out (DAO) and am left with ODBC or direct SQL calls but I do not know how to get images exported from Direct sql calls using (Function attached). But I have not been able to get it to work.

( This can do direct call but how to do a direct call to export images ")
executeSQL((SQLWCHAR*)"SELECT Attachments FROM tblTools WHERE ID=1", NULL, pRst.m_pDatabase->m_hdbc);

bool executeDirectSQL(SQLWCHAR *SQL, SQLHANDLE hDbConn)  
{  
	SQLRETURN sr;                           //Return code for your ODBC calls  
	SQLHSTMT hstmt;  
	bool m_bSuccess = 1;  
	// Allocate new Statement Handle based on previous connection  
	sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);  
	if (sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {  
  
		m_bSuccess = 0;  
	}  
  
  
	sr = SQLExecDirectW(hstmt, SQL, SQL_NTS);  
	if (sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {  
		m_bSuccess = 0;  
	}  
	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);  
  
	return m_bSuccess;  
}  

Answer 1

As I said earlier I'm not an Access user. However, in the hope that this will be helpful I put together a brief example that automates Excel from an MFC dialog application. The example starts Excel, opens a macro enabled workbook and runs the macro and displays the result.

Since you mentioned that your code would run from a DLL you must ensure that the application has initialized COM for use in a Single Threaded Apartment. Typically, an MFC application (.exe.) would call AfxOleInit. A non-MFC application might call OleInitialize() or CoInitialize(Ex). The example was put together with the 64-bit version of Office 2013. Obviously you would need to adapt the example to your particular circumstances (i.e., Office version and Access instead of Excel). I hope you can use this as a guide.

In a header file -
// Imports Excel type library
#import "C:\Program Files\Microsoft Office\Office15\Excel.exe" auto_search auto_rename

In a .cpp file to do the work -

try  
    {  
        Excel::_ApplicationPtr pApp;  
        Excel::WorkbooksPtr pBooks;  
        Excel::_WorkbookPtr pBook;  
        CString clicked;  
  
        // Start Excel Application and get Application Object  
        _com_util::CheckError(pApp.CreateInstance(L"Excel.Application", NULL, CLSCTX_LOCAL_SERVER));  
  
        // Get Workbooks collection  
        pBooks = pApp->Workbooks;  
  
        // Open the workbook containing the macro  
        pBook = pBooks->Open(_bstr_t(L"C:\\Users\\RLWA32\\Documents\\Testbook2.xlsm"));  
  
        // Run the VBA function named SayHello and pass it a string  
        _variant_t result = pApp->Run(_variant_t("SayHello"), _variant_t("Passed Parameter"));  
  
        // Get return values from SayHello Function  
        clicked.Format(_T("Buttton clicked was %s"), V_I4(&result) == IDOK ? _T("OK") : _T("Cancel"));  
  
        // Close the workbook without saving  
        pBook->Close(_variant_t(false));  
  
        // Quit Excel  
        pApp->Quit();  
  
        // Dispaly SayHello return values  
        AfxMessageBox(clicked, MB_ICONINFORMATION);  
    }  
    catch (_com_error& ce)  
    {  
        AfxMessageBox(ce.ErrorMessage(), MB_ICONERROR);  
    }  
  

Excel VBA macro -

Running the macro from MFC -

Display the result -