Vossen Posted June 4, 2008 Share Posted June 4, 2008 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 Link to comment Share on other sites More sharing options...
DaRam Posted June 4, 2008 Share Posted June 4, 2008 (edited) 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 Link to comment Share on other sites More sharing options...
Vossen Posted June 4, 2008 Author Share Posted June 4, 2008 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. Link to comment Share on other sites More sharing options...
DaRam Posted June 4, 2008 Share Posted June 4, 2008 $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 popupDim $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 Link to comment Share on other sites More sharing options...
Vossen Posted June 4, 2008 Author Share Posted June 4, 2008 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now