Solved

MS Access Pass through Query from Sage Line 50

Posted on 2011-09-14
12
795 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 84
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now