Find Jobs
Hire Freelancers

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

$10-30 USD

Closed
Posted about 10 years ago

$10-30 USD

Paid on delivery
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 [login to view URL] = 0 Dim rst As [login to view URL] Dim sql As String sql = "SELECT COUNT(*)AS total " _ & "FROM InventorySuppliers INNER JOIN Boyd0315All ON [login to view URL] = [login to view URL] " _ & "WHERE ([login to view URL] = 315) AND ([login to view URL] = 'Y') AND ([login to view URL] = '39%') AND " _ & "(NOT round(([login to view URL] * .61 + 65 + 265)/100,2) = ([login to view URL])) AND (NOT ([login to view URL] = 'E') AND " _ & "NOT ([login to view URL] = 'L') AND NOT ([login to view URL] = 'P'))OR " _ & "([login to view URL] = 315) AND ([login to view URL] = 'Y') AND ([login to view URL] = '39%') " _ & "AND (NOT ([login to view URL] + [login to view URL] + [login to view URL] + [login to view URL] = [login to view URL])) AND (NOT ([login to view URL] = 'E') AND " _ & "NOT ([login to view URL] = 'L') AND NOT ([login to view URL] = 'P'))" Set rst = [login to view URL](sql) With rst If Not .EOF Then GetQueryCount5N = .Fields(0).Value End If .Close End With Set rst = Nothing End Function
Project ID: 5718999

About the project

11 proposals
Remote project
Active 10 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
11 freelancers are bidding on average $28 USD for this job
User Avatar
A proposal has not yet been provided
$25 USD in 2 days
5.0 (17 reviews)
5.5
5.5
User Avatar
A proposal has not yet been provided
$25 USD in 1 day
4.4 (19 reviews)
4.6
4.6
User Avatar
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 Basic,C#. Please provide me the entire code set along with what you want to achieve i will try to come up with the best possible SQL statement which will fasten the extraction of resources., I am eager to contribute my abilities and experience for your required job. Given my extensive training and background, I believe I can help you in getting this task done at best methodology. I can personally assure high quality service and excellent communication can be provided by myself to fulfill your work. I look forward to hear from you. Sincerely, Hisham Haniffa
$25 USD in 1 day
5.0 (3 reviews)
2.3
2.3
User Avatar
Hello. More 20 years programming experience. Several questions, pls: Do you change only round in code? How many time it take before changing? Regards. ---------------------------------------------------------------------------------------------------------------------------------------------------
$25 USD in 1 day
5.0 (1 review)
2.0
2.0
User Avatar
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 prices the best results. If you had interest for my suggestion, please call me. I await for you. Best Regards.
$34 USD in 1 day
5.0 (1 review)
0.6
0.6
User Avatar
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 day
0.0 (0 reviews)
0.0
0.0
User Avatar
Er is nog geen voorstel voorzien
$27 USD in 1 day
0.0 (0 reviews)
0.0
0.0
User Avatar
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 day
0.0 (0 reviews)
0.0
0.0
User Avatar
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 the lack of speed! There are a number of ways around this and I am happy to talk you through them Thanks Kevin Ross
$35 USD in 1 day
0.0 (0 reviews)
0.0
0.0
User Avatar
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. Michail Papageorgiou
$35 USD in 1 day
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED STATES
Boardman, United States
5.0
7
Payment method verified
Member since Nov 27, 2008

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759) & Freelancer Online India Private Limited (CIN U93000HR2011FTC043854)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.