ADO connection, Access, Queries - Oh my!

[Goldfishhh]

I've managed to get setup correctly an ADO connection into my Access database and can sucessfully execute a query (e.g. Select * from table_blah).

I want to change my query so that it selects data from one table and creates a new table. (e.g. SELECT oldtable.field_1, oldtable.field2, oldtable.field3 INTO newtable FROM oldtable)

When I try to execute the new query, I get an error:

C:\accessload.au3 (29) : ==> The requested action with this object has failed.:

$adoRs.open($query2, $adoCon)

$adoRs.open($query2, $adoCon)^ ERROR

->13:03:46 AutoIT3.exe ended.rc:1

+>13:03:48 AutoIt3Wrapper Finished

>Exit code: 1 Time: 2.129

I've played around with the Cursor and lock types (CursorType = 2 and LockType = 3). Now am stuck.

Any ideas?

expandcollapse popup

$Select ="SELECT IPW_ARCHIVE_SUMMARY.JOBID, IPW_ARCHIVE_SUMMARY.PARENTJOBID, IPW_ARCHIVE_SUMMARY.MATCHINGJOBID, IPW_ARCHIVE_SUMMARY.SUBMITTIMESTAMP, IPW_ARCHIVE_SUMMARY.DOCDATE, IPW_ARCHIVE_SUMMARY.PROCESS, IPW_ARCHIVE_SUMMARY.FORM, IPW_ARCHIVE_SUMMARY.JOBTYPE, IPW_ARCHIVE_SUMMARY.JOBDESCRIPTION, IPW_ARCHIVE_SUMMARY.LINEOFBUSINESS, IPW_ARCHIVE_SUMMARY.JOBS_TOTALDOCS_MAILED, IPW_ARCHIVE_SUMMARY.ONE_OZ_PIECE_COUNT, IPW_ARCHIVE_SUMMARY.TWO_OZ_PIECE_COUNT, IPW_ARCHIVE_SUMMARY.OVER_TWO_OZ_PIECE_COUNT, IPW_ARCHIVE_SUMMARY.UAA_COUNT, IPW_ARCHIVE_SUMMARY.FOREIGN_COUNT, IPW_ARCHIVE_SUMMARY.CHANGED_COUNT, IPW_ARCHIVE_SUMMARY.MVSJOBNAME, IPW_ARCHIVE_SUMMARY.DOCTYPE, IPW_ARCHIVE_SUMMARY.PRIORITY, IPW_ARCHIVE_SUMMARY.SITEOWNER, IPW_ARCHIVE_SUMMARY.SITEASSIGNED, IPW_ARCHIVE_SUMMARY.PRINTERNAME, IPW_ARCHIVE_SUMMARY.TOTALDOCS, IPW_ARCHIVE_SUMMARY.TOTALSHEETS, IPW_ARCHIVE_SUMMARY.ITERATIONCOUNT, IPW_ARCHIVE_SUMMARY.ENCLOSERTYPE, IPW_ARCHIVE_SUMMARY.DISPATCHTYPE, IPW_ARCHIVE_SUMMARY.SLAMONITOR, IPW_ARCHIVE_SUMMARY.SLASTATUS, IPW_ARCHIVE_SUMMARY.TESTJOB, IPW_ARCHIVE_SUMMARY.PRINT_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.QC_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.INSERT_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.RECON_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.DISPATCH_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.PRINT_PLAN, IPW_ARCHIVE_SUMMARY.PRINT_REAL, IPW_ARCHIVE_SUMMARY.QC_PLAN, IPW_ARCHIVE_SUMMARY.QC_REAL, IPW_ARCHIVE_SUMMARY.RECON_PLAN, IPW_ARCHIVE_SUMMARY.RECON_REAL, IPW_ARCHIVE_SUMMARY.DISPATCH_PLAN, IPW_ARCHIVE_SUMMARY.DISPATCH_REAL FROM IPW_ARCHIVE_SUMMARY"
$Select2 ="SELECT IPW_ARCHIVE_SUMMARY.JOBID, IPW_ARCHIVE_SUMMARY.PARENTJOBID, IPW_ARCHIVE_SUMMARY.MATCHINGJOBID, IPW_ARCHIVE_SUMMARY.SUBMITTIMESTAMP, IPW_ARCHIVE_SUMMARY.DOCDATE, IPW_ARCHIVE_SUMMARY.PROCESS, IPW_ARCHIVE_SUMMARY.FORM, IPW_ARCHIVE_SUMMARY.JOBTYPE, IPW_ARCHIVE_SUMMARY.JOBDESCRIPTION, IPW_ARCHIVE_SUMMARY.LINEOFBUSINESS, IPW_ARCHIVE_SUMMARY.JOBS_TOTALDOCS_MAILED, IPW_ARCHIVE_SUMMARY.ONE_OZ_PIECE_COUNT, IPW_ARCHIVE_SUMMARY.TWO_OZ_PIECE_COUNT, IPW_ARCHIVE_SUMMARY.OVER_TWO_OZ_PIECE_COUNT, IPW_ARCHIVE_SUMMARY.UAA_COUNT, IPW_ARCHIVE_SUMMARY.FOREIGN_COUNT, IPW_ARCHIVE_SUMMARY.CHANGED_COUNT, IPW_ARCHIVE_SUMMARY.MVSJOBNAME, IPW_ARCHIVE_SUMMARY.DOCTYPE, IPW_ARCHIVE_SUMMARY.PRIORITY, IPW_ARCHIVE_SUMMARY.SITEOWNER, IPW_ARCHIVE_SUMMARY.SITEASSIGNED, IPW_ARCHIVE_SUMMARY.PRINTERNAME, IPW_ARCHIVE_SUMMARY.TOTALDOCS, IPW_ARCHIVE_SUMMARY.TOTALSHEETS, IPW_ARCHIVE_SUMMARY.ITERATIONCOUNT, IPW_ARCHIVE_SUMMARY.ENCLOSERTYPE, IPW_ARCHIVE_SUMMARY.DISPATCHTYPE, IPW_ARCHIVE_SUMMARY.SLAMONITOR, IPW_ARCHIVE_SUMMARY.SLASTATUS, IPW_ARCHIVE_SUMMARY.TESTJOB, IPW_ARCHIVE_SUMMARY.PRINT_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.QC_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.INSERT_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.RECON_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.DISPATCH_BLOCK_COMPLETE, IPW_ARCHIVE_SUMMARY.PRINT_PLAN, IPW_ARCHIVE_SUMMARY.PRINT_REAL, IPW_ARCHIVE_SUMMARY.QC_PLAN, IPW_ARCHIVE_SUMMARY.QC_REAL, IPW_ARCHIVE_SUMMARY.RECON_PLAN, IPW_ARCHIVE_SUMMARY.RECON_REAL, IPW_ARCHIVE_SUMMARY.DISPATCH_PLAN, IPW_ARCHIVE_SUMMARY.DISPATCH_REAL INTO new FROM IPW_ARCHIVE_SUMMARY"

