Solved

WSH error 80040E14 attempting to run an MS Access Query

Posted on 2004-08-09
9
1,030 Views
Last Modified: 2012-08-13
I made a query in MS Access 97 that within Access works just fine.

When I tried to convert the query to run within a VBS script using this driver "Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files
I get a syntax error.  I assumed that my understanding of the limitations of the provider was inadequate and so resorted to attempting to run my access query directly from the script using "Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files as the driver.

Alas, the VBS still kicks out an error 80040E14 Invalid SQL statement; expected DELETE, INSERT,PROCEDUER, SELECT or UPDATE.

Now, I have nowhere to go.

Can anyone put me out of my misery?

Here is the SQL - that works fine in ACCESS!
Select p.EmplRef, VhcCntrId, AdjDays, AdjLse, AdjInitial, AdjTerm,  LseRate, p.ChrgFrom, EPP, PPP, ChrgTo, OnRoadDat, OffRoadDat, RegId,  TrnsType,   PayPerd, p.CmpyCod, p.PlanCod, p.DivnCod, c.Divncod as DivGrp, InsrAdj,
InsrCost, RunNbr, RunDat, RunTim, PPPNbr, EPPNBR, HECosts,              EmplId, EmplSnam, EmplInits, Sono, EmplFnam, p.Brand, c.cnctemail
FROM  TPayroll AS p,  cnct AS  c
WHERE ((p.PayPerd = c.Payroll) or c.Payroll = 'MW')                and      
              p.cmpyCod = c.cmpyCod                                         and      
              p.planCod = c.planCod                                            and      
              p.DivnCod = c.DivnCod                                           and      
              p.Brand    = c.Brand  
UNION SELECT
              p.EmplRef, VhcCntrId, AdjDays,  AdjLse, AdjInitial, AdjTerm,         LseRate,  p.ChrgFrom, EPP, PPP, ChrgTo, OnRoadDat, OffRoadDat,
              RegId,  TrnsType, PayPerd, p.CmpyCod, p.PlanCod,               p.DivnCod, c.Divncod as DivGrp, InsrAdj, InsrCost, RunNbr, RunDat, RunTim,
              PPPNbr, EPPNBR, HECosts, EmplId, EmplSnam, EmplInits, Sono,
              EmplFnam, p.Brand, c.cnctemail
FROM   TPayroll AS p,
           cnct AS  c
WHERE ((p.PayPerd = c.Payroll) or c.Payroll = "MW") and      
              p.cmpyCod = c.cmpyCod                           and      
              c.planCod  = P.PLANCOD                           and      
              C.DivnCod = "*"                                        and      
              p.Brand    = c.Brand                                  and
NOT EXISTS
(  SELECT REGID FROM TPAYROLL AS P1, CNCT AS C1
    WHERE P1.REGID = P.REGID AND
    ((p1.PayPerd = c1.Payroll) or c1.Payroll = 'MW')    and      
      p1.cmpyCod = c1.cmpyCod    and      
      p1.planCod = c1.planCod       and      
      p1.DivnCod = c1.DivnCod      and      
      p1.Brand    = c1.Brand   )
ORDER BY p.CmpyCod, p.DivnCod, p.EmplRef, p.OnRoadDat, p.TrnsType;

Tables are DBase IIIm linked into an Access97 DB, Field Names ending 'Dat' are DBase Date fields.  The query in Access is called QryPayrollLive
Any help would be greatly appreciated.

The vbs bit of code looks like this:
Con.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; Dbq=i:\data\payroll\payroll.mdb"
Con.Open
rs.open "QryPayrollLive", Con


Thanks
0
Comment
Question by:atiffany
  • 4
  • 3
  • 2
9 Comments
 
LVL 3

Accepted Solution

by:
Julian_C earned 250 total points
ID: 11751818
The problem is that you are using attached tables. As far as I know this works fine in Access because you run the Access application and it is this application process that acts as the bridge to the dbase tables to proxy them through so you can query them as attached tables

So, when you access the MDB through ODBC (or in this case OLEDB?) it is just a driver that understands te makup of MDB files. It can read data that is stored in them. It is not running the MS Access process that is capable of presenting the DBase files as Access tables via the attachment process therefore it can't make the jump through to the Dbase files.

What can you do? Well I can think of a couple of ideas. Either access the DBase files through ODBC rather than the access driver and cut out the middle man. If you used ADO.NET with ODBC driver you could construct and Dataset with all the dbase files as tables with joins and all (I think?) OR you could import the data in the Access DB instead of attaching it. it would then be there for you to run queries against. Or perhaps somke really nasty use of the access runtime ;-)

Cheers
Julian
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11752186
I agree with JulianC, had the same issue and the same solutions as him.
You could also workaround  it by not opening Access via a query, but really running access by running a macro of the database.
Hope this helps
0
 

Author Comment

by:atiffany
ID: 11752711
Thanks, guys.  I did try using ODBC to access the DBase files but got a (to me) meaningless syntax error from the script.  From what you are saying, it looks as though I will have to convert the access query to  output the records into a temporary Access table, call this routine by invoking Access from my VB script and then run a select query from within the script to retrieve data from the temp table:@
I'll let you know how it goes...
But - .  I would really have liked to run this though the DBASE ODBC driver. I've not had any previous problem using this driver with simple selects but - and could it be because of the union or sub-select - with this query, the script is reporting that the table I am accessing is locked by another user.  I checked and it isn't!  Could it be that the driver is trying to make multiple connections to the data source?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11752981
Are you aware that the DBASE odbc driver has little different expectations about the SQL Sytnax?

Can you post the Syntax you tried along with the error message?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:atiffany
ID: 11753162
Sure,
This is an extract from the vbs script.

