Sign in to follow this  
Followers 0
Ghost21

Run a Query

17 posts in this topic

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 ?

Share this post


Link to post
Share on other sites



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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Yes its ADODB Connection.

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Ghost21,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

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!!!...

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

01_UpdateSawMoveStatus is my Query inside the database..

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!!!

Share this post


Link to post
Share on other sites

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 ..

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0