[RESOLVED] Open DBF file

[nexusm]

Hi everyone
This is the code:

Dim dbfFolder$, dbfFile$
dbfFolder = CommonDialog1.InitDir      'dbfFolder is just App.Path without backslash (e.q. "D:\Test"
dbfFile = CommonDialog1.FileTitle        'dbfFile selected is "IE_01-10-2016_01-10-2016_3535.DBF"


Dim CN As ADODB.Connection
Dim RS As New ADODB.Recordset
Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset

CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbfFolder & ";" & _
"Extended Properties=""DBASE IV;"";"

'dbfFile with extension *.DBF
'no brackets[]
sql = "SELECT * FROM " & dbfFile
RS.Open sql, CN, adOpenStatic, adLockOptimistic
'err text: Syntax error in FROM clause.
'sql: SELECT * FROM IE_01-10-2016_01-10-2016_3535.DBF

'dbfFile without extension *.DBF
'no brackets[]
Dim sTbl$
sTbl = Replace(dbfFile, ".dbf", "")
sql = "SELECT * FROM " & sTbl
RS.Open sql, CN, adOpenStatic, adLockOptimistic
'err text: Syntax error in FROM clause.
'sql: SELECT * FROM IE_01-10-2016_01-10-2016_3535

'dbfFile with extension *.DBF
'with brackets[]
sql = "SELECT * FROM [" & dbfFile & "]"
RS.Open sql, CN, adOpenStatic, adLockOptimistic
'err text: The Microsoft Jet database engine
'could not find the object 'IE_01-10-2016_01-10-2016_3535.DBF'.
'Make sure the object exists and that you spell its name
'and the path name correctly.
'sql: SELECT * FROM [IE_01-10-2016_01-10-2016_3535.DBF]

'dbfFile without extension *.DBF
'with brackets[]
sql = "SELECT * FROM [" & sTbl & "]"
RS.Open sql, CN, adOpenStatic, adLockOptimistic
'err text: like last one
'sql: SELECT * FROM [IE_01-10-2016_01-10-2016_3535]

how you see from each comment, I'm using diferent sql statement, without any succes.
what is wrong, where I made mistake.
I realy dont know where is the issue.
thx in advance for any sugestion.

[IkkeEnGij]

what happens if you change the dbf's filename from IE_01-10-2016_01-10-2016_3535.DBF to test.dbf

[nexusm]

Yes, if you mean the lenght of the file, I read on internet many articles regarding this issue.
When I have changed the name of the file (e.g. I kept from the initial name only the first two characters) the error message which I've received was "unrecognized database format" which actually is a strange situation.

[nexusm]

maybe is usejul some detail abaut this file and the entire scenario.
I made a program that has as auxiliary function the release invoices.
accounting department would like that those invoices can be automatically imported.
accounting program can import .xml files (each containing an individul invoice) or DBF files that can contain an entire day bills.
I managed to xml files but because of the structure of accounting software, this can not be linked to an account accountant.
for example we have a product which is called "selling services" and who is assigned to an account accountant, say 405.
xml structure has provided the facility to transmit this account, for which the data is imported correctly, but the product appears against being "undefined" because it lacks binding account accountant.
therefore need to approach the 2nd give way, namely to write a DBF table.

but it seems that we have not much success.
and the dbf file name, comes from long File ID + customer billing period

[Davor Geci]

Hello,

(((the forum didn't let me to upload dbf files so I made a zip file)))

In att is a zip file with 2 files (it is the same file, an old DOS dbf file but I copy and paste it and changed the filename), when I try with it's original filename KONTA.DBF it goes like a baby with your first code

sql = "SELECT * FROM " & dbfFile
RS.Open sql, CN, adOpenStatic, adLockOptimistic

if I try with your filename:
IE_01-10-2016_01-10-2016_3535.DBF

it gives me the error: "Syntax error in FROM clause"

So the error is in filename


(If you don't have the control in creating this files, you can, copy and rename just before you get to the moment when you want to use it)


po01.zip

[nexusm]

I download your zip file and I tryed bouth.
I still received the same error.
I mean "Syntax error in FROM clause"

[IkkeEnGij]

you got that error from both of the .dbf's Davor did put here ?
or only from the IE_01-10-2016_01-10-2016_3535.DBF
please be specific

i tried with Davor's 2 dbf's
to import or link them with access2010
the KONTA.DBF imported/linked without problem
the IE_01-10-2016_01-10-2016_3535.DBF could not be imported or linked
access2010 could not find the file (file not found error)
renamed IE_01-10-2016_01-10-2016_3535.DBF to test.dbf
the test.dbf imported/linked without problem
i have to agree with Davor, the probable cause is the filename
but: since you have an unrecognized database format error when changing to a 8.3 filename
there is a possibility that the file indeed is not a .dbf

but contrary to Davor, who was so helpfull to post 2 .dbf files here
you on the other hand clearly deem it not necessary to put the offending .dbf here

[nexusm]

this is a zip file which contain the original file

[nexusm]

I'm sorry, I made a mistake.
If the filename is only alphanumeric (with no punctuation)
error message returned is not related to the query itself or clauses in it.

Davor's file "KONTA" displays:
"The Microsoft Jet database engine could not find the object 'KONTA'. Make sure the object exists and that you spell its name and the path name correctly"

as the original file after I brought punctuation. (IE01102016011020163535)
with or without extention

What can be done in this case

(once again apologize for the confusion, also when I said "unrecognized database format" I actually tested another table, which was compromised )

[IkkeEnGij]

tried with the file in post#8, renamed to a 8.3 filename (test.dbf)
msaccess 2010 does not recognize that file as a .dbf file
however DBF Viewer 2000 did recognize it
and after DBF Viewer 2000 saved it as a dBASE IV file
msaccess 2010 did recognize it and could import/link it

i think that your file probably has a corrupted header

[nexusm]

I think you're right, it is possible that the header is corrupt.
however, I changed provider driver and I managed to open the Davor file "KONTA"
(CN.Open "Provider=VFPOleDb.1;Data Source=" & dbfFolder)
work great.

so I think the issue is solved

Many thanks for your advice