Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

MS Access Pass through Query from Sage Line 50

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
databarracks
Asked:
databarracks
  • 7
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
databarracksAuthor Commented:
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
 
databarracksAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
omgangCommented:
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
 
databarracksAuthor Commented:
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
 
omgangCommented:
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
 
databarracksAuthor Commented:
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
 
omgangCommented:
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
 
databarracksAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
databarracksAuthor Commented:
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
 
databarracksAuthor Commented:
Very helpful as always
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now