ACalcutt Posted January 25, 2006 Posted January 25, 2006 (edited) does anyone know how i could use a access mdb file in my script without actually needing access installed? maybe an external DLL call or something?... i haven't done anything with dlls yet...so if its possible any help would be apreciated edit..i see i messed up on the title... MDB...not MDF ... Edited January 25, 2006 by ACalcutt Andrew Calcutt Http://www.Vistumbler.net Http://www.TechIdiots.net Its not an error, its a undocumented feature
Stumpii Posted January 25, 2006 Posted January 25, 2006 does anyone know how i could use a access mdb file in my script without actually needing access installed? maybe an external DLL call or something?... i haven't done anything with dlls yet...so if its possible any help would be apreciated edit..i see i messed up on the title... MDB...not MDF ... Something like this to get you started: Local $oRS Local $oConn $oConn = ObjCreate("ADODB.Connection") $oRS = ObjCreate("ADODB.Recordset") $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\temp\MyDatabase.mdb") $oRS.Open("Select * FROM Table1", $oConn, 1, 3) MsgBox(1,"", $oRS.RecordCount) $oConn.Close $oConn = 0 Most computers have the drivers necessary for ADO already installed. You can go to M$ if you have any problems there. I think ADO 2.8 is the latest. Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.
ACalcutt Posted January 25, 2006 Author Posted January 25, 2006 (edited) looks promising...i have never used COM before, but i kindof can see whats going on... any reference to ADODB? (like a list with things like ADODB.Connection and ADODB.Recordset) edit: well using the OLE viewer I see about 8 ADODB.(something) items i dont see where "$oRS.RecordCount" came form though can i also write back to the database with this method? Edited January 25, 2006 by ACalcutt Andrew Calcutt Http://www.Vistumbler.net Http://www.TechIdiots.net Its not an error, its a undocumented feature
Stumpii Posted January 25, 2006 Posted January 25, 2006 (edited) looks promising...i have never used COM before, but i kindof can see whats going on... any reference to ADODB? (like a list with things like ADODB.Connection and ADODB.Recordset) edit: well using the OLE viewer I see about 8 ADODB.(something) items i dont see where "$oRS.RecordCount" came form though can i also write back to the database with this method? Read through this and you will be an ADO wizz! Without knowing what you are doing, it is hard to give you any pointers, but basically the recordset is one row of a table (in simple language) with each column called a field. The Recordset has an update method that writes data back to the DB. I will try and knock up an example. Here is some expanded script that reads names/addresses from a DB and allows you to change the address. Note that recordset is opened in such a way that the changes are immediately made to the DB. Local $oRS Local $oConn $oConn = ObjCreate("ADODB.Connection") $oRS = ObjCreate("ADODB.Recordset") $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\temp\MyDatabase.mdb") $oRS.Open("Select * FROM Table1", $oConn, 1, 3) MsgBox(1,"", "There are " & $oRS.RecordCount & " names/addresses.") $oRS.MoveFirst For $iIndex = 1 To $oRS.RecordCount $NewAddress = InputBox("Address Check", $oRS.Fields("Name").value & "'s address is currently: " & _ $oRS.Fields("Address").value & ". Please enter a new address.", $oRS.Fields("Address").value) $oRS.Fields("Address").value = $NewAddress $oRS.MoveNext Next $oConn.Close $oConn = 0 Here is the database that I used. Place in c:\temp, or mod the code. If you don't want to use the DB, then create your own with a 'Table1' table with two colums 'Name' and 'Address'. Stick some data in and run the program.MyDatabase.zip Edited January 25, 2006 by Stumpii Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.
ACalcutt Posted January 25, 2006 Author Posted January 25, 2006 Thanks, that helps Andrew Calcutt Http://www.Vistumbler.net Http://www.TechIdiots.net Its not an error, its a undocumented feature
KenE Posted January 25, 2006 Posted January 25, 2006 (edited) Thanks, Stumpii for the example! As a side note, I've discovered that you can actually have the .mdb open in Access at the same time without any problems, tables will reflect changes made from AutoIt when you close and re-open them in Access. Also, make sure you add some code to handle errors that may occur when you try to put the wrong data type into a field, for example text into a numeric only field. If you don't, AutoIt will bomb with an error, see attached file for error. I'm lazy and I don't feel like re-typing it. Edited January 25, 2006 by KenE
randallc Posted March 12, 2006 Posted March 12, 2006 Hi,I am having great difficulty with this;I can open the example OK, but not my mdb$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$s_database)Can someone simply adjust this command to show UID, PWD too?Thanks, randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
randallc Posted March 12, 2006 Posted March 12, 2006 (edited) Hi,Never mind; its a sure way to get something working!Post a question; next time I tried it, it worked!$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$s_database&';UID=admin;PWD=password')Amazing; i'd tried so many times before..Best, Randall Edited March 12, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
joseedwin Posted June 21, 2006 Posted June 21, 2006 (edited) this is really nice, thanks for sharing it. i was able to use it, but i had a problem if the table/query had a space in the name it would create an error. underscore would not work unless i rename the table and or query. Edited June 21, 2006 by joseedwin
laffo16 Posted July 17, 2006 Posted July 17, 2006 great post, has helped me loads. does anyone know how to Count the columns (field names?, not records) in a table.
Zedna Posted July 17, 2006 Posted July 17, 2006 great post, has helped me loads.does anyone know how to Count the columns (field names?, not records) in a table.Search in forum Scrips & Scraps for ADODB.ConnectionHere is link Resources UDF ResourcesEx UDF AutoIt Forum Search
Dizzy Posted September 2, 2006 Posted September 2, 2006 (edited) Hi Stumpii,this is a very good ('cause it's little) demo for changing values in a mdb.Can you give me (us) 2 more things?How can i add a new entry (Name & Adress) and did i have to add the counter by myself?And how can i delete a full entry?Thanks for your help! DizzyAnd ... Search in forum Scrips & Scraps for ADODB.ConnectionHere is linkwon't be there any more Edited September 2, 2006 by Dizzy
randallc Posted September 3, 2006 Posted September 3, 2006 Hi,You can read/use my functions in a UDF; [with example and UDF linkat that linked post]AccessCom.au3Add ; _CreateTable,_CreateFieldDelete; modify the "_AddData" to a "_DeleteRecord" func?Best, randallLet me know if this helps; ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Dizzy Posted September 4, 2006 Posted September 4, 2006 (edited) Hi Randallc, the link is very good, but there are a few questions more. - the db increased by the second or third start - how can i add a new database set with all 4 statements at one time? (fieldname0 + 1 +2)?? - how can i delete one exact set - how can i increase the next set (i even add the same entry's) I havn't found the functions (answers) yet. Thanks for help! Dizzy Hmmmm - i have to look a little bit deeper in the accesscom.au3 right? Edited September 4, 2006 by Dizzy
randallc Posted September 4, 2006 Posted September 4, 2006 OK, I have made a quick and dirty couple of additions; 1. _AddRecord; seee in example using "|" delimited string for each record 2. _DeleteRecord; in example, deletes record 2 (we can still make a sql Delete function too to delete by matches...) expandcollapse popup;AccessExampleSimpleCopy.au3 0_23 Local $o_Con, $o_Con2, $o_Rs ;~ #include "_GUICtrlListView.au3" ;_108 ;~ #include "Array2D.au3" #include "_ArrayView2D1D.au3" ;DATA======================================================== Global $s_dbname = "c:\test.mdb", $s_Tablename = "table1", $s_Tablename2 = "table2", $Fieldname0 = "txt1", $formatT = "Text(50)", $data0 = "Blah Blah Blah" Global $Fieldname1 = "num1", $formatN = "Number", $data1 = 99, $Fieldname2 = "date1", $formatD = "Date", $data2 = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2) #include"AccessCOM.au3" Local $s_db2name = "c:\test2.mdb", $s_Table2name = "Table8" ;CREATE======================================================== If Not FileExists($s_dbname) Then _CreateDB ($s_dbname, "", "") ;CONNECT======================================================== _AccessConnectConn ($s_dbname, $o_Con, 0) If _TableExists ($o_Con, $s_dbname, $s_Tablename) Then _DropTable ($s_dbname, $s_Tablename, $o_Con) _CreateTable ($s_dbname, $s_Tablename, $o_Con) ;~ _CreateField($s_dbname, $s_Tablename, "ROWID", $formatN, $o_Con) _CreateField ($s_dbname, $s_Tablename, $Fieldname0, $formatT, $o_Con) _CreateField ($s_dbname, $s_Tablename, $Fieldname1, $formatN, $o_Con) _CreateField ($s_dbname, $s_Tablename, $Fieldname2, $formatD, $o_Con) ; Example of how to add data to a single field of a table _AddData ($s_dbname, $s_Tablename, $Fieldname0, "OPEN", $o_Con) _AddData ($s_dbname, $s_Tablename, $Fieldname0, $data0, $o_Con) _AddData ($s_dbname, $s_Tablename, $Fieldname1, $data1, $o_Con) _AddData ($s_dbname, $s_Tablename, $Fieldname2, $data2, $o_Con) ;DISPLAY1======================================================== $query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'" $ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con) ;~ _ArrayViewQueryTable($ar_Rows,$query) _ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1) ; Example of how to list all of the Fields in a particular table ; ****************************************************** ;~ $str = _FieldNames($s_dbname, $s_Tablename, $o_Con) ;~ MsgBox(0, "Fields in " & $s_Tablename, $str) ; Example of how to read the info from all records in one field of a table as a string ; ****************************************************** ;~ $query = "SELECT * FROM " & $s_Tablename ;~ $strData = _ReadOneField($query, $s_dbname, $Fieldname0, $o_Con) ;~ MsgBox(0, $Fieldname0, $strData) ;DISPLAY2======================================================== ;_AddRecord($s_dbname, $s_Tablename1, $ar_array, ByRef $o_adoCon, $ar_FieldFormatsF, $i_adoMDB = 1, $USRName = "", $PWD = "") $s_Row = "col1|260|25/12/2006" _AddRecord ($s_dbname, $s_Tablename, $s_Row, $o_Con) $query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'" ;~ $query = "SELECT RowID, * FROM " & $s_Tablename &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'" $ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con) ;~ _ArrayViewQueryTable($ar_Rows,$query) _ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1) ;DISPLAY2======================================================== ;_DeleteRecord($s_dbname, $s_Tablename1, $i_DeleteCount, ByRef $o_adoCon, $i_adoMDB = 1, $USRName = "", $PWD = "") _DeleteRecord ($s_dbname, $s_Tablename, 2, $o_Con) $query = "SELECT * FROM " & $s_Tablename & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'" ;~ $query = "SELECT RowID, * FROM " & $s_Tablename &" IN '"&$s_dbname&"'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'" $ar_Rows = _RecordSearch ($s_dbname, $query, $o_Con) ;~ _ArrayViewQueryTable($ar_Rows,$query) _ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1)Best, Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Dizzy Posted September 4, 2006 Posted September 4, 2006 Hi! i get errors in line 55 and 63: ERROR: _AddRecord(): undefined function. _AddRecord ($s_dbname, $s_Tablename, $s_Row, $o_Con) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ AddRecord ($s_dbname, $s_Tablename, $s_Row, $o_Con) ERROR: _DeleteRecord(): undefined function. _DeleteRecord ($s_dbname, $s_Tablename, 2, $o_Con) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ _DeleteRecord ($s_dbname, $s_Tablename, 2, $o_Con) Whats wrong with the ref? Greets
randallc Posted September 4, 2006 Posted September 4, 2006 (edited) Hi,Have you put this most recent AccessCOM.au3 0_27from above post #16 into scripdirectory?Randall Edited September 4, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
randallc Posted September 4, 2006 Posted September 4, 2006 (edited) And here is v 0_28 with an Exec func; so easy to delete found records;Best, RandallEdit; AccessCom.zip Edited December 15, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Dizzy Posted September 4, 2006 Posted September 4, 2006 (edited) Yes this works great. Now i can build my db and work with it (hopefully ) THX ! BTW: Do you never sleep? And : 0.28 lets assume me that there will be a lot of work to version 1.00? Edited September 4, 2006 by Dizzy
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