Access Database?

[Vossen]

Hello,

I am trying to make an access database connection like when I'm writing ASP in Visual Basic.

It seems to work somewhat, but I can't make it write anything.

$Conn = ObjCreate("ADODB.Connection")
$Conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & @ScriptDir & "\database.mdb")

$strSQL = "Select * from Tabel"
$RS = $Conn.Execute($strSQL)

If $RS.EOF Then

MsgBox(0,"Empty!","The databasen is empty!")

Else
    Do

        FileWrite("Result.txt",$RS("Record") & " 1")
        $RS.MoveNext

    Until $RS.EOF
EndIf

$Conn.Close
$Conn = "Nothing"

This is what I've made the problems seems to be the $RS("Record"), any ideas or isn't it possible to do it like this?

// Vossen

[DaRam]

Try adding () to all Recordset properties.

So, $RS.EOF will be $RS.EOF(), etc.

Edit: Above not required.

Instead of:

$RS = $Conn.Execute($strSQL)

Try:

$Rs.Open($strSQL, $Conn)

Edited June 4, 2008 by DaRam

[Vossen]

I think this is what you ment,

$Conn = ObjCreate("ADODB.Connection")
$Conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & @ScriptDir & "\database.mdb")

$strSQL = "Select * from Tabel"
$RS.Open($strSQL, $Conn)

If $RS.EOF() Then

MsgBox(0,"Empty!","The databasen is empty!")

Else
    Do

        FileWrite("Result.txt",$RS("Record") & " 1")
        $RS.MoveNext()

    Until $RS.EOF()
EndIf

$Conn.Close()
$Conn = "Nothing"

But it gives me an error,

Line 5 (File "xxx\Access Connection.au3"):

$RS.Open($strSQL, $Conn)
$RS^ ERROR

Error: Expected a "=" operator in assignment statement.

[DaRam]

$RS = $Conn.Execute($strSQL) Should work also.

Database tips:

Do not use keywords like Table, Database, Record to name....you got it a Table, Database, or a Field/column.

Wrap Access SQL Table and field references in [], and terminate with ;

So, instead of "SELECT field1, field2 FROM sometbl", "SELECT [field1], [field2] FROM [sometbl];" is safer.

Using your example, this is a better SQL: SELECT * FROM [Tabel];

Anyway. Try this and let me know if you get errors. Output should be in in a Text file with the same name as the script.

expandcollapse popup

Dim $oError
Dim $I, $J, $Txt, $CSVFile
Dim $adoCn = ObjCreate( "ADODB.Connection" )
Dim $adoRs = ObjCreate( "ADODB.RecordSet" )

; Initializes COM handler
$oError = ObjEvent("AutoIt.Error", "ErrHandler")

If not IsObj($adoCn) Then MsgBox(0, "Error","Cannot create ActiveX Data Connection Object")

$adoCn.Properties("Prompt") = 2;=adPromptComplete, 1 = adPromptAlways
$adoCn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & @ScriptDir & "\database.mdb")

$Txt = StringReplace(@ScriptFullPath, ".au3", ".txt")
$CSVFile = FileOpen($Txt, 1)

$adoRs.CursorLocation = 3; adUseClient
$Txt = "SELECT * FROM [Tabel];"; <- Better !
$Txt = "SELECT * FROM Tabel"
$adoRs.Open( $Txt, $adoCn)
$J = $adoRs.Fields.Count - 1
$Txt = ""
For $I = 0 To $J
   $Txt = $Txt & $adoRs.Fields($I).Name & @TAB
Next;$I
FileWrite($CSVFile, $Txt & @CRLF)

While NOT $adoRs.EOF
    $Txt = ""
    For $I = 0 To $J
        $Txt = $Txt & $adoRs.Fields($I).Value & @TAB
    Next;$I
    FileWrite($CSVFile, $Txt & @CRLF)
    $adoRs.MoveNext
WEnd

$Txt = ""
If $adoRs.CursorLocation <> 0 Then; NOT adOpenForwardOnly
     $Txt = $adoRs.RecordCount() & " Rows "
EndIf
$Txt = $Txt  & "Retrieved from Connection: " & $adoCn.ConnectionString
FileWrite($CSVFile, @CRLF & $Txt & @CRLF)
FileClose($CSVFile)

$adoRs.Close
$adoCn.Close

Func ErrHandler()
  $HexNumber=Hex($oError.number,8)
  Msgbox(0, StringReplace( $oError.windescription, "error", "COM Error #") & $oError.Number, _
            $oError.Description & @CRLF & _
            "Source: "       & @TAB & $oError.source         & @CRLF & _
            "at Line #: " & $oError.ScriptLine & @TAB & _
            "Last DllError: " & @TAB & $oError.lastdllerror   & @CRLF & _
             "Help File: " & @TAB & $oError.helpfile & @TAB & "Context: " & @TAB & $oError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
  Exit
Endfunc

[Vossen]

Thank you so much, I actually learned much from your post. Stripped down to what looks like what I used in ASP then it would look like,

$Conn = ObjCreate("ADODB.Connection")
$Conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & @ScriptDir & "\database.mdb;")

$strSQL = "Select * from [Tabel];"
$RS = $Conn.Execute($strSQL)

If $RS.EOF Then

MsgBox(0,"Empty!","The databasen is empty!")

Else
    Do

        FileWriteLine("Result.txt",$RS.Fields("Record").Value)
        $RS.MoveNext

    Until $RS.EOF
EndIf

$Conn.Close
$Conn = "Nothing"

It works and it's very basic, just what I needed.

Thanks again...

// Vossen

Edited June 4, 2008 by Vossen