How to use VS to manipulate Access

1. Import ADO Class:

#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")

This statement should be in the file that we define the objects.

We get 3 smart pointer after import, they are: _ConnectionPtr, _RecordsetPtr , _CommandPtr

2. Init COM: in the function of BOOL CaccessApp::InitInstance()

MFC: AfxOleInit();

Other: CoInitialize(NULL); CoUnInitialize();

3. Connect Access:

_ConnectionPtr connection;
_RecordsetPtr recordset;
//Connect
connection.CreateInstance( __uuidof(Connection)); 
connection->Open("Provider=Microsoft.JET.OLEDB.4.0;Data Source=dictionary.mdb","","",adModeUnknown); 

4. Close Connection

//if connection is still valid
if(connection->State)
{
    connection->Close();
    connection = NULL;
}

5. Set connection time out

connection->put_ConnectionTimeout(long(5));

6. Open result set

_RecordsetPtr m_pRecordset;  
m_pRecordset.CreateInstance(__uuidof(Recordset));
　 m_pRecordset->Open("SELECT * FROM word",_variant_t((IDispatch *)connection,true),adOpenStatic,adLockOptimistic,adCmdText);

7. Close result set

m_pRecordset->Close();  

8. Iterate result set

while(!m_pRecordset->adoEOF)
{
    var = m_pRecordset->GetCollect("Name");
    if(var.vt != VT_NULL)
        strName = (LPCSTR)_bstr_t(var);
    var = m_pRecordset->GetCollect("Age");
    if(var.vt != VT_NULL)
        strAge = (LPCSTR)_bstr_t(var);
    m_AccessList.AddString( strName + " --> "+strAge );
    m_pRecordset->MoveNext();
}
        

9. Get Field value

m_pRecordset->GetCollect("Name");

m_pRecordset->GetCollect(_variant_t(long(0));

pRecordset->get_Collect("COLUMN_NAME");

pRecordset->get_Collect(long(index));

10. Add

m_pRecordset->AddNew();

m_pRecordset->PutCollect();

m_pRecordset->Update();

11. Delete

try
{
    // to delete the 2nd
    m_pRecordset->MoveFirst();
    m_pRecordset->Move(1);        
    
    m_pRecordset->Delete(adAffectCurrent);  

    // adAffectCurrent: delete the current one
    m_pRecordset->Update();
}
catch(_com_error *e)
{
    AfxMessageBox(e->ErrorMessage());
}