Sign in to follow this  
Followers 0
ACalcutt

Use Access MDF file without access installed?

31 posts in this topic

#1 ·  Posted (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 ;) ... :lmao:

Edited by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites



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 ;) ... :lmao:

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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (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 by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites

#4 ·  Posted (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 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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (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 by KenE

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 by randallc

Share this post


Link to post
Share on other sites

#9 ·  Posted (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 by joseedwin

Share this post


Link to post
Share on other sites

great post, has helped me loads.

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

Share this post


Link to post
Share on other sites

anyone??

Share this post


Link to post
Share on other sites

#13 ·  Posted (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!

:P

Dizzy

And ...

Search in forum Scrips & Scraps for ADODB.Connection

Here is link

won't be there any more :nuke: Edited by Dizzy

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

#15 ·  Posted (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 by Dizzy

Share this post


Link to post
Share on other sites

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

;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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

Yes :):D

this works great. Now i can build my db and work with it (hopefully :P )

THX !

BTW: Do you never sleep? :nuke:

And : 0.28 lets assume me that there will be a lot of work to version 1.00?

Edited by Dizzy

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0