[RESOLVED] ADO to read dbf not working

[DebbieInFlorida]

According to all posts I have seen, this shold work, but of course it doesn't, does anyone see what is wrong....

DBF is on C:\temp\test.dbf


Dim DB As New ADODB.Connection
Dim RS As New ADODB.Recordset

dim sFilePath as string = "C:\temp"

DB.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & sFilePath & ";Extended Properties=DBase IV")


Dim sQuery As String
sQuery = "Select * From test.dbf"

RS.Open(sQuery, DB, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, )

It blows up on the RS.Open line, saying file not found. I know it is there.

Thanks so much.

[techgnome]

Are you using .NET or VB6? You posted in the .NET forum, but posted ADO Classic code.

Also you don't need the .dbf in the table name... it's implied... jsut SELECT * FROM test will be sufficient.

-tg

[DebbieInFlorida]

thanks for your hasty reply. I am in vb.net, VS2005, from the examples I have seen, the code didn't matter.
I did try the .open without the .dbf and still get the message

Microsoft jet engine coud not find the object 'test', make sure it exists or spelled properly.

Do you have a sample of it actually working? otherwise I must find another method to read the dbf file.

THanks

[techgnome]

Yes I have code to open DBF files... but it's ADO.NET code...(and it is at home) but since you are using VS2005, then that will be fine - shouldn't be using ADO.

-tg

[DebbieInFlorida]

thanks, I would really appreciate it. I have been meaning to use ADO.NET anyway, but holding off on it.

[techgnome]

Here's how I read in dbf files using ADO.NET:

    Friend Const _DBF As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=Admin;Password=;Data Source="

        Dim DbfConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(_DBF & Configuration.ArchiveRoot & "\Processing")
        DbfConn.Open()
        Dim DbfCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM dbf_table", DbfConn)
        Dim myDataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(DbfCommand)
        Dim myDataTable As DataTable = New DataTable
        myDataAdapter.Fill(myDataTable)
        DbfConn.Close()

I used _DBF as a constant for the connectionstring, sans the path to the dbf files. I build up that part when I create the connection. In this case Configuration.ArchiveRoot was an application setting that pointed to the folder where I was processing the files from (they were coming to me in zip format, which I then unzipped to the Processing folder, that's where that comes from). "dbf_table" is then the name of the dbf file, in this case dbf_table.dbf ...

-tg

[DebbieInFlorida]

Thanks so much for the snippet, but it is still giving me the same error as before on this line
myDataAdapter.Fill(myDataTable)

Microsoft Database engine could not find object 'Lookup_table'

So it is not the method that is the problem I even tried to open another dbf, just to make sure and same thing. Any other ideas????
Is there another way to read DBF's that may not be as elegant, I just need to read this file.

THanks,

[DebbieInFlorida]

I got it to work!!!!
I copied the file and renamed it, I am not sure what this did, maybe there was something corrupt or whatever, I can't explain, but it now reads all the records.

THanks so much.

[DebbieInFlorida]

FYI for anyone else having similar problem. It was not that the file was corrupt, but the name was too long!! Apparently, there is a limit, not sure if it is 7 or 8, but I cut it to 7 and it works fine.
Crazy waste of energy.
Cheers.

[techgnome]

wow... that's news to me... Thanks for sharing that.

-tg