How to use VS to manipulate Access

1. Import ADO Class:

1 #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:

1 _ConnectionPtr connection;
2 _RecordsetPtr recordset;
3 //Connect
4 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

1 connection->put_ConnectionTimeout(long(5));

6. Open result set

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

7. Close result set

1 m_pRecordset->Close();

8. Iterate result set

 1 while(!m_pRecordset->adoEOF)
 2 {
 3     var = m_pRecordset->GetCollect("Name");
 4     if(var.vt != VT_NULL)
 5         strName = (LPCSTR)_bstr_t(var);
 6     var = m_pRecordset->GetCollect("Age");
 7     if(var.vt != VT_NULL)
 8         strAge = (LPCSTR)_bstr_t(var);
 9     m_AccessList.AddString( strName + " --> "+strAge );
10     m_pRecordset->MoveNext();
11 }
12

9. Get Field value

1 m_pRecordset->GetCollect("Name");
2 
3 m_pRecordset->GetCollect(_variant_t(long(0));
4 
5 pRecordset->get_Collect("COLUMN_NAME");
6 
7 pRecordset->get_Collect(long(index));

10. Add

1 m_pRecordset->AddNew();
2 
3 m_pRecordset->PutCollect();
4 
5 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());
}