i am doing one project in vb6.0 with ms-access 2003 database.
i want bulk data updatation in one table from excel sheet.
is possible , if possible pls give me a good solution.

my table name : segment transaction its contain more then 20 columns , my updatation for only one column based on another column's data.

this bulk updation data from exlcel sheet.

Please help me.


It's really necessary to do this from VB?

If you had an ACCEESS database and an EXCEL Sheet, I supose that you can open your two files, order by a column (it suposed to be a primary key in these, hun?), then copy and paste the data from the column you want... just simple...

But, if you really need to do this task from VB, I recommend that you open two files via ADO, and manipulate them as a database.

'Make sure you have set the Microsoft ActiveX Data Objects on PROJECT -> REFERENCES menu and created a CONNECTION variable

'Connection string to open a access database

'Connection string to open a Excel 97-2003 datasheet
oConexao.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=YOUR_PATH\YOUREXCELFILE.XLS;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

'Connection string to open a Excel 2007 datasheet
oConexao.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=YOUR_PATH\YOUREXCELFILE.XLSX;Extended Properties='Excel 12.0 Xml;HDR=YES'"

Note that oConexao is my variable. It's type is ADODB.Connection .
The connection to the Access may function to 97-2003 or 2007 databases (I use it frequently).
The connection to the Excel file may vary depending on the version you have, as seen in the example code.
To manipulate the Excel file via ADO, remember that the sheet name becomes the table name, and the columns names becomes the fields names in the ADO architecture.

If you need some connection strings for other file types, please refer to http://www.connectionstrings.com/, this site have a lot of information about connections strings in pre-net and NET applications too.

I hope this be usefull for you.


use two database connection
1 for access
2 for excel
read from excel and write to access.