Copy Excel data into Access database using ADO for Visual C++ 
Author Message
 Copy Excel data into Access database using ADO for Visual C++

Hiz,

How to copy Excel data to Access database using ADO for
Visual C++???

I'm only able to extract Excel data.
I'm only able to insert data to Access database but it has
to be hardcode.

Eg.
/*******************************************************/

        //Initialize the COM environment
        ::CoInitialize(NULL);

        //_ConnectionPtr is the connection interface.
        _ConnectionPtr MyDb;
        MyDb.CreateInstance(__uuidof(Connection));
        MyDb->Open("DSN=Access","","",-1);

        //_RecordsetPtr is the recordset interface.
        _RecordsetPtr MySet;

        MySet.CreateInstance(__uuidof(Recordset));
        MySet->Open("INSERT INTO Details (Name) VALUES
('515155')",
        MyDb.GetInterfacePtr
(),adOpenDynamic,adLockOptimistic,adCmdText);

        //Shut down the COM environment
        ::CoUninitialize();

/*******************************************************/



Sat, 29 Jan 2005 10:03:20 GMT  
 Copy Excel data into Access database using ADO for Visual C++
Hiz,

How to copy Excel data to Access database using ADO for
Visual C++???

I'm only able to extract Excel data.
I'm only able to insert data to Access database but it has
to be hardcode.

Eg.
/*******************************************************/

        //Initialize the COM environment
        ::CoInitialize(NULL);

        //_ConnectionPtr is the connection interface.
        _ConnectionPtr MyDb;
        MyDb.CreateInstance(__uuidof(Connection));
        MyDb->Open("DSN=Access","","",-1);

        //_RecordsetPtr is the recordset interface.
        _RecordsetPtr MySet;

        MySet.CreateInstance(__uuidof(Recordset));
        MySet->Open("INSERT INTO Details (Name) VALUES
('515155')",
        MyDb.GetInterfacePtr
(),adOpenDynamic,adLockOptimistic,adCmdText);

        //Shut down the COM environment
        ::CoUninitialize();

/*******************************************************/



Sat, 29 Jan 2005 10:10:15 GMT  
 Copy Excel data into Access database using ADO for Visual C++
Hiz,

How to copy Excel data to Access database using ADO for
Visual C++???

I'm only able to extract Excel data.
I'm only able to insert data to Access database but it has
to be hardcode.

Eg.
/*******************************************************/

        //Initialize the COM environment
        ::CoInitialize(NULL);

        //_ConnectionPtr is the connection interface.
        _ConnectionPtr MyDb;
        MyDb.CreateInstance(__uuidof(Connection));
        MyDb->Open("DSN=Access","","",-1);

        //_RecordsetPtr is the recordset interface.
        _RecordsetPtr MySet;

        MySet.CreateInstance(__uuidof(Recordset));
        MySet->Open("INSERT INTO Details (Name) VALUES
('515155')",
        MyDb.GetInterfacePtr
(),adOpenDynamic,adLockOptimistic,adCmdText);

        //Shut down the COM environment
        ::CoUninitialize();

/*******************************************************/



Sat, 29 Jan 2005 10:09:52 GMT  
 Copy Excel data into Access database using ADO for Visual C++
Warz,

here is the ADO code in VBA for Access.  the translation to VC++ should be
straight forward.  let me know if you need help.

regards
roy fine

/* ************************************ */
Public Sub GetExcelData()
Dim val1 As Variant
Dim oConnExcel As ADODB.Connection
Dim rsExcel As ADODB.Recordset
Set oConnExcel = New ADODB.Connection
Set rsExcel = New ADODB.Recordset
Dim reccnt As Variant

Dim rsJet As ADODB.Recordset
Set rsJet = New ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandText = "delete * from tblFromExcel"
cmd.ActiveConnection = CurrentProject.Connection
cmd.Execute reccnt, , adCmdText

rsJet.ActiveConnection = CurrentProject.Connection
rsJet.Open "select field1,field2,field3,field4,field5,field6 From
tblFromExcel",
, _
        adOpenForwardOnly, adLockOptimistic, adCmdText

oConnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=C:\Somewhere\xcel_data.xls;Extended Properties=""Excel
8.0;HDR=Yes"""

rsExcel.ActiveConnection = oConnExcel
rsExcel.Open "Select * from xcel_data", , adOpenForwardOnly, adLockReadOnly,
adCmdText
While Not rsExcel.EOF
  rsJet.AddNew
  rsJet.Fields.Item(0).Value = rsExcel.Fields.Item(0).Value
  rsJet.Fields.Item(1).Value = rsExcel.Fields.Item(1).Value
  rsJet.Fields.Item(2).Value = rsExcel.Fields.Item(2).Value
  rsJet.Fields.Item(3).Value = rsExcel.Fields.Item(3).Value
  rsJet.Fields.Item(4).Value = rsExcel.Fields.Item(4).Value
  rsJet.Fields.Item(5).Value = rsExcel.Fields.Item(5).Value
  rsJet.Update
  rsExcel.MoveNext
  Wend

rsJet.Close
Set rsJet = Nothing

rsExcel.Close
Set rsExcel = Nothing

oConnExcel.Close
Set oConnExcel = Nothing
End Sub
/* ************************************* */


Quote:
> Hiz,

> How to copy Excel data to Access database using ADO for
> Visual C++???

> I'm only able to extract Excel data.
> I'm only able to insert data to Access database but it has
> to be hardcode.

> Eg.
> /*******************************************************/

> //Initialize the COM environment
> ::CoInitialize(NULL);

> //_ConnectionPtr is the connection interface.
> _ConnectionPtr MyDb;
> MyDb.CreateInstance(__uuidof(Connection));
> MyDb->Open("DSN=Access","","",-1);

> //_RecordsetPtr is the recordset interface.
> _RecordsetPtr MySet;

> MySet.CreateInstance(__uuidof(Recordset));
> MySet->Open("INSERT INTO Details (Name) VALUES
> ('515155')",
> MyDb.GetInterfacePtr
> (),adOpenDynamic,adLockOptimistic,adCmdText);

> //Shut down the COM environment
> ::CoUninitialize();

> /*******************************************************/



Sat, 29 Jan 2005 21:00:43 GMT  
 Copy Excel data into Access database using ADO for Visual C++
You can use other method to import data from Excel, such as DTS in SQL
Server. For more details, please refer to:
Title: HOW TO: Import Data into SQL Server from Excel
URL: http://support.microsoft.com/support/kb/articles/Q321/6/86.asp


Mon, 31 Jan 2005 15:17:58 GMT  
 Copy Excel data into Access database using ADO for Visual C++
The following example may be of assistance:
Title: Q278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and
Write Data in Excel Workbooks
URL: http://support.microsoft.com/support/kb/articles/Q278/9/73.asp


Mon, 31 Jan 2005 16:54:45 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Accessing an MS-Access database file using Visual C++ 4.0

2. Accessing cells from Excel file using Visual C++

3. Database settings -- Visual C++ used with Visual FoxPro database

4. Using Access Database in Visual C++ 6.0

5. Q: How to access an Access 97 database in Visual C++ 5.0

6. MFC Visual C++ 5.0 and Excel, Access binding

7. using VC to access database with ADO.

8. Database Access using ADO

9. Accessing Excel Worksheet Names from Visual C++

10. Accessing Excel Worksheet Names from Visual C++

11. How to bind ADO data bound controls in Visual C++

12. Using ADO for SQL Server data access

 

 
Powered by phpBB® Forum Software