Click here to Skip to main content
15,885,309 members
Search within:


Handling fields of Attachment type in MS Access using ADO
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have some fields in my database that will need to store images ( bitmap, JPG or PNG ) and PDF ( or Excel/RTF/TXT ) files. Browsing through Internet, I have learned that MS Access 2007 ( and newer versions ) have a field of type Attachment that can suit my needs.

However, I am filling database via GUI ( made in C++ and WinAPI ), using ADO. I was not able to find an example of inserting/loading such data into/from database using ADO. Searching on Stack Overflow and here through some similar questions ( VB, Delphi, Python...) I have found out that my approach might not be the best, but seems possible to do.

Being inexperienced, and since this is my first time tackling this type of task, I ask community to help me solve this task. Therefore my question:

How should I handle inserting/loading this type of data ( images, documents...) into/from MS Access 2007 (or higher) database using ADO?

Can you provide small code snippet that illustrates inserting/loading such data into/from database using ADO?

Thank you.
Posted
Updated 18-Nov-14 10:36am
v2
Add a Solution

2 solutions

I just did this in a project myself. What I had to do was Create the DB field as an "OLE Object" (field type). To read/write the field I used a SafeArray packed in a COleVariant (vt = VT_ARRAY | VT_UI1).

Heres some example code:

// ADO - 2.7 comes with WinXP
#import "msado27.tlb" no_namespace \
	rename("EOF", "EndOfFile") \
	rename("Connection", "ADOConnection") \
	rename("Recordset", "ADORecordset")

CString g_cstrDBCon	= _T("Driver={Microsoft Access Driver (*.mdb)};Dbq=Test.mdb;Uid=;Pwd=;");

#define __DBCLOSE(AdoObj) { \
	if(AdoObj) { \
		if(AdoObj->State == adStateOpen) \
			AdoObj->Close(); \
		AdoObj.Release(); \
		AdoObj = NULL; \
	} \
}


BOOL Load()
{
	BOOL bRetVal = FALSE;
	HRESULT hr = NOERROR;
	COleVariant cvFieldVal;
	CString cstrSQL = _T("");
	CString cstrDBError = _T("");
	_ConnectionPtr pCn = NULL;
	_RecordsetPtr pRs = NULL;

	try
	{
		// Create database objects 
		if(FAILED(hr = pCn.CreateInstance(__uuidof(ADOConnection))))
			_com_issue_error(hr);
		if(FAILED(hr = pRs.CreateInstance(__uuidof(ADORecordset))))
			_com_issue_error(hr);
		// Open connection
		if(FAILED(pCn->Open(_bstr_t(g_cstrDBCon), "", "", adConnectUnspecified)))
			_com_issue_error(hr);
		// Open and read table
		pRs->Open(_bstr_t(L"TableName"), _variant_t((IDispatch*)pCn, true),
			adOpenDynamic, adLockPessimistic, adCmdTable);
		while(!pRs->EndOfFile)
		{
			// Get [ID] (long value example)
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"ID"))->GetValue();
			long lID = cvFieldVal.lVal;
			// Get [Name] (string value example)
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"Name"))->GetValue();
			CString cstrName = cvFieldVal.bstrVal;
	
			// Get [BinaryValue]
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"BinaryValue"))->GetActualSize();
			long lActSize = cvFieldVal.lVal; // Get binary data size
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"BinaryValue"))->GetChunk(pNewProp->m_lDefValLen);
			if(cvFieldVal.vt == (VT_ARRAY|VT_UI1))
			{
				BYTE* pBinaryData = new BYTE[lActSize];
				char* pBuf = NULL;
				SafeArrayAccessData(cvFieldVal.parray, (void**)&pBuf);
				memcpy(pBinaryData, pBuf, lActSize);
				SafeArrayUnaccessData(cvFieldVal.parray);
	
				// Do something with data ... 

				delete [] pBinaryData;
			}

			// Next record
			pRs->MoveNext();
		}
		pRs->Close();
		bRetVal = TRUE;
	}
	catch(_com_error& e) 
	{ 
		if((pCn) && (pCn->Errors->Count > 0)) 
		{
			ErrorPtr pErr = pCn->Errors->GetItem(0); 
			cstrDBError.Format(L"Error %x: %s", pErr->Number, (LPWSTR)pErr->Description); 
			pCn->Errors->Clear(); 
		} 
		else 
		{ 
			cstrDBError.Format(L"Error %x: %s", e.Error(), (LPWSTR)e.Description()); 
		} 
	} 
	catch(...) 
	{ 
		cstrDBError = L"Unknown Error"; 
	}

	__DBCLOSE(pRs);
	__DBCLOSE(pCn);
	
	if(!bRetVal)
	{
		MessageBox(cstrDBError, _T("DB Error"), MB_ICONERROR);
	}

	return bRetVal;
}

