ADO DBF connection

[KyleC]

Alright,

I am new to Autoit, they use it where I work, so I am picking it up. I have some experience with Java and php. anyway.

I am trying to read the field names from a dbf file in order to select the proper import template in a program we use. If anyone has any suggestions as to how to do this better, I am in no way attached to my current process. So, right now, I am not sure if any part of this is working correctly or not, but I will pass along what I have so far.

$folder = "C:"
$Conn = ObjCreate("ADODB.Connection")
$conn.open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = "& $folder&';Extended Properties="DBASE IV";')


$rs = ObjCreate("ADODB.recordset")
$rs.open ('"Select * from press.dbf"', $conn)

$x = ObjCreate("adodb.record")

dim $fields[$rs.fields.count]
$i=0
do 
    for $x in $rs.Fields
       $fields[$i]=($x.name)
       $i += 1
    next    
until $rs.EOF

I get an error that reads:

C:\Documents and Settings\kylec\Desktop\AutoIt\Scratch\ADO_DBF.au3 (7) : ==> The requested action with this object has failed.:

$rs.open ('"Select * from ' & '"press.dbf"' & '"', $conn)

$rs.open ('"Select * from ' & '"press.dbf"' & '"', $conn)^ ERROR

I just need the field names in an array. which I was planning on doing with a for...in...next but I am unsure as to how I would set that up to work properly.

Thanks.

Edited June 1, 2011 by KyleC

[GEOSoft]

Depending on the version you are using the provider string may be wrong.

http://dundats.mvps.org/autoit/udf_code.aspx?udf=access

Don't try to copy paste the fuunctions; just download the zip package and in the Constants file you will find a function at the top that should get you the correct provider string.

Edit: Also that $rs.open line is wrong I think (I've been wrong before). The Select should be pointing to the table, not the file so take a look at the functions in the UDf to see how to handle that.

NOTE: The udf is no longer supported because I have no way to test it now.

Edited June 1, 2011 by GEOSoft

[KyleC]

That would appear to be for Access, I am using dbase files. Could I use access?

[Zedna]

1) Add error handler

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    

; This is my custom defined error handler
Func MyErrFunc()

  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
            
Endfunc

2) look here for similar approach with ADO

3) search forum for more examples

[Roshith]

Try this out

#include<Array.au3>
Dim $array[100], $iCount = 0
$DBSystem = ObjCreate("DAO.DBEngine.36")
$db = $DBSystem.OpenDatabase("C:\Master_db.mdb")
$dbtable = $db.OpenRecordset("SELECT * FROM [tablename]")
$dbtable.movefirst
While $dbtable.eof <> -1
    $iCount += 1
    $array[$iCount] = $dbtable.fields(0).value
    $dbtable.movenext
WEnd
$db.close
_ArrayDisplay($array)

Declare the array as per the number of records in teh database

Edited June 1, 2011 by Roshith

[Roshith]

Oh sorry, you are looking to get the field names, This would work for access

For $i = 1 To $dbtable.fields.count-1
    ConsoleWrite($dbtable.fields($i).name&@CRLF)
Next

[KyleC]

Oh sorry, you are looking to get the field names, This would work for access

For $i = 1 To $dbtable.fields.count-1
    ConsoleWrite($dbtable.fields($i).name&@CRLF)
Next

Yea, my loop I have is similar. I just can't get the ADO connection with my dbf file to work.

[Roshith]

The connection which I created above using "ObjCreate("DAO.DBEngine.36")" works well with mdb database, if you are using access

[KyleC]

Yea, my loop I have is similar. I just can't get the ADO connection with my dbf file to work.

for $x in $rs.Fields
    $fields[$i]=($x.name)
    $i += 1
    ConsoleWrite($x.name)
next

[KyleC]

the problem is that we get DBF files form a client that we need to turn around quickly. they have a number of different formats that they send to us. If I can get a connection that will allow me to get my field names that would allow me to select the proper import templates based on the layout. of course I could jump through some hoops and transfer/import the DBase table into access and link to that, but it seems like an unnecessary step.

[GEOSoft]

The part that I wanted you to check is the

$conn.open("Provider = Microsoft.Jet.OLEDB.4.0

That's why I suggested you download the zip for that udf and look at the function in the Constants file that sets the provider.

[KyleC]

I see, yea, I downloaded that, and the jet string seems to be identical. I got mine from connectionstrings.com not sure what else I need to do, I have been fiddling with it but it just seems to not be able to find the table in the folder.

[GEOSoft]

Set up an error handler as someone showed you above and see what it returns for the error. That should tell you where the problem lies and then we can go from there.

[KyleC]

I have never used an error handler before. so that is new to me. I will have to figure that out.