$query = $select
$query1 = $Select2

;The & is simply a concatenation of the strings.
;
;Let's set the variable for the one field that we want to retrieve from the database.;
;
Local $title

;Pretty straightforward so far, isn't it?

;Then create the connection to the ADODB:

$adoCon = ObjCreate("ADODB.Connection")

;Then set the Provider. Here is the Provider for a .mdb file:

$adoCon.Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\InetPub\WWWRoot\ipw\ipw2.mdb;UID=Admin")

;Now let's create the object Recordset, set some required options and then execute the query:

$adoRs = ObjCreate ("ADODB.Recordset")
$adoRs.CursorType = 2
$adoRs.LockType = 3
$adoRs.open($query, $adoCon)

$ipwid0 = $adoRs.fields(0).value
$ipwid1 = $adoRs.fields(1).value
$ipwid2 = $adoRs.fields(2).value
$ipwid3 = $adoRs.fields(3).value
$ipwid4 = $adoRs.fields(4).value
$ipwid5 = $adoRs.fields(5).value
$ipwid6 = $adoRs.fields(6).value
$ipwid7 = $adoRs.fields(7).value
$ipwid8 = $adoRs.fields(8).value
$ipwid9 = $adoRs.fields(9).value
$ipwid10 = $adoRs.fields(10).value
$ipwid11 = $adoRs.fields(11).value
$ipwid12 = $adoRs.fields(12).value
$ipwid13 = $adoRs.fields(13).value
$ipwid14 = $adoRs.fields(14).value
$ipwid15 = $adoRs.fields(15).value
$ipwid16 = $adoRs.fields(16).value
$ipwid17 = $adoRs.fields(17).value
$ipwid18 = $adoRs.fields(18).value
$ipwid19 = $adoRs.fields(19).value
$ipwid20 = $adoRs.fields(20).value
$ipwid21 = $adoRs.fields(21).value
$ipwid22 = $adoRs.fields(22).value
$ipwid23 = $adoRs.fields(23).value
$ipwid24 = $adoRs.fields(24).value
$ipwid25 = $adoRs.fields(25).value
$ipwid26 = $adoRs.fields(26).value
$ipwid27 = $adoRs.fields(27).value
$ipwid28 = $adoRs.fields(28).value
$ipwid29 = $adoRs.fields(29).value
$ipwid30 = $adoRs.fields(30).value
$ipwid31 = $adoRs.fields(31).value
$ipwid32 = $adoRs.fields(32).value
$ipwid33 = $adoRs.fields(33).value
$ipwid34 = $adoRs.fields(34).value
$ipwid35 = $adoRs.fields(35).value
$ipwid36 = $adoRs.fields(36).value
$ipwid37 = $adoRs.fields(37).value
$ipwid38 = $adoRs.fields(38).value
$ipwid39 = $adoRs.fields(39).value
$ipwid40 = $adoRs.fields(40).value
$ipwid41 = $adoRs.fields(41).value
$ipwid42 = $adoRs.fields(42).value
$ipwid43 = $adoRs.fields(43).value

;Close the connection unless you have a specific reason for leaving your connection open:

$adoCon.Close

;Then test the results with the MsgBox():

MsgBox(0,"blah",$title & $IPWid0 & " " & $IPWid3)

[ResNullius]

Isn't the syntax "FROM" then "INTO" ?

ie

NOT:

SELECT oldtable.field_1, oldtable.field2, oldtable.field3 INTO newtable FROM oldtable

but rather:

SELECT oldtable.field_1, oldtable.field2, oldtable.field3 FROM oldtable INTO newtable