Sign in to follow this  
Followers 0
amfony

Cant connect to Access Database remotley - can do it locally

6 posts in this topic

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!

Share this post


Link to post
Share on other sites



@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

; 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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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