Macro help..

[Sky86]

Hi,

i have set up a Marco system that will search record from Access database.

The user can select the search option and done multisearch (that mean the user can insert up to 65000 data to search from the database). The problem that i facing now is the process in searching the record and display it in excel done very slow.

it display 1 data's result in 1 second. With 65k data, it nearly need about 18 hour .

Do u hav any idea to let the process done faster.

Thanks.

[rory]

We would need to see the code you have to determine which bits can be made faster.

[unmarkedhelicopter]

With an ODBC connection why not do the search with SQL ?
Shouldn't take more than a few seconds.

[Sky86]

Hi,

This is the coding i use.

[vba]
Sheets(PrintSheet).Select
For searchrow1 = Searchrow To 65000
If Main.Cells(searchrow1, 1).Value <> "" Then
sql = "Select * From " & Searchtable & " where part_name <> '' " + Site + car_typesearch + SOption + " '" & Main.Cells(searchrow1, 1).Value & "' ORDER BY Branch_plant"
'MsgBox (sql)
Set rs = conn.Execute(sql)
If rs.EOF Or rs.BOF Then
Main.Cells(cantsearch, 4).Value = Main.Cells(searchrow1, 1).Value
''Cells(1, 1).Value = "abc"
'MsgBox "No Record"
cantsearch = cantsearch + 1
RowRecord = RowRecord - 1
Else
Do While Not rs.EOF
'next page
If RowRecord >= 65006 Then
Searchrow = searchrow1
PageSelect
Else
For CellsRecord = 0 To rs.Fields.Count - 1
Sheets(PrintSheet).Cells(RowRecord, CellsRecord + 6).Value = rs.Fields(CellsRecord).Value
Next
rs.MoveNext
RowRecord = RowRecord + 1
End If

Loop
End If
RowRecord = RowRecord + 1
Else
Exit Function
End If
Next

End Function
[/vba]


My sources is from a Access Format and need to preview it in excel format. So that y so slow.
i try to run in Access Query then export into excel, but if the result is more then 65k the record that more then 65k will not come out.

Thanks

[rory]

Well, I guess there's two things:
1. Running 65,000 searches is going to take some time. I also can't see the realistic use for it, but that's for you to decide.
2. This bit:
[vba]Sheets(PrintSheet).Cells(RowRecord, CellsRecord + 6).Value = rs.Fields(CellsRecord).Value[/vba] won't be helping. Writing to cells one at a time is very slow. You should look at rs.GetRows or rs.GetString and then populate all the result cells in one go.

[Sky86]

Hi Rory,
Thank for the information. i will try on it.

i got an error here.
[VBA] If RowRecord >= 65006 Then
Searchrow = searchrow1
PageSelect
Else
For CellsRecord = 0 To rs.Fields.Count - 1
Sheets(PrintSheet).Cells(RowRecord, CellsRecord + 6).Value = rs.Fields(CellsRecord).Value
Next
rs.MoveNext
RowRecord = RowRecord + 1
End If
[/VBA]

if follow the coding, the record only will go to next sheet when Rowrecord reach 65006.
but the result i got is, the coding go to next page when Rowrecord = 37044. i set Rowrecord as Long.
i cant find where is the problem coming out.

Thank & Regards.
Sky

[rory]

I can't see why that would happen.

[L@ja]

Hali,
first:
you set the screenupdating to false to increase your macro speed.

second:
pls try a pivot table functions.
if you insert a pivot into your sheet, you can set the pivot source to your mdb. and here you can filter also as you want.
if your table field are indexed(wich u would like to filter) then will faster.
(i cant check it, but i thing this will faster like your 65k*ask the mdb)

regards
L@ja

[Sky86]

Hei Rory,
i got the error already.
is on the other function.

L@ja,
Thank for the info.
i will search on the screenupdating's coding.
but for the second, Pivot table.
i dono how it works, anyway thanks.
i will not try the pivot table now, coz the project is near the due date already,
i cant change the coding.


1 question here,
When i export the table in Access is more faster then i do it in excel,
but if the record in table is more then 65k then the record afterthat will not come out.
is it possible when access export the record in many sheets.

Thanks n Regards


thanks.

[rory]

No, you would have to use something like GetRows to limit the number of rows returned, then move to a new sheet and retrieve some more. Can I ask what the point of this is and why you don't just use Access? What are your users actually going to do with these thousands of rows of data?

[L@ja]

Hi Sky

the screenupdating works:
application.screenupdating=false
this will do that excel screen not updated, so will faster.

the pivot, pls try itnot exact, i have only a hungarian office)
menu/data/pivot or pivot diagram
src= other?? (second in the office 2k)
select a MS query, and as you want.
additionally :if you swiched the macro recording you will happy


but as Rory wrote, why you don't use an access, more best to this biggest data managing
regards

[Sky86]

Thank Guys,

i will record the macro for pivot table, then test the coding.

i also let the user know already about the spped of the process.
and also let them know that can use access, but they want to use it in excel, they need to use the excel function.
The user got many data to search (excel format), they want to copy and paste all the search data into the tools i create.

In access, the result will display in table or query format, Right?
if like this, they will not buy the idea.

Anyways, Thanks Guys.