VBA, MS Project and DB

[kkarnez]

Hello,
I use MS Project and I need to export data to an Oracle database. The problem is that MS Project always overwrites the existing tables in the database. So I wrote a VBA module to export the data. The problem now is that this program is extremely slow. Imagine that it exports 1 row of MS Project in almost 1 sec. (And I have thaousands of them!!)

I don't know many things about ado. Can anyone give me a small example of how can I write data to a database FAST?

PS (I use a FOR loop to read each row of MS Project and write it to the database)

thanks...

 

[Ruairi]

If you are using ADO for your database connection you should be able to do it faster using a batch update. Open a recordset with all of the columns you want to add data to from the oracle table (if a column doesn't accept nulls and doesnt have a default you have to explicitly give it a value). Open the recordset with a where clause such that it will select no rows (where IdentityColumn = -1 or something similar). Then add each row to the recordset and do a batch update.

Dim rs As New ADODB.Recordset 

'Open your recordset here, make sure you give it a where clause such that no rpws are returned 

For Count = 1 TO MaxValue 
   rs.AddNew 
   rs("field1") = MSProjectField1Data 
   rs("field2") = MSProjectField2Data 
   'etc.. 
Next Count 

rs.BatchUpdate

Another Option (maybe)
I am working on a total rewrite of a log tracking system in VB with SQL server. I often have to import over 1GB of data to the SQL DB from the DOS system. The fastest way i have found to do this is to export the data to text files, then use BCP (a bulk copy program that comes with SQL server) to import the text files. This may be an option if you have access to a bulk copy program for oracle that recognizes text files. In any case, invoking all the overhead of a client/server conversation for each row is not a good idea.
HTH Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[kkarnez]

Thank you for your help, but how can I open the recordset there? I use:

'Rs1.Open Source, Connect'

where 'Source' is a string containing the insert sql statement...

 

[MarrowGenx]

This is the code I use in Access to open a recordset and database. Hopefully it will help you.

Dim rs1 as new dao.recordset
dim dbsa as database

set dbsa as currentdb
Set rs1 = dbsa.OpenRecordset("tblName", dbOpenDynaset)