Run a Query

[Ghost21]

Trying to Execute or Run a Query inside a Access Database.

instead of running

$Saw_oRS.open("UPDATE Data SET Data.Moved = -1 WHERE (((Data.Moved)=0) AND ((Data.Date_Start)<>'0000-00-00'))", $Saw_oConn, 1, 3)

I was hopeing to do something like

$Saw_oRS.EXECUTE("Data.Moved", $Saw_oConn, 1, 3)

Any ideas people ?

[PsaltyDS]

Is that using an AODB connection? The syntax of the query is determined by the interface you are using, not by AutoIt.

[Ghost21]

Yes its ADODB Connection.

[kylomas]

Ghost21,

In an ADO EXECUTE stmt the 1st parm is an SQL stmt, as in your first example.

You can set a variable to the sql like

local $sqlvar = "UPDATE Data SET Data.Moved = -1 WHERE (((Data.Moved)=0) AND ((Data.Date_Start)<>'0000-00-00'))"

and then do

$Saw_oRS.EXECUTE($sqlvar, $Saw_oConn, 1, 3)

kylomas

[Ghost21]

Thats what I do right now.. I would prefer to just do somethign like $ors.conn.Execute("Query1") that way the code stays in the database and all the Operations for the most part in the database.

[kylomas]

Ghost21,

I don't have any experience in this but do "stored procedures" fit the bill?

kylomas

[Ghost21]

I read somethign about procedures but have no idea how to run that from autoit.

[Ghost21]

Well apparentley after alot of drinking / googleing your able to do a ADODB Execute .. Soemthing like

Set recordset = command.Execute( RecordsAffected, Parameters, Options )

Think I can get the stupid thing to work... NOPE...

If anyone can pass any help along on how to execute a query on the database side and have it run.. PLEASE let me know...

[PsaltyDS]

When you want to run a multi-line query, create the query string between BEGIN/END statements.

You can also create store procedures with the CREATE PROCEDURE function, then run it with EXECUTE.

[hannes08]

Hi Ghost21,

I have an vbs example that might help you here:

Set connDB = CreateObject("ADODB.Connection")
connDB.ConnectionString = "DSN=updt1413;DBQ=C:\accessdb.mdb;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
connDB.open

Set comm = CreateObject("ADODB.Command")
comm.ActiveConnection = connDB

comm.CommandText = "Import1"
comm.execute

comm.CommandText = "Insert1"
comm.execute

comm.CommandText = "Update1"
comm.execute

Set comm = Nothing
Set connAktualizaceDB = Nothing

There should be no big problem porting this to AutoIT.

[Ghost21]

Func _SawdbOpen()

$Saw_oConn = ObjCreate("ADODB.Connection")

$Saw_oRS = ObjCreate("ADODB.Recordset")

If FileExists("SawData.mdb") Then

$Saw_oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=SawData.mdb")

Else

EndIf

EndFunc ;==>_SawdbOpen

This is what I tried

_SawdbOpen()

$comm = ObjCreate("ADODB.Command")

$comm.ActiveConnection = $Saw_oConn

$comm.CommandText = "01_UpdateSawMoveStatus"

$comm.execute

_SawdbClose()

EndFunc ;==>_UpdateSawMoveStatus

This is the ERROR I get

COM Error Details:

err.description is: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; ex[ected 'DELETE','INSERT','PROCEDURE'.'SELECT',or 'UPDATE'

err.source is Microsoft OLD DB PRovider for ODBC Drivers

Below is whats working right now...

$Saw_oRS.open("UPDATE Data SET Data.Moved = -1 WHERE (((Data.Moved)=0) AND ((Data.Date_Start)<>'0000-00-00'))", $Saw_oConn, 1, 3)

$Saw_oConn.close

This isn't right as your code should all be in the database not in your program..

HELP!!!...

[DarkestHour]

ADO Is still expecting an SQL Statement.

Your line

$comm.CommandText = "01_UpdateSawMoveStatus"

That is where it is expecting the statement to be.

Where are you getting "01_UpdateSawMoveStatus" from?

[Ghost21]

01_UpdateSawMoveStatus is my Query inside the database..

[DarkestHour]

I was taking a look at this page.

http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6

Go there and see if it may lead you in the right direction. It looks like the Access Query Objects are very rudimentary and have to be formatted a certain way.

Please post your Query object after you take a look at that.

[Ghost21]

I read the whole thing and I can't see anything that puts me any closer.

[Ghost21]

RECAP;

Made a Query inside an Access 2003 Database.

That inturn runs a large SQL statement against the table.

1. Query Marks Data

2. Appends Data to Backup Table

3. Deletes the Marked Data

So instead of writing a huge statement like $Saw_oRS.open("UPDATE Data SET Data.Moved = -1 WHERE (((Data.Moved)=0) AND ((Data.Date_Start)<>'0000-00-00'))", $Saw_oConn, 1, 3) and others that are way bigger..

If I can just run the name of the Query called 01_Markrecords like $Saw_Conn.Execute("01_Markrecords") would be way easier.

It is such a better pratice to put all the database code in the database instead of in your program... SOMEONE PLEASE HELP!!!

[Ghost21]

VICTORY!!!

_SawdbOpen()
;$Saw_oRS.open("DELETE Data.*, Data.Moved, Data.Date_Start FROM Data WHERE (((Data.Moved)=-1) AND ((Data.Date_Start)<>'0000-00-00'))", $Saw_oConn, 1, 3)
$Saw_oRS.Open('exec 04_ClearOldSawData', $Saw_oConn, 1, 3)     <--- Look how simple and clean that is .. 
$Saw_oConn.close
_SawdbClose()

What this does is it gives the processing power to the database and your client is just a thin frontend.

$Saw_oRS.Open('exec 04_ClearOldSawData', $Saw_oConn, 1, 3) <--- Look how simple and clean that is ..