Performance Question

[Frenchy646]

Hello guys,

Right, so, I have a program which is taking information from Excel rows, and putting them into an Access database, using VBA.
(before you ask, the import function will not work for my requirements ).

I am using a lot of SQL queries to do my operations ( getting information from the table, updating rows, and inputting new ones ).
I end up having anything from 6 to 14 queries per row on the excel file.
Hence, running the program takes time ( around 20 mins + on the last file I ran it on ).

Is there any way to increase the performance of SQL queries ?
Also, is there any way of implementing a progress bar, showing how much time is left (or simply that queries are still happening ), as I never know if Access has just crashed or not.

If you can't answer any of these questions, if you could reply to say so ?
I would appreciate to know that I'm not the only one finding this question baffling :)

[OBP]

I am surprised they take so long to run, that sounds like more than 500,000 records.
Are you using a Form to run this VBA?
If so you can set the Form's Status bar text like this
Forms("frmImport").cmdGetSource.StatusBarText = "Opening Excel Selected Workbook."

So you could put that between queries.

[OBP]

I notice from your other post that you are running VBA generated SQL, why aren't you running real queries, which should be a lot quicker.

[Jam]

Hi Frenchy,

Well it's quite difficult to answer just like that. In fact, you may look on both sides (Access and Excel).
In Access you should check for indexing
You may use DAO rather than ADO with Workspaces +BeginTrans/CommitTrans
Open your Connection once then do all your queries before closing the connection
If your file is on a LAN directory may be you should think to move it to another place
In Excel, in general the slowest part is moving from cells to cells. So you may investigate that. Other thing I may suggest, is put all the data in a worksheet then pick-up the data with formulas from your cells rather than write data to cells (don't know if your doing it that way though :question: ).
Disable Calculation and Screenupdating may help too

HTH

Jam

[Frenchy646]

First of all, thank you all for your replies.


I am surprised they take so long to run, that sounds like more than 500,000 records.
Are you using a Form to run this VBA?
If so you can set the Form's Status bar text like this
Forms("frmImport").cmdGetSource.StatusBarText = "Opening Excel Selected Workbook."


Well, basically, for the moment, I am just doing this by running a VBA module. That is, for the moment.
What I am really wanting to do is (and this is a work in progress ), make a form with different options for the user, one of which would be this import.
As for the 500,000 records thing, the file I used it on which I talked about had 1689 rows (Excel).
Once I get the cell details for the row, I do a select query to check if data exists in my database. If not, I do an insert query. I then do another select query just to find the autonumber that was generated (maybe there is a faster way than doing a query with 11 fields to find an autonumber ? )
If it does already contain the record however, I then go to the last 10 columns of the excel file, iterate through each column, parsing the string in the header to convert it to a date. Using this date and the autonumber, I do a select query to find out if the finance data for this date with this autonumber exists in the database, if yes, do nothing.
If it doesn't exist, do an insert query, then do an update query in the total table.
Sorry if this is hard to understand, tried my best to explain it without drowning you in code. I should note that, the excel file is not generated by me, it is made by finance, and they have decided that they want to put one column per month for each instance. Also, there is no set number of months which they give me. They can give me all the months for the past two years with modifications, or they can just give me two.


I notice from your other post that you are running VBA generated SQL, why aren't you running real queries, which should be a lot quicker.
I am moving post in a few months, and have been tasked, for office efficiency, to create this VBA program for the user(s) to :
-Simply import the Excel file, to their required needs ( the Access import function, as I said, not suitable for these requirements ).
-Permit them to add modifications.
-Fully automate the fincancial calculations received from finance in a less-than-desirable format.

Also, people in the office aren't really...computer savvy. I just have to give them a button to press to do the work.
Interesting that pure SQL queries are more efficient than VBA generated. Is this because in VBA, the String has to be decoded ?
Is there a way for me to run "real queries" from VBA, considering it uses data retrieved in VBA from the Excel file ?


In Access you should check for indexing
You may use DAO rather than ADO with Workspaces +BeginTrans/CommitTrans
Open your Connection once then do all your queries before closing the connection
Interesting, will check it out.


In Excel, in general the slowest part is moving from cells to cells. So you may investigate that.
When you say "moving" from cell to cell, do you mean accessing the information in a column of a row, then, later on, accessing the next column ?


Other thing I may suggest, is put all the data in a worksheet then pick-up the data with formulas from your cells rather than write data to cells (don't know if your doing it that way though :question: ).
Apart from the month columns, 11 of the columns are strings, which can't be calculated and have to be entered ( by finance thank god ) by hand. And they are even sometimes null or empty strings !
I don't have much love for our financial department...

Thank you very much again OBP and Jam for replying so quickly !

[OBP]

I think this would run considerably faster if you just imported the excel Sheet in to Access and worked with it there.
Access Queries, especially coupled with VBA Function Modules are very fast and very powerful.
Real queries in the Access database can be run by VBA with a simple docmd statement.
Can you post a Dummy example of an Excel worksheet?

I am not sure how you are going to meet your "Task" of automating the analysis when you have to cope with
"it is made by finance, and they have decided that they want to put one column per month for each instance. Also, there is no set number of months which they give me. They can give me all the months for the past two years with modifications, or they can just give me two."

[Frenchy646]

I am not sure how you are going to meet your "Task" of automating the analysis when you have to cope with
"it is made by finance, and they have decided that they want to put one column per month for each instance. Also, there is no set number of months which they give me. They can give me all the months for the past two years with modifications, or they can just give me two."

Well, with regards to that, it is done.
I have successfully fully automated the process, with the exception of finding out how to enable the user to browse for the file to import after clicking on a button in the form (tried a simple example on the WEB, and got some error to do with library or licensing or something or rather not being available).

It's automated, it's just slow. On the other hand, it's understandable when you think my module is going through every row of the excel spreadsheet, reformatting strings, turning all the financial columns into relevant entries in the tb, making sure there are no double entries, etc.
Guess they'll just have to deal with it.

[OBP]

If the error was a Library reference you set those in the VBA Editor>Main Menu>Tools>References.

I can also provide browser code, but it also needs references setting as well.

[hansup]

Once I get the cell details for the row, I do a select query to check if data exists in my database. If not, I do an insert query. I then do another select query just to find the autonumber that was generated (maybe there is a faster way than doing a query with 11 fields to find an autonumber ? )
You could use SELECT @@IDENTITY after the insert to determine the last autonumber value.

In the sample below, MyTable includes an autonumber field named id. After INSERT, SELECT @@IDENTITY returns a recordset with a single row and single column, which contains the last id value.

Public Sub SelectIdentity()
Dim strSql As String
Dim db As DAO.Database
Dim lngId As Long
Dim strMsg As String

On Error GoTo ErrorHandler

strSql = "INSERT INTO MyTable (some_text) VALUES ('foo');"
Set db = CurrentDb
CurrentDb.Execute strSql, dbFailOnError
lngId = db.OpenRecordset("SELECT @@IDENTITY")(0)
Debug.Print lngId
Set db = Nothing

ExitHere:
On Error GoTo 0
Exit Sub

ErrorHandler:
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure SelectIdentity"
MsgBox strMsg
GoTo ExitHere
End Sub
db.OpenRecordset("SELECT @@IDENTITY")(0) is just a shortcut to refer to the first column of the recordset.