Friday, August 3, 2007

Getting values from Databases quickly.

When you request some values from databases in excel, it probably takes few secs. For example, when you get values from Microsoft query section, it takes lesser time as compared to the procedure when you create a connection and enters the values record by record. Its always a better option to fetch the complete table and insert the whole table in excel. If your database is huge, this technique works really well. See the following code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Your Oracle DSN;DBQ=Your database Service;uid=your account;pwd=your password;DriverId=25;FIL=Oracle;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("$A$8")).QueryTable
.CommandText = qur <-----(write your query like select * from something)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

The above code fetches the data from oracle and enters in the active excel sheet. It will return the table object based on the query you will give in the code, and insert the results in tabular format in no time. If you compare the process with the procedure of connecting and reading the rows one by one through recordset....its going to take 20 times longer than it should usually take!


No comments: