Jump to content
Sign in to follow this  

SQL Query with joined tables

Recommended Posts

Hello to you all,

I wonder, if there's anyone who can give me answer on the question if a SQL query with joined tables does work with adodb and the "open" command


Underneath will work

Local $oRS
Local $oConn

$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:filesdata.mdb ;pwd="")
$SQLt_Orders= "SELECT t_Orders.n_OrderNum, t_Orders.OrderDate, t_Orders.n_OrderStatus, t_Orders.n_TotalPrice, t_Orders.n_OrderPriceNet FROM t_Orders"

But if i'm joining fields from another table, I cant get it working in autoit, the sql statement is working properly in access.

Do I need to open all joined tables or do I need to simplify my SQL statement and join tables in auto IT?


Many thanks in advance



Share this post

Link to post
Share on other sites

Hi, Klassieker. Something you might try - I recently ran into a similar problem (in my case a very, very long sql statement) and ended up trying something like this as a workaround, just for testing. I was connecting to Oracle, but the gist of it should be the same:

Local $aQuery
    _FileReadToArray(@DesktopDir & "\Payroll.sql", $aQuery)
    $sString = _ArrayToString($aQuery, " ")

$sCallersConnectionString = "Provider=MSDAORA.1;User ID=jeremiahlx;Password=Change123;Data Source=EDWPROD;Persist Security Info=False"
$sCallersSQLStatement = StringTrimLeft($sString, 3)

Basically, it reads the sql file into an array, parses to a string, and then calls that string for the Statement. It's a bit of a dirty hack, but it will at least tell you if you're getting the syntax wrong when typing it into the script, or if there is something else going on.

Edited by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Share this post

Link to post
Share on other sites

Hello to you all,

I wonder, if there's anyone who can give me answer on the question if a SQL query with joined tables does work with adodb and the "open" command


I confirm that you can join tables. That works fine.

I use a slightly different DSN-less connection string using (MSAccess 2010) but that shouldn't be an issue

DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:filesdata.md;uid=;pwd=;

I always use

    $oRS.CursorType = 2

    $oRS.LockType = 3

$adoRs.Open($SQLt_Orders, $oConn)

and I am using this SQL on my CATRaxx mdb database, works perfectly well

    Track.ArtistSort AS "Artist",
    Track.Title AS "Track Title",
    (INT(Track.Length/60) + round(((Track.Length/60) - INT(Track.Length/60))*0.60,2)) AS "Length",
    (SELECT PersonalRating.SortBy FROM PersonalRating WHERE Track.PersonalRatingID = PersonalRating.PersonalRatingID) AS "Track Rating", 
    (SELECT PersonalRating.PersonalRating FROM PersonalRating WHERE Track.PersonalRatingID = PersonalRating.PersonalRatingID) AS "Rating", 
    Album.Title AS "Album Title",
    (SELECT PersonalRating.SortBy FROM PersonalRating WHERE Album.PersonalRatingID = PersonalRating.PersonalRatingID) AS "Album Rating", 
    Track.Index AS "Track Nr",
    Track.Released AS "Track Released", 
    LEFT(Track.LastPlayed, 19) AS "Last Played",
    IIF(Track.Instrumental = -1,'Yes','')  AS "Instrumental",
    Track.Notes_PlainText AS "Notes"    
        INNER JOIN Track ON Album.AlbumID = Track.AlbumID
    Track.LastPlayed DESC

As you didn't publish the complete script, it's hard to say what goes wrong, but if you fire two consecutive SQL's you have to open 2 connections, something like this (this is a SQL to a CSV file)

Func FireSQL($connection, $search)
    Local $sResult = "", $adoCon, $adoRs, $adoCon2, $adoRs2, $adoSQL, $iRow
    $adoCon = ObjCreate("ADODB.Connection")
    If @error Then Exit MsgBox(48, "FireSQL Error", "Connection error: " & @error)
    $adoRs = ObjCreate("ADODB.Recordset")
    ; count rows
    $adoSQL = "SELECT count(*) as Count FROM CrewPhone.csv"

    $adoRs.CursorType = 2
    $adoRs.LockType = 3
    $adoRs.Open($adoSQL, $adoCon)

    With $adoRs
        If .RecordCount Then
            $sResult = $sResult & "" & .Fields("Count").Value & @CR
    ;ConsoleWrite(@ScriptLineNumber & " " & $sResult & @CR)
    Local $aResult[$sResult + 1][5]
    $aResult[0][0] = $sResult
    $adoCon2 = ObjCreate("ADODB.Connection")
    If @error Then Exit MsgBox(48, "FireSQL Error", "Connection error " & @error)
    $adoRs2 = ObjCreate("ADODB.Recordset")

    If StringLen($search) > 0 Then
        $adoSQL = "SELECT * FROM CrewPhone.csv where instr(`FullName`,'" & $search & "')>0"
        $adoSQL = "SELECT * FROM CrewPhone.csv"

    $adoRs2.CursorType = 2
    $adoRs2.LockType = 3
    $adoRs2.Open($adoSQL, $adoCon2)
    $iRow = 0
    With $adoRs2
        If .RecordCount Then
            While Not .EOF
                $iRow += 1
                $aResult[$iRow][0] = .Fields("FullName").Value
                $aResult[$iRow][1] = .Fields("MobileTelephoneNumber").Value
                $aResult[$iRow][2] = .Fields("BusinessTelephoneNumber").Value
                $aResult[$iRow][3] = .Fields("Email1Address").Value
                $aResult[$iRow][4] = .Fields("Email2Address").Value

    Return $aResult
EndFunc   ;==>FireSQL


CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image




Share this post

Link to post
Share on other sites

Sorry, for not responding to the suggestions.

I lost my complete include folder and also my testenvironment. Don't know why, but I cant find it back.

Because of the testing, I didn't backup my folders.

Shame on me.

I wil come back with the string I've  made in Acces (old version) to use it in Autoit.

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...