troubleshooting Question

VBS file calling complex access query getting errors

Avatar of Butterfield_Cayman
Butterfield_Cayman asked on
Microsoft AccessVB Script
11 Comments1 Solution490 ViewsLast Modified:
i have a vbs file that is exporting a access query into an excel spreadsheet.

I have a number of problems due to the complexity of the query. I'm using left, right, instring, and various other functions in the qry. When I run the vbs file I recieve the following error: error: "invalod procedure call" code: "80040E14" source: "Microsoft JET Database Engine". I've tried cleaning the qry but am loosnig too much of the functionality.

I see a number of options for my solution:
1) if someone could send me a fix that would allow me to run this qry that would be the ideal solution
2) if not, as an alternate, i could write a create table qry, and a delete table qry and then run the createtbl qry, export the table, and then delete it after. I just need to know how to run queries in access from a vbs file.

the first is my prefered solution and if someone can assist me in finding that by the end of the day I'll up the points. Attached is my vbs snippett and my access qry.
========vbs code===============
 
Set conn = CreateObject ("ADODB.CONNECTION")
Set rs = CreateObject("ADODB.RECORDSET")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Competencies.mdb"
 
sql = "SELECT * INTO [Excel 8.0;Database=c:\book.xls].[NewSheet] FROM [TomTest]"
 
rs.Open sql, conn, 3, 3
 
 
============== access qry==================
SELECT [EMPLOYEE MASTER].Active AS STATUS, [EMPLOYEE MASTER].[Employee ID] AS USERID, ([EMPLOYEE MASTER.Employee ID]+(Left([LASTNAME],4))) AS USERNAME, Right([EMPLOYEE MASTER.employee name],(Len([EMPLOYEE MASTER.employee name])-(InStr([EMPLOYEE MASTER.employee name],",")+1))) AS FIRSTNAME, Left([EMPLOYEE MASTER.employee name],(InStr([EMPLOYEE MASTER.employee name],",")-1)) AS LASTNAME, [EMPLOYEE MASTER].Sex AS GENDER, [EMPLOYEE MASTER].Email AS EMAIL, [Find Supervisor ID].[EMPLOYEE MASTER_Employee ID] AS USERID2, "1581" AS HR, [Find Supervisor ID].[Employee ID] AS MANAGER, [EMPLOYEE MASTER].[Job Title] AS TITLE, "KY" AS LOCATION, "EST" AS TIMEZONE, TomEmpPayGrade.Code, IIf([Value] In ("1","2","3","4"),"GRADE01-04",IIf([Value] In ("5","6","7","8"),"GRADE05-08",IIf([Value] In ("9","10","11"),"GRADE09-11","GRADE12-15"))) AS jobcodetom, TomEmpPayGrade.Value AS CUSTOM01, DIVISIONS.[Div Name] AS DIVISION, DEPARTMENTS.[Dept Name] AS DEPARTMENT, [EMPLOYEE MASTER].[Vacation Days], [EMPLOYEE MASTER].[Vacation Remaining], ([EMPLOYEE MASTER.Vacation Days])-([EMPLOYEE MASTER.Vacation Remaining]/7.5) AS CUSTOM04, [EMPLOYEE MASTER].[Sick Remaining], [EMPLOYEE MASTER].[Sick Days], [EMPLOYEE MASTER.Sick Days]-([EMPLOYEE MASTER.Sick Remaining]/7.5) AS CUSTOM03, [EMPLOYEE MASTER].[Start Date] AS HIREDATE, [EMPLOYEE MASTER].[Current Job Start Date] AS CUSTOM02, TomEmpPayGrade.Value
FROM ((DEPARTMENTS INNER JOIN (DIVISIONS INNER JOIN [EMPLOYEE MASTER] ON DIVISIONS.ID = [EMPLOYEE MASTER].[Div ID]) ON DEPARTMENTS.ID = [EMPLOYEE MASTER].[Dept ID]) INNER JOIN [Find Supervisor ID] ON [EMPLOYEE MASTER].[Employee ID] = [Find Supervisor ID].[EMPLOYEE MASTER_Employee ID]) INNER JOIN TomEmpPayGrade ON [EMPLOYEE MASTER].[Employee ID] = TomEmpPayGrade.[Employee ID]
WHERE ((([EMPLOYEE MASTER].Active)=True));
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros