Geschlossen

VBA Count(*) slow in access, SQL pass thru

I've got the following code in my access VB code. It worked fine before I added the round() sectin of the code to round the equasion off to 2 digits.

I know the code is correct, i ran it in SQL, it takes 7 seconds to run.

I've been waiting for over an hour through access though..

FTY table 1, which is the one rounding, and adding, has about 14,000,000 records, table 2, which its being compared to, has about 100,000,000 records.

Public Function GetQueryCount5N() As Long

[url removed, login to view] = 0

Dim rst As [url removed, login to view]

Dim sql As String

sql = "SELECT COUNT(*)AS total " _

& "FROM InventorySuppliers INNER JOIN Boyd0315All ON [url removed, login to view] = [url removed, login to view] " _

& "WHERE ([url removed, login to view] = 315) AND ([url removed, login to view] = 'Y') AND ([url removed, login to view] = '39%') AND " _

& "(NOT round(([url removed, login to view] * .61 + 65 + 265)/100,2) = ([url removed, login to view])) AND (NOT ([url removed, login to view] = 'E') AND " _

& "NOT ([url removed, login to view] = 'L') AND NOT ([url removed, login to view] = 'P'))OR " _

& "([url removed, login to view] = 315) AND ([url removed, login to view] = 'Y') AND ([url removed, login to view] = '39%') " _

& "AND (NOT ([url removed, login to view] + [url removed, login to view] + [url removed, login to view] + [url removed, login to view] = [url removed, login to view])) AND (NOT ([url removed, login to view] = 'E') AND " _

& "NOT ([url removed, login to view] = 'L') AND NOT ([url removed, login to view] = 'P'))"

Set rst = [url removed, login to view](sql)

With rst

If Not .EOF Then

GetQueryCount5N = .Fields(0).Value

End If

.Close

End With

Set rst = Nothing

End Function

Fähigkeiten: Visual Basic

Mehr darüber sql price, p sql, end vba, vba e, sql l, fty, eof, access sql, recordset, vba pass, access vba pass, run access function sql, vba function, sql select select, sql select, Vba sql, openrecordset, access sql function, vba string, access vba recordset sql, DAO, ean code, code access vba, access vba code, vba code access

Über den Arbeitgeber:
( 7 Bewertungen ) Boardman, United States

Projekt-ID: #5718999

12 Freelancer bieten im Durchschnitt $30 für diesen Job

NourElDeen

A proposal has not yet been provided

$25 USD in 2 Tagen
(8 Bewertungen)
5.0
AndroidAll

Dear sir, I'm interesting in your job. I have much experience in the MS Excel and VBA. I can do this job. If you want, I can develop a demo within a short time. Regards. PS. In order to show my ability, I c Mehr

$50 USD in 1 Tag
(18 Bewertungen)
4.3
dpune

A proposal has not yet been provided

$25 USD in 1 Tag
(19 Bewertungen)
4.7
hishamhaniffa

Dear Sir, I am writing to inquire about job opportunity you have requested. I am interested in working for you and to complete the given task successfully and as you require. As a skilled labor force in Visual Basi Mehr

$25 USD in 1 Tag
(3 Bewertungen)
2.3
zkutch

Hello. More 20 years programming experience. Several questions, pls: Do you change only round in code? How many time it take before changing? Regards. ------------------------------------------------------------- Mehr

$25 USD in 1 Tag
(1 Bewertung)
2.0
TypingMachine

Dear Sir, I'm quite interested by your project. I am programmer with sincere and capable. I have developed many project. Particular, I have rich experience in Access VBA. I can provide you the best cheap Mehr

$34 USD in 1 Tag
(1 Bewertung)
0.6
jjmendozag2010

The problem is the order in your sql expression. If you choose my proposal, I can solve in one day, I only need some data (100 records) from each table to fix the sql and test [url removed, login to view] be the solution is to make a dif Mehr

$25 USD in 2 Tagen
(0 Bewertungen)
0.0
theps

I usually use instead of round () calculating the positive difference between and its comparison with 0.001. It works more reliably. Try.

$25 USD in 1 Tag
(0 Bewertungen)
0.0
mdierc

Er is nog geen voorstel voorzien

$27 USD in 1 Tag
(0 Bewertungen)
0.0
avaneeshshukla

i have more than 15 years of experience in vb projects and handled many back-end procedures. i am 100% sure i would provide you the best result.

$25 USD in 1 Tag
(0 Bewertungen)
0.0
kevinross100

Hi, This is quite a simple problem and is caused by the access thinking it has to run the round part of the query locally. Because of that the SQL server is sending all the records to access for it to process hence Mehr

$35 USD in 1 Tag
(0 Bewertungen)
0.0
papageorgis

Hello Mr pghquest, I've been programming (stand-alone and web) for almost 10 years now, even though I am new in freelancer.com. I am at your disposal for any clarifications. Thank you in advance for your time. Mehr

$35 USD in 1 Tag
(0 Bewertungen)
0.0