amfony Posted September 27, 2007 Share Posted September 27, 2007 Hi Guys, Continuing from the last post i made (about usb device criteria and usage) i want to be able to both write and search a access DB that is on a 'server' from within AutoIT. I have success with testing - as in the Access DB is on my laptop i am writing my scripts on. I created a System DSN for that AccessDB and i use the following to connect to it: $oConn = ObjCreate("ADODB.Connection") ;$oConn.Provider ("Microsoft.Jet.OLEDB.4.0") >> My attempt to get it working over network $oConn.Open ("MYDB_SYSTEM DSN") $oResult = $oConn.Execute( "my sql statments" ) But when i try to use everything i find on the internet about remote connections i get the 'object has failed that request' type error. I am frustrated with this as i have no DB experience (remotley) and i cant even tell if im using ODBC, OLE or whatever .. it is very much my weak point. Can anyone help me please!! I am not worthy (in this case that is true) Thanks alot anyone - i am sure that this is very easy for someone - just not me. Thanks again! Link to comment Share on other sites More sharing options...
ptrex Posted September 27, 2007 Share Posted September 27, 2007 @amfony This will get you started, but don't aks questions because I didn't make it. Just fill the in the location of the file on the remote server using a UNC path expandcollapse popup; Modify the variables below as desired or applicable ; ****************************************************** $dbname = "c:\test.mdb" $tblname = "table1" ;$fldname = "txt1" ;$format = "Text(50)" ;$data = "Blah Blah Blah" ;$fldname = "num1" ;$format = "Number" ;$data = 99 $fldname = "date1" $format = "Date" $data = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2) ; Un-comment the action you want to do ; ****************************************************** ;_CreateDB($dbname) ;_CreateTBL($dbname, $tblname) ;_CreateFLD($dbname, $tblname, $fldname, $format) ; Example of how to add data to a single field of a table ; ****************************************************** ;_AddData($dbname, $tblname, $fldname, $data) ; Example of how to list all of the Fields in a particular table ; ****************************************************** ;$str = _FieldNames($dbname, $tblname) ;MsgBox(0, "Fields in " & $tblname, $str) ; Example of how to read the info from all records in one field of a table as a string ; ****************************************************** ;$query = "SELECT * FROM " & $tblname ;$strData = _ReadOneFld($query, $dbname, $fldname) ;MsgBox(0, $fldname, $strData) ; Example of how to get entire rows of pattern matched data ; This returns a two dimensional array where [0][0] holds the number of matching ; records found, [0][1] holds the first field name [0][2] holds the second etc. ; [1][1] is the data from the first record - first field ; [2][3] is the data from the 2nd record - 3rd field ; ****************************************************** ;$query = "SELECT * FROM " & $tblname & " WHERE " & $fldname & " = 99" ;$rows = _RecordSearch($dbname, $query) ;_2dArrayDisp($rows) ; Example of how to add an entire redord into a table at one time ; For this example: ; First make sure you have a database created including a table ; and also that the table has the fields that you want to use. ; Then create a 2 dimensional array populated much like the one above ; ****************************************************** ;Dim $row[2][4] ;$row[0][0] = UBound($row, 1) - 1 ;$row[0][1] = "num1" ;$row[1][1] = 251 ;$row[0][2] = "txt1"; match the field name ;$row[1][2] = "Denver, CO"; with the data you are going to use ;$row[0][3] = "date1" ;$row[1][3] = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2) ;_AddEntireRecord($dbname, $tblname, $row) ; this will show the entire table ;$query = "SELECT * FROM " & $tblname ;$rows = _RecordSearch($dbname, $query) ;_2dArrayDisp($rows) Func _CreateDB($dbname) $newMdb = ObjCreate ("ADOX.Catalog") $newMdb.Create ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) $newmdb.ActiveConnection.Close EndFunc ;==>_CreateDB Func _CreateTBL($dbname, $tblname) $addtbl = ObjCreate ("ADODB.Connection") $addTbl.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) $addTbl.Execute ("CREATE TABLE " & $tblname) $addtbl.Close EndFunc ;==>_CreateTBL Func _CreateFLD($dbname, $tblname, $fldname, $format) $addfld = ObjCreate ("ADODB.Connection") $addfld.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) $addfld.Execute ("ALTER TABLE " & $tblname & " ADD " & $fldname & " " & $format) $addfld.Close EndFunc ;==>_CreateFLD Func _AddData($dbname, $tblname, $fldname, $value) $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname & ";") $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 2 $adoRs.LockType = 3 $adoRs.Open ("SELECT * FROM " & $tblname, $adoCon) $adoRs.AddNew $adoRs.Fields ($fldname).Value = $value $adoRs.Update $adoRs.Close $adoCon.Close EndFunc ;==>_AddData Func _FieldNames($dbname, $tblname) $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.Open ("SELECT * FROM " & $tblname, $adoCon) $name = "" For $i = 1 To $adoRs.Fields.Count $name = $name & $adoRs.Fields ($i - 1).Name & @CRLF Next Return $name EndFunc ;==>_FieldNames Func _ReadOneFld($_sql, $_dbname, $_field) Dim $_output $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($_sql, $adoCon) With $adoRs If .RecordCount Then While Not .EOF $_output = $_output & .Fields ($_field).Value & @CRLF .MoveNext WEnd EndIf EndWith $adoCon.Close Return $_output EndFunc ;==>_ReadOneFld Func _RecordSearch($_dbname, $_query) $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($_query, $adoCon) With $adoRs Dim $_output[.Fields.Count + 1][.RecordCount + 1] $_output[0][0] = .RecordCount For $i = 1 To .Fields.Count $_output[$i][0] = .Fields ($i - 1).Name Next If $adoRs.RecordCount Then $z = 0 While Not .EOF $z = $z + 1 For $x = 1 To .Fields.Count $_output[$x][$z] = .Fields ($x - 1).Value Next .MoveNext WEnd EndIf EndWith $adoCon.Close Return $_output EndFunc ;==>_RecordSearch Func _AddEntireRecord($_dbname, $_tblname, $_array) $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname & ";") $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 2 $adoRs.LockType = 3 $adoRs.Open ("SELECT * FROM " & $tblname, $adoCon) $adoRs.AddNew $records = $_array[0][0] For $i = 1 To $records For $x = 1 To UBound($_array,2) - 1 $adoRs.Fields ($_array[0][$x]).Value = $_array[1][$x] Next Next $adoRs.Update $adoRs.Close $adoCon.Close EndFunc ;==>_AddEntireRecord Func _2dArrayDisp($array) $report = "Number of records matching = " & $array[0][0] & @CRLF For $x = 1 To UBound($array, 2) - 1 For $i = 1 To UBound($array, 1) - 1 $report = $report & $array[$i][$x] & "|" Next $report = StringTrimRight($report, 1) & @CRLF Next MsgBox(0, "Display", $report) EndFunc ;==>_2dArrayDisp regards ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New Link to comment Share on other sites More sharing options...
DaleHohm Posted September 27, 2007 Share Posted September 27, 2007 And regarding the "remote" piece, I don't think that the Jet engine support anything but local access. I learned this the hard way when I tried to point a web server at an access database on a UNC mapped drive... Dale Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model Automate input type=file (Related) Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded Better Better? IE.au3 issues with Vista - Workarounds SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead? Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble Link to comment Share on other sites More sharing options...
amfony Posted September 28, 2007 Author Share Posted September 28, 2007 Hi Guys THanks for the input - the code really did help. It showed me how good i am at proof reading. I am crap. I had a spelling mistake in my db name variable. Shameful i know - i am not happy with myself either. Thanks both, just to let you know it does seem to work over the network with out issue. However even though i am poor at this - i can probably tell this is likley to be an inefficent way to access the DB as opposed to a DB Server server. Thanks alot again - stay tuned for more issues with my script haha Link to comment Share on other sites More sharing options...
DaleHohm Posted September 28, 2007 Share Posted September 28, 2007 just to let you know it does seem to work over the network with out issue. However even though i am poor at this - i can probably tell this is likley to be an inefficent way to access the DB as opposed to a DB Server server.Thats great to know. My experience with this was 8 years ago, so it's good to know that some things do get better...Dale Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model Automate input type=file (Related) Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded Better Better? IE.au3 issues with Vista - Workarounds SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead? Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble Link to comment Share on other sites More sharing options...
IvanCodin Posted October 8, 2007 Share Posted October 8, 2007 When I attempt to read a database I get an error. The error indicates the function is not valid. Here is the section of the code it complains about. Func _RecordSearch($_dbname, $_query) $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($_query, $adoCon) With $adoRs Dim $_output[.Fields.Count + 1][.RecordCount + 1] $_output[0][0] = .RecordCount For $i = 1 To .Fields.Count $_output[$i][0] = .Fields ($i - 1).Name Next If $adoRs.RecordCount Then $z = 0 While Not .EOF $z = $z + 1 For $x = 1 To .Fields.Count $_output[$x][$z] = .Fields ($x - 1).Value <--- The .Field is what it compains about. Tried several varations. Next .MoveNext WEnd EndIf EndWith $adoCon.Close Return $_output EndFunc ;==>_RecordSearch I tried to change .Fields to $Fields and _Value and $_Value. None of these worked. I am a novice programmer and this is beyond my understand as to how the function works. Any suggestions? Me 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