Sign in to follow this  
Followers 0
KyleC

ADO DBF connection

14 posts in this topic

#1 ·  Posted (edited)

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 by KyleC

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by GEOSoft

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by Roshith

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

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

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