Cant connect to Access Database remotley - can do it locally

[amfony]

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!

[ptrex]

@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

[DaleHohm]

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

[amfony]

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

[DaleHohm]

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

[IvanCodin]

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