Here is the connection;
'=============

Con.ConnectionString="Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files;Dbq=I:\data\payroll"

'I also tried copying all the data to a local C drive and changing the Dbq above to C:\data\payroll burt it didn't make any difference.

'Depending on a runtime param (whether its a test run)
'=================================

If Not InTest Then
    strTblCnct = "cnct.dbf"
Else
    strTblCnct = "testCnct.dbf"
End If

'Here is the SQL string
'=============
    SQLTxt = "Select p.EmplRef, VhcCntrId, AdjDays, AdjLse, AdjInitial, AdjTerm, LseRate, " & _
             "       p.ChrgFrom, EPP, PPP, ChrgTo, OnRoadDat, OffRoadDat, RegId,  TrnsType, " & _
             "       PayPerd, p.CmpyCod, p.PlanCod, p.DivnCod, c.Divncod as DivGrp, InsrAdj, " & _
             "       InsrCost, RunNbr, RunDat, RunTim, PPPNbr, EPPNBR, HECosts, EmplId, EmplSnam, " & _
             "       EmplInits, Sono, EmplFnam, p.Brand, c.cnctemail " & _
             "from   TPayroll.dbf p, " & strTblCnct & " c " & _
             "Where ((p.PayPerd = c.Payroll) or c.Payroll = 'MW')    and " & _
             "        p.cmpyCod = c.cmpyCod                          and " & _
             "        p.planCod = c.planCod                          and " & _
             "        p.DivnCod = c.DivnCod                          and " & _
             "        p.Brand    = c.Brand  "
    SQLTxt = sqlTxt & " UNION " & _
             "SELECT p.EmplRef, VhcCntrId, AdjDays, AdjLse, AdjInitial, AdjTerm, LseRate, " & _
             "       p.ChrgFrom, EPP, PPP, ChrgTo, OnRoadDat, OffRoadDat, RegId,  TrnsType, " & _
             "       PayPerd, p.CmpyCod, p.PlanCod, p.DivnCod, c.Divncod as DivGrp, InsrAdj, " & _
             "       InsrCost, RunNbr, RunDat, RunTim, PPPNbr, EPPNBR, HECosts, EmplId, EmplSnam, " & _
             "       EmplInits, Sono, EmplFnam, p.Brand, c.cnctemail " & _
             "from   TPayroll.dbf  p, " & strTblCnct & " c " & _
             "WHERE ((p.PayPerd = c.Payroll) or c.Payroll = 'MW')          and " & _
             "        p.cmpyCod = c.cmpyCod                                and " & _
             "        c.planCod  = P.PLANCOD                               and " & _
             "        C.DivnCod = '*'                                      and " & _
             "        p.Brand    = c.Brand                                 AND " & _
             "NOT EXISTS " & _
             "  (  SELECT REGID FROM TPAYROLL.dbf P1, " & strTblCNCT & " C1 " & _
             "     WHERE P1.REGID = P.REGID                                  AND " & _
             "           ((p1.PayPerd = c1.Payroll) or c1.Payroll = 'MW')    and " & _
             "             p1.cmpyCod = c1.cmpyCod                           and " & _
             "             p1.planCod = c1.planCod                           and " & _      
             "             p1.DivnCod = c1.DivnCod                           and " & _      
             "             p1.Brand    = c1.Brand      ) " & _
             "ORDER BY 17, 19,29, 12, 15"

Con.Open
rs.open SQLTxt, Con

The above line errors with WSH reportine error 80004005 Microsoft ODBC dBase Driver.  The database engine could not lock table 'TPAYROLL#dbf' because it is already in use by another person or process
Source: Microsoft OLD DB Provider for ODBC Drivers.

No other user is accessing the table in any way when the error occurs.  The error is repeatable time and time again.  I can see me accessing the file TPAYROLL.bdf just before the error pops up. then the connection to the table closes.

Many thanks
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 11753236
Actually, I see that you are accessing TPayroll.dbf twice+ in your code, which Access will certainly use to connect twice to this DBASE file (m$ is unable to optimize here when accessing the Access Database over ODBC/OLEDB :-( ... hence the errror you see...
Unless you rewrite the process to eliminate that bottleneck, you won't find a solution, I fear...
Cheers
0
 
LVL 3

Expert Comment

by:Julian_C
ID: 11755910
So, when you connect to the dbase files with the dbase driver and get the locking issues have you tried changing the locking method to something more optimistic, like adLockOptimistic. As dbase's so file based it might not be possible but it's worth a go. I think it's the 4th parameter on the .open statment.
0
 

Author Comment

by:atiffany
ID: 11760222
Thx Julian, I'll give that a go. AngelIII suggests rewriting.  I really wanted one routine that would do it (On the assumption that the more components I have the greater the risk of failure). Looks as though from what you are saying, I may not have an option.  Thanks for your help and as always, I'll let you know how I get on.

0
 

Author Comment

by:atiffany
ID: 11764281
I am happy to report that the issue has now been resolved.  I managed to perform the query using the Visual Foxpro  ODBC driver rather than the DBaseIII driver I was using.  It seems that MDAC2.1 and greater do not support file-sharing of DBASE.  A full lock is placed on any file being queried and I assume that as I was performing a sub-select, an attemp was made by the driver to create a second connection to the datasource.  That was failing because of the full lock by the first connection. Apparently, Vis FoxPro driver doesn't apply these restrictions - It's also amazingly fast!

Thanks to AngelIII and Julian C for assisting and leading me to the conclusion that using the DB3 driver was futile.  I am therefore splitting the points between both.
Thanks guys.


0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

22 Experts available now in Live!

Get 1:1 Help Now