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.ValueFROM ((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));
1) I'm using access 2003, so when opening the database get this msg: opening "c:/competencies.mdb"
This file may not be safe if it contains code that was intended to harm your computer.
Do you want to open this file or cancel the operation?
And then I have to hit the open button. Is there anywhere to bypass this?
2) this code expects c:\books.xls to exist, and that the fields from the query are already there. Ideally I'd like the file to be deleted shortly after being exported. is there a way around this?
Thanks for the help