Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access Pass through Query from Sage Line 50

Posted on 2011-09-14
12
Medium Priority
?
821 Views
Last Modified: 2012-08-13
Hi there,

I have managed to borrow a piece of code from the internet for running a pass through query via VBA as below. As I have read that a pass thorugh query would be faster than using a query from within the [INVOICE] linked table I would like to use the 'temporary' queries results in a pop up form of which has criteria. However this code needs to be run regularly with different criteria but once the querydef has been created it say that 'this query already exists'.

How can I write the code so it allows to create a temporary query or overwrite an existing saved query. I really am trying to shave off the current time it takes to load the results from the Sage database but I am sure you all know that Sage isn't the fastest when querying from it. Any tips on how I can speed up my query from Sage would be much appreciated.

Thank you in advance
Dim strCompany As String
Dim strYearStart As String

strCompany = Me.Combo3
strYearStart = "'2011-1-1'"

Dim dabs As DAO.Database
Dim qdef As DAO.QueryDef
Dim recs As DAO.Recordset
Set dabs = CurrentDb
Set qdef = dabs.CreateQueryDef("qry3Pass")
qdef.Connect = "ODBC;DSN=SageLine50v17;UID=***;PWD=***;"
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & "AND INVOICE.INVOICE_DATE > '2011-1-1'"

qdef.ReturnsRecords = True
'qdef.Execute
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)



Me.frm2Pass.Form.RecordSource = "qry3Pass"

Open in new window

0
Comment
Question by:databarracks
  • 7
  • 3
  • 2
12 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 36535107
Don't use CreateQueryDef - just build the query and then set the SQL as needed:
Dim qdef As DAO.QueryDef
Set qdef = Currentdb.QueryDefs("qry3Pass")
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & "AND INVOICE.INVOICE_DATE > '2011-1-1'"

Open in new window

That should get you where you need to be.
0
 

Author Comment

by:databarracks
ID: 36535210
You are an absolute legend and it works really well. I noticed that once the query has been run once it loads much faster thereafter, but if the form has been inactive for a while it seems to take a bit longer. Do you have any other tips as how I could keep my load times consistent and failr quick. Ideally I am looking for a 3 to 5 sec waiting time?
0
 

Author Comment

by:databarracks
ID: 36535259
Looking at your code again, this means that the query is actually working off the linked table that I have established and therefore isn't a pass thorugh query in essence or am I wrong?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 28

Expert Comment

by:omgang
ID: 36536206
databarracks, here's the SQL for a pass-through query I have in Access to Sage Mas90.  Note the specification of the SOTAMAS90 DSN

SELECT AP1_VendorMaster.Division, AP1_VendorMaster.VendorNumber, AP1_VendorMaster.VendorName
FROM AP1_VendorMaster IN '' [ODBC;DSN=SOTAMAS90;UID=XXX|COM;PWD=password;Directory=T:\Best\Mas90;CacheSize=4;DirtyReads=1;BurstMode=1;SERVER=NotTheServer];

Where UID=XXX|COM is user ID pipe Company Code

The Sage Mas90 installation created the SOTAMAS90 DSN.  I'm thinking you should have something similar for Line50?

OM Gang
0
 

Author Comment

by:databarracks
ID: 36536258
Hi there,

I am not sure if this was created upon Line 50 installation but I have the below code:

ODBC;DSN=SageLine50v17;UID=XXXr;PWD=;

I am guessing that all I need to do is add the rest of your code from ";Directory" onwards with the appropriate path to my ACCDATA file. Would there be any performance benefits using this approach?
0
 
LVL 28

Expert Comment

by:omgang
ID: 36536315
By definition a pass-though query uses the database engine of the host to run the SQL, e.g. the query will be executed by Line50 and not Access.  I saw a performance increase using pass-through queries instaed of linked tables to Mas90.

A quick Google for 'Sage Line 50 pass-through query' resulted in
http://www.about-access-databases.com/pass-through-query.html

Also, look at this PAQ describing how to find/determine the connect string to use in your Pass-Through query
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26179598.html

OM Gang
0
 

Author Comment

by:databarracks
ID: 36536433
I have determined the connection string, which was the one that I had sent a short while ago and have already created a pass through as per the google approach.

My current code as per LSMConsulting (ID:36535107) works fine but is not fast enough. THe code below is a slight change of LSMConsultings's and is more like yours but I keep getting the error 'Invalid table specification (#0)' ??
Dim strCompany As String
Dim strYearStart As String

strCompany = "'" & Me.Combo3 & "'"
strYearStart = "'2011-1-1'"

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qry3Pass")
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "IN [ODBC;DSN=SageLine50v17;UID=manager;PWD=;]" & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & " AND INVOICE.INVOICE_DATE > '2011-1-1' "

qdef.ReturnsRecords = True
'qdef.Execute
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)



Me.frm2Pass.Form.RecordSource = "qry3Pass"

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 36536513
Is qry3Pass already a pass-though query to Line50?  If so, use Debug.Print qdef.SQL to get the current SQL to compare to.

Here's what I'd do:  create a pass-though query to the Line50 table using a valid company and valid date as criteria.  Make sure that executes and returns records.  Copy the SQL statement from that query and use it to build the SQL statement for your VBA routine.

The error seems to indicate an invalid table name, e.g. INVOICE is not a table in Line50.  Is it possible the linked table name in your Access db is different from the actual table name in Line50.

OM Gang
0
 

Author Comment

by:databarracks
ID: 36536606
Yes qry3Pass is already a pass-through query and works fine using a valid company and date. The table name is fine and is correct. The error comes about when I try and use the "IN" condition within the SQL. Ultimately if you don't believe that this route would improve performance considerably then, I will stick to the current approach that is working and takes about  max 10 seconds to load. The working code is below and speed is decent. It takes anout 10 seconds to return the results upon first run of query but for any concequential re-runs therafter despite the change in criteria take about 3.

All I want is to improve the initial 10 seconds to about 5 or 6. If there is no benefit in running the pass through from vba directly as opposed to the method below, then I will just leave it at that.

THank you again for your patience.
Dim strCompany As String
Dim strYearStart As String

strCompany = "'" & [Forms]![frmMain]![sfrCustomersMain].[Form]![sfrCustomerList].[Form].[account_ref] & "'"
strYearStart = Me.txtYears.value

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qry3Pass")
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_OR_CREDIT, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & " AND INVOICE.INVOICE_DATE " & strYearStart & ""

qdef.ReturnsRecords = True
'qdef.Execute
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)



Me.RecordSource = "qry3Pass"

Open in new window

0
 
LVL 85
ID: 36536759
The IN keyword can be used, but if you're working with linked tables there's little reason to use that, and it can be somewhat of a maintenance headache.

I don't see how you could reduce the load time of the query, unless you fired off that query well before it's needed (which would allow Access to open and pool/maintain the connection). You could do this in a startup form when the app starts, if that's appropriate, but honestly I'd just instruct the users that there will be a bit of a delay, or show a Label or something on the form that says "Please wait ... connecting to database" or something like that.
0
 

Author Comment

by:databarracks
ID: 36536864
I could give that a go by opening the connection before it is used but maybe that will be too similar to a linked table meaning that the connection would be constant from the time the user starts using it.  I think I will do what you said and just let the user name that this may take a few seconds before it loads.

THank you for your help as always, it is much appreciated.
0
 

Author Closing Comment

by:databarracks
ID: 36536876
Very helpful as always
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question