Can this Excel-Access lookup be speeded up?

1

1scant

I have a rather large Excel file that looks up references in a hug
Access file. The Excel file has about 120,000 rows (2 files of 60,000
and the Access file has about 2.7M records. I have to determine if eac
Ref Doc in column F of the Excel file has a record in the Access file.
This is my first attempt at getting one application to talk t
another.

The problem is, it is pretty slow and I'm hoping someone can point ou
a way to make it much faster. One idea might be to sort the Excel fil
and the Access table on the Ref Doc and do brute force find in a
ever-decreasing size loop. But I'm not savvy enough to tackle tha
right yet.

The code is as follows:

Sub MatchInARMS()

'Application.ScreenUpdating = False
' turning screen updating off optimizes code execution

Dim conADOConnection As New Connection, strConnect As String
Dim strDB, strSQL As String
Dim cmdGetMyData As New Command
Dim rstMyData As Recordset
Dim strRefCell As String
Dim strPrevCell As String
Dim boolPrevStatus As Boolean


'**********************************************

strDB = "C:\Documents and Settings\u00504\Desktop\dc_arms_all.mdb"

'**********************************************

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = " & strDB
conADOConnection.Open strConnect


' loop to look up each Ref Doc in ARMS
' Starting at F2 and continuing down that column of reference items.

Worksheets("dc_drawing_bad_3").Activate
Worksheets("dc_drawing_bad_3").Range("F2").Activate
strPrevCell = "Nothing"
strPrevValue = "Nothing"
boolPrevStatus = False

For i = 1 To 16008

strRefCell = ActiveCell.Value

'If the previous reference is the same as the new ref, don't do
' the time-consuming DB lookup.

If strRefCell = strPrevCell Then
If boolPrevStatus Then
ActiveCell.Offset(0, 4) = "GOOD"
ActiveCell.Offset(0, 5).Value = strPrevValue

Else
ActiveCell.Offset(0, 4) = "BAD"
End If

Else

strPrevCell = strRefCell
strSQL = "Select * From arms Where [dsn]='" & strRefCell & _
"'ORDER BY status"

With cmdGetMyData
Set .ActiveConnection = conADOConnection
.CommandText = strSQL
.CommandType = adCmdText
End With

Set rstMyData = cmdGetMyData.Execute()

If rstMyData.EOF Then
'MsgBox strRefCell & " BAD "
ActiveCell.Offset(0, 4).Value = "BAD"
boolPrevStatus = False
Else
'MsgBox strRefCell & " GOOD "
ActiveCell.Offset(0, 4).Value = "GOOD"
strPrevValue = rstMyData.Fields(4).Value
ActiveCell.Offset(0, 5).Value = strPrevValue
boolPrevStatus = True
End If
rstMyData.Close

End If

ActiveCell.Offset(1, 0).Activate

Next i


'Application.ScreenUpdating = True
conADOConnection.Close

End Su
 
M

MH

Put an index on the lookup field in Access. i.e. if your table names are
Lookup_Key and LookUp_Value then you want an index on the LookUp_Key field
(not the value field).

MH

1scant said:
I have a rather large Excel file that looks up references in a huge
Access file. The Excel file has about 120,000 rows (2 files of 60,000)
and the Access file has about 2.7M records. I have to determine if each
Ref Doc in column F of the Excel file has a record in the Access file.
This is my first attempt at getting one application to talk to
another.

The problem is, it is pretty slow and I'm hoping someone can point out
a way to make it much faster. One idea might be to sort the Excel file
and the Access table on the Ref Doc and do brute force find in an
ever-decreasing size loop. But I'm not savvy enough to tackle that
right yet.

The code is as follows:

Sub MatchInARMS()

'Application.ScreenUpdating = False
' turning screen updating off optimizes code execution

Dim conADOConnection As New Connection, strConnect As String
Dim strDB, strSQL As String
Dim cmdGetMyData As New Command
Dim rstMyData As Recordset
Dim strRefCell As String
Dim strPrevCell As String
Dim boolPrevStatus As Boolean


'**********************************************

strDB = "C:\Documents and Settings\u00504\Desktop\dc_arms_all.mdb"

'**********************************************

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = " & strDB
conADOConnection.Open strConnect


' loop to look up each Ref Doc in ARMS
' Starting at F2 and continuing down that column of reference items.

Worksheets("dc_drawing_bad_3").Activate
Worksheets("dc_drawing_bad_3").Range("F2").Activate
strPrevCell = "Nothing"
strPrevValue = "Nothing"
boolPrevStatus = False

For i = 1 To 16008

strRefCell = ActiveCell.Value

'If the previous reference is the same as the new ref, don't do
' the time-consuming DB lookup.

If strRefCell = strPrevCell Then
If boolPrevStatus Then
ActiveCell.Offset(0, 4) = "GOOD"
ActiveCell.Offset(0, 5).Value = strPrevValue

Else
ActiveCell.Offset(0, 4) = "BAD"
End If

Else

strPrevCell = strRefCell
strSQL = "Select * From arms Where [dsn]='" & strRefCell & _
"'ORDER BY status"

With cmdGetMyData
Set .ActiveConnection = conADOConnection
CommandText = strSQL
CommandType = adCmdText
End With

Set rstMyData = cmdGetMyData.Execute()

If rstMyData.EOF Then
'MsgBox strRefCell & " BAD "
ActiveCell.Offset(0, 4).Value = "BAD"
boolPrevStatus = False
Else
'MsgBox strRefCell & " GOOD "
ActiveCell.Offset(0, 4).Value = "GOOD"
strPrevValue = rstMyData.Fields(4).Value
ActiveCell.Offset(0, 5).Value = strPrevValue
boolPrevStatus = True
End If
rstMyData.Close

End If

ActiveCell.Offset(1, 0).Activate

Next i


'Application.ScreenUpdating = True
conADOConnection.Close

End Sub
Click to expand...
 
1

1scant

Please, excuse my too-quick response, MH. Of course, my Access table
has a primary key and that was all I was aware of that could be done,
prior to doing a little more research on your suggestion. Were you
suggesting the use of the Create Index statement? I was unaware of
this statement and will have to figure out how it is used.

Thanks.
 
1

1scant

I feel stupid. I simply went into my table and set the index propert
on the field that I search on, and the speed is ten-fold better.

Thanks, again
 
1

1scant

Is it possible to use a wildcard in an ADO SQL Select? I have tried the
following statement - it does not give an error, but it doesn't work,
either.

strMyCriteria = _
"((dsn='" & strRefCell & "') AND (dtc LIKE '*VEND'))"
 
M

MH

If you are using ADO the wildcards are different to the ones you use in
native Access. The following will work:
strMyCriteria = _
"((dsn='" & strRefCell & "') AND (dtc LIKE '%VEND'))"
Click to expand...

The wildcards you need to change in your queries are:

* = %
? = _

MH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workbook_Open vs Auto_Open 4
Loop Repeats - PLS HELP 2
Application.Quit Run-Time Error 1004 6
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
PLEASE help ... 3
accessing workbook fails 4
A workbook of macros which runs on external workbooks. 6
Formatting with Macros 3

Top