Use Access MDF file without access installed?

[ACalcutt]

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

[Stumpii]

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.

[ACalcutt]

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

[Stumpii]

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

[ACalcutt]

Thanks, that helps

[KenE]

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]

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

[randallc]

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

[joseedwin]

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]

great post, has helped me loads.

does anyone know how to Count the columns (field names?, not records) in a table.

[laffo16]

anyone??

[Zedna]

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.Connection

Here is link

[Dizzy]

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!

Dizzy

And ...

Search in forum Scrips & Scraps for ADODB.Connection

Here is link

won't be there any more Edited September 2, 2006 by Dizzy

[randallc]

Hi,

You can read/use my functions in a UDF; [with example and UDF linkat that linked post]

AccessCom.au3

Add ; _CreateTable,_CreateField

Delete; modify the "_AddData" to a "_DeleteRecord" func?

Best, randall

Let me know if this helps;

[Dizzy]

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]

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

[Dizzy]

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]

Hi,

Have you put this most recent

AccessCOM.au3 0_27

from above post #16 into scripdirectory?

Randall

Edited September 4, 2006 by randallc

[randallc]

And here is v 0_28 with an Exec func; so easy to delete found records;

Best, Randall

Edit; AccessCom.zip

Edited December 15, 2006 by randallc

[Dizzy]

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