BOOL Save()
{
	BOOL bRetVal = FALSE;
	HRESULT hr = NOERROR;
	COleVariant cvFieldVal;
	CString cstrSQL = _T("");
	CString cstrFilter = _T("");
	CString cstrDBError = _T("");
	_ConnectionPtr pCn = NULL;
	_RecordsetPtr pRs = NULL;

	try
	{
		// Create database objects 
		if(FAILED(hr = pCn.CreateInstance(__uuidof(ADOConnection))))
			_com_issue_error(hr);
		if(FAILED(hr = pRs.CreateInstance(__uuidof(ADORecordset))))
			_com_issue_error(hr); 
		// Open connection
		if(FAILED(pCn->Open(_bstr_t(g_cstrDBCon), "", "", adConnectUnspecified)))
			_com_issue_error(hr);

		long lRecordID = 1;

		// Position recordset
		cstrFilter.Format(L"ID=%d", lRecordID);
		pRs->put_Filter(COleVariant(cstrFilter, VT_BSTR));

		// Write [Name] (string value example)
		CString cstrVal = _T("Some Text");
		pRs->Fields->GetItem(_bstr_t(L"Name"))->put_Value(COleVariant(cstrVal, VT_BSTR));
			
		// Dummy binary value
		long lBinaryValLen = 333;
		BYTE* pValue = new BYTE[lBinaryValLen];
		memset(pValue, 1, lBinaryValLen);

		// Create safearray  
		SAFEARRAYBOUND sab[1];
		sab[0].lLbound = 0;	
		sab[0].cElements = lBinaryValLen;
		SAFEARRAY FAR* psa = SafeArrayCreate(VT_UI1, 1, sab);
		BYTE* pByte;
		if(SafeArrayAccessData(psa, (void**)&pByte) == NOERROR)
			memcpy(pByte, pProp->m_pDefValue, pProp->m_lDefValLen);
		SafeArrayUnaccessData(psa);
		VARIANT vArray;
		VariantInit(&vArray);
		vArray.vt = VT_ARRAY|VT_UI1;
		vArray.parray = psa;

		// Write [BinaryValue]
		if(FAILED(pRs->Fields->GetItem(_bstr_t(L"BinaryValue"))->AppendChunk(vArray)))
			_com_issue_error(hr);
		VariantClear(&vArray);
		SafeArrayDestroy(psa);
	
		pRs->Update();
		pRs->Close();

		bRetVal = TRUE;
	}
	catch(_com_error& e) 
	{ 
		if((pCn) && (pCn->Errors->Count > 0)) 
		{
			ErrorPtr pErr = pCn->Errors->GetItem(0); 
			cstrDBError.Format(L"Error %x: %s", pErr->Number, (LPWSTR)pErr->Description); 
			pCn->Errors->Clear(); 
		} 
		else 
		{ 
			cstrDBError.Format(L"Error %x: %s", e.Error(), (LPWSTR)e.Description()); 
		} 
	} 
	catch(...) 
	{ 
		cstrDBError = _T("Unknown Error"); 
	}

	__DBCLOSE(pRs);
	__DBCLOSE(pCn);
	
	if(!bRetVal)
	{
		MessageBox(cstrDBError, _T("DB Error"), MB_ICONERROR);
	}

	return bRetVal;
}



Good luck
 
Share this answer
 
Posted
Updated 19-Nov-14 2:40am
v4
Comments
AlwaysLearningNewStuff 19-Nov-14 11:54am    
Thank you so much! I am in a mess currently as I must design the GUI for the app that uses this code so please give me time ( I have to find a way to place 50+ controls on a single form -> madness, but one can't argue with the boss ). Best regards.
JJMatthews 20-Nov-14 3:24am    
No problem man, glad if I could help. Good luck on your project.
KarstenK 20-Nov-14 13:39pm    
be really carefully in error handling. Give every error an internal value, so you can find the function which fail.

Dont ferget: "Error handling is the art of developing software" ;-)
AlwaysLearningNewStuff 2-Dec-14 0:29am    
I have tried to adapt your code but have failed to succeed. I have never used variants and safe arrays before, so that might be the problem.

I have added the smallest possible code snippet below of what I have tried.

Test database has 1 table test and 2 fields ID, which is primary key, and field which is of OLE Object type.

Please correct my mistakes if that is not much of a trouble for you ( error checking is omitted ) :

wchar_t *bstrConnect = L"Provider=Microsoft.ACE.OLEDB.12.0; \
Data Source = C:\\Users\\Smiljkovic85\\Desktop\\OLE.accdb";

try
{
HRESULT hr = CoInitialize(NULL);

// connection
ADODB::_ConnectionPtr pConn(L"ADODB.Connection");
// recordset
ADODB::_RecordsetPtr pRS(L"ADODB.Recordset");

// connect to DB
hr = pConn->Open(bstrConnect, L"admin", L"", ADODB::adConnectUnspecified);

// open file
std::ifstream in(L"C:\\Users\\Smiljkovic85\\Desktop\\Kings Indian Atack.pdf",
std::ios::ate | std::ios::binary);

// get file size
LONG fileSize = in.tellg();

// here I tried to apply your code
pRS->Open(L"test", _variant_t((IDispatch*)pConn, true),
ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
// add new record
pRS->AddNew();

_variant_t varChunk;

SAFEARRAY FAR *psa;
SAFEARRAYBOUND rgsabound[1];
rgsabound[0].lLbound = 0;
// modify to our file size
rgsabound[0].cElements = fileSize;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound);

//=================== tried this as well but no luck...
char *chData = (char *)psa->pvData;
chData = new char[fileSize];
in.read(chData, fileSize);

======================== */
char *chData = new char[fileSize];
in.read(chData, fileSize);

BYTE* pData;
SafeArrayAccessData(psa, (void **)&pData);
memcpy(pData, chData, fileSize);
SafeArrayUnaccessData(psa);

// Assign the Safe array to a variant.
varChunk.vt = VT_ARRAY | VT_UI1;
varChunk.parray = psa;

pRS->Fields->GetItem(L"field")->AppendChunk(varChunk);
// add this record into DB
pRS->Update();

// cleanup
delete[] chData;
in.close();
pRS->Close();
pConn->Close();
CoUninitialize();
}
catch (_com_error e)
{
MessageBox(hWnd, (LPWSTR)e.Description(), L"", 0);
}

}


I apologize for taking me too long to respond but I really tried to fix this on my own...

EDIT:
----

I have searched here for help and two posts that gave me an idea. Still none of my solutions work. You can see them in the above code snippet, in the comments.

What I get now, is the following error: a problem occurred while microsoft access was communicating with the ole server or activex control in MS Access. I have searched online for solution but had no luck, every link claims it has to do with access and not with the code.

Best regards.
JJMatthews 2-Dec-14 2:22am    
Hello, that is a new error for me. I looked through your code and it does look fine, I would say that the error is definitely from Access (as you thought).

I did on search on your specific error and saw allot of different information, but this page is what I believe may be happening:
http://support.microsoft.com/kb/295824/EN-US
I have had issues with MS Office "security" before. I have also had allot of issues with Norton products.

I came across this article as well and I believe it's worth investigating if the previous solution does not help:
http://support.microsoft.com/kb/907337/en-us


Here is a really good resource, I found it invaluable when learning ADO coding:
https://www.microsoft.com/en-us/download/details.aspx?id=21995


What you are trying to do will work and has been done many times before, I will help you in any way that I can. The good thing is that you always learn allot from issues like this.

In your initial post you said that you are running Access 2007 and were planning to use an "Attachment" field type. The biggest difference between what I have done and what you are working on I believe is the Access version and the field type. I am using Access 2003 with "OLE Object" field type for my blobs. I was using Access to design and test my database schema. I am now running Oracle but all I had to do was change the connection string and everything was fine.

If you are still having the problem let me know and I will install Access 2007 and create a test database.

Good luck man
Jeremy
My tip is to save images and files NOT in the database, but in some extra directories on the db-server. Be sure to give unique names, so files arent mismatch or overwritten. And delete the files if the dataset is deleted...

Looks like Microsoft has some good example code.
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Saving data using ado vb.net after checking 2 fields access 2003 db
How to send attachment as pdf to the email using PHP?
MS Access double’s number format obstructs ADO query
Vb net with MS Access field type
ADO, SQL Stored Proc, and Error #13(type mismatch)
MS Access Date field formatting
Date format for MS Access ADO C++ programming
How to delete a record in ms access database using VB.NET
Can't create a Auto Increment Field in MS Access
Dynamic Field Structure for Item (Part) Description - ADO/SQL/C#

Advertise
Privacy
Cookies
Terms of Use
Last Updated 19 Nov 2014
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900