open dbase files with different file extension

[skeelz]

I am trying to open and read dbase files that have different file extensions. The file extensions output from the program that I am trying to read have the same name but different file extensions depending on the data that is inside them.

example:
123456.env (envelope file)
123456.veh (vehicle information)
123456.ad1 (administrative information)

The error I keep getting is
<filename>.<extension> isn't a valid name.

Does anyone know of a way to open and read these files without first renaming them?

Any help would be appreciated!

 

[JerryKlmns]

Change the registry (by very carefull)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

Modify DisabledExtensions to include your different file extensions.

 

[BobRodes]

I'm looking in the same directory, but under ...\Jet\4.0\ISAM Formats\. Jerry, do you know why you wouldn't have to have some value in here before Jet will recognize your file extension as a dBase file? I notice that dBase is missing from the Engines directory and Paradox isn't. As I remember, dBase was pretty much a text file with special chaacters in it, so I don't really know.

TIA

Bob

 

[skeelz]

Thanks for the suggestions, I really appreciate it.
However niether of these suggestions worked. There is an article on it at microsoft support but it is very old. I tried replacing the # sign and it didn't work.

The program that is exporting these dbf files with different extensions is based on very old technology but is the standard throughout the entire collision industry and is the ONLY format collision estimating software uses and exports

Here is the article:

http://support.microsoft.com/kb/101742/en-us

APPLIES TO
• Microsoft Visual Basic 3.0 Professional Edition

The standard file extension used by dBASE for tables is .DBF. In Visual Basic version 3.0 using the dBASE installable ISAMs, you can open a table by specifying the file name without this extension because the dBASE installable ISAM assumes the extension to be .DBF by default. If you specify the extension <filename>.<extension>, the dBASE installable ISAM will not recognize it and will give you the following error message:
<filename>.<extension> isn't a valid name.

To open a dBASE table file that has a non-standard file extension, specify the table name as <filename>#<extension>. The dBASE installable ISAM interprets the pound sign (#) in the table name as a period and opens the dBASE table.
Back to the top

Example
The following code example demonstrates how to open a dBASE table file that has a non-standard file extension (AUTHORS.OLD) and print the first field of all records in the table to the form. The following example assumes that you have a dBASE III table with a file name of AUTHORS.OLD located in the C:\DBASEIII\OLDBOOKS directory. You may need to modify the example and create a dBASE III database with a table called AUTHORS.OLD in order for it to work correctly.

1. Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.
2. Add a Command Button (Command1) to Form1.
3. Add the following code to the Click event of Command1: Sub Command1_Click()
Dim db As Database
Dim OldAuthors As Table

Connect$ = "dBASE III" ' Specify database type
dbName$ = "C:\DBASEIII\OLDBOOKS" ' Specify database directory

Set db = OpenDatabase(dbName$, False, False, Connect$)
Set OldAuthors = db.OpenTable("Authors#Old") ' Open table
While Not OldAuthors.EOF
Print OldAuthors(0) ' Print field(0) to the form
OldAuthors.MoveNext ' for all records.
Wend

OldAuthors.Close
db.Close
End Sub


4. Run the example.
5. Click the Command1 button.

APPLIES TO
• Microsoft Visual Basic 3.0 Professional Edition


Basically I need a connection string that will work in visual studio 2005, programming language is C#, and open .dbf files with different file extensions.

All help is greatly appreciated!

 

[strongm]

>work in visual studio 2005, programming language is C#,

Which puts you in the wrong forum ... try forum796

 

[BobRodes]

Or perhaps forum732. That said, however, I would try including the file extension in my SQL SELECT statement, e. g. "Select * from 123456.veh". Have you tried that yet?

 

[Arcance]

Hi, I am the programmer for the project at hand, maybe I can shed some more light on the subject.

I have tried OleDB, ODBC, ADO and DAO, all open the files just fine as long as they have a ".dbf" extension. The drivers all apparently do not care what extension you put in the code, they only look for .dbf. You can even leave of the extension from the file all together in the code.

Here is a sample of the code I am using..it is in .net but the same principals apply and since this thread is already started I will go ahead and post it here.

The oleDB version

string conString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "C:\\Pathways Output" + ";Extended Properties=dBase IV";

            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [0CE52VAI#ENV]", conn);
            da.Fill(ds);
            conn.Close();
            DataTable dt = ds.Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                uniqueFileID = (dr["UNQFILE_ID"].ToStrin());
                
            }//end foreach
            MessageBox.Show(uniqueFileID);

The DAO version, which I hoped would work since it is older technology

 DAO.DBEngine dbe = new DAO.DBEngine();
            DAO.Workspace ws = dbe.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
            DAO.Database db;
            DAO.Recordset rst;
            string connectType, dbName;

            connectType = "dBASE III";
            dbName = "C:\\Pathways Output";

            db = ws.OpenDatabase(dbName, false, false, connectType);
            rst = db.OpenRecordset("0CE52VAI#ENV",DAO.RecordsetTypeEnum.dbOpenTable,0,DAO.LockTypeEnum.dbOptimistic);
            while (!rst.EOF){
            MessageBox.Show("It Works"); 
            rst.MoveNext(); 
            } 

            rst.Close();
            db.Close();
            ws.Close();
            dbe = null;

I am using the "#" in place of the "." since Microsoft said that would work, however, I get the same result using either.

Thanks for the input.

 

[BobRodes]

Ok, got something working here. First, I went to NWIND.MDB and exported the Customers table to c:\temp\customer.dbf. I then renamed customer.dbf to customer.abc. Now, this code:

Option Explicit
Dim rs As DAO.Recordset
Dim db As DAO.Database

Private Sub Form_Load
Set db = DAO.DBEngine.OpenDatabase("c:\temp", False, False, "Dbase III")
Set rs = db.OpenRecordset("select * from customer#abc")
Stop
End Sub

This code does NOT work with a reference to "Microsoft DAO 3.6 Object Library". This code DOES work with a reference to "Microsoft DAO 3.51 Object Library", and also with a reference to "Microsoft DAO 2.5/3.5 Compatibility Library". The latter two also work if you replace "Dbase III" with "Dbase IV".

I spent a lot of time trying to find a solution with OLE DB, and couldn't find one.

Anyway, perhaps you're using the latest and greatest version of DAO, which appears to have nerfed this capability. I tried using first the version that was around with VB 3.0 and moving up from there.

HTH

Bob

 

[dilettante]

All I have handy is a file [tt]fox_samp.dbf[/tt] but I renamed it [tt]sample.abc[/tt] and placed it into the VB6 project directory for testing.

Option Explicit

Private Sub Form_Load()
    Dim rsDBF As New ADODB.Recordset
    Dim intFld As Integer
    
    With rsDBF
        .Open "SELECT * FROM [fox_samp IN sample.abc]", _
              "Provider=Microsoft.Jet.OLEDB.4.0;" _
              & "Data Source=""" & App.Path & """;" _
              & "Extended Properties=dBase IV", _
              adOpenForwardOnly, adLockReadOnly, adCmdText
        For intFld = 0 To .Fields.Count - 1
            txtLog.Text = txtLog.Text _
                        & .Fields.Item(intFld).Name & vbNewLine
        Next
        .Close
    End With
End Sub

Seems to work fine here.

 

[Arcance]

Very interesting guys, and thanks alot for the help. I think we are tracking down the issue here.

Bob I am indeed using the DAO 3.6 library and so that is aparently the problem.

I didi not have version 3.51 on this computer so I downloaded the .dll and put it in the same folder that the 3.6 version is in, yet it does not show up in my references list even after a reboot.

I also tried putting it in the Windows/System directory.

So I have the 3.51.dll in the "C:\Program Files\Common Files\Microsoft Shared\DAO" directory and in the "C:\Windows\System" directory, but it isn't showing up in the references list.

Do I need to put it somewhere else or do something else to get it to show up?

Something else I noticed that is odd....I deleted the 3.6 version in hopes that the 5.1 version woould show up but when I go back to the folder the 3.6 version is back! Hrmm, wierd.

I hope this isn't like DirectX where once you have a newer version you can never go back.

Thanks for the help guys!

 

[JerryKlmns]

Bob,

just got back here. To answer your question, I don't. But, I notice that at the "ISAM Format" of dBase III, IV & 5 the "Engine" is Xbase, which on the other hand does exist in "Engines"

 

[SBerthold]

Arcance,

You also need the JET 3.5 files, otherwise I believe DAO will not load.

http://support.microsoft.com/kb/172733/en-us

DAO350 needs to be registered with regsvr32.

DAO360 cannot be deleted because it is protected (if deleted, the system will copy it back from the dll cache)

 

[BobRodes]

<Seems to work fine here.

Arcance, you may want to go with dilettante's suggestion. I looked for some sort of syntax that would work for a while, and didn't find one. I'd prefer to use ADO if I could.

Now, it looks like you didn't register your downloaded dll, and you have to do that. Go to the command prompt, navigate to your directory, and type [tt]regsvr32 mydll.dll[/tt]. It should then show up in your references list. If it doesn't, you can hit the browse button in references and browse to it.'

HTH

Bob

 

[BobRodes]

Darn it all! dilettante, I'm trying to use your idea and getting the same error I've always gotten, can't find object. Here's my version:

Dim strCn As String

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp;Extended Properties=dBASE IV;User ID=Admin;Password=; "

Set cn = New ADODB.Connection
cn.Open strCn
Set rs = New ADODB.Recordset
With rs
    .Open "select * from [customer in c:\temp\customer.abc]", cn
End With
Stop
rs.Close
End Sub

Can you spot anything wrong?

 

[dilettante]

I'm stumped.

I tried pasting your code above in, and altered it to reflect the sample .DBF file I have on hand here and declared the two object variables. I got the same error you did.

I fiddled and fiddled, and got it to work.

I had no idea how mine differed significantly, so I pasted your code back in and made the original minor changes. Now it works:

Private Sub Form_Load()
Dim strCn As String
[COLOR=red]Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset[/color]

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp;Extended Properties=dBASE IV;User ID=Admin;Password=; "

Set cn = New ADODB.Connection
cn.Open strCn
Set rs = New ADODB.Recordset
With rs
    .Open "select * from [[COLOR=red]fox_samp[/color] in c:\temp\[COLOR=red]sample.abc[/color]]", cn
End With
Stop
rs.Close
End Sub

This also works:

Private Sub Form_Load()
Dim strCn As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp;Extended Properties=dBASE IV;User ID=Admin;Password=; "

Set cn = New ADODB.Connection
cn.Open strCn
[COLOR=red]Set rs = cn.Execute("select * from [fox_samp in c:\temp\sample.abc]", , adCmdText)[/color]
Stop
rs.Close
End Sub

As does:

Private Sub Form_Load()
Dim strCn As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp;Extended Properties=dBASE IV;User ID=Admin;Password=; "

Set cn = New ADODB.Connection
cn.Open strCn
Set rs = cn.Execute("select * from [fox_samp in [COLOR=red]sample.abc[/color]]", , adCmdText)
Stop
rs.Close
End Sub

And even:

Private Sub Form_Load()
Dim strCn As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp;Extended Properties=dBASE IV;User ID=Admin;Password=; "

Set cn = New ADODB.Connection
cn.Open strCn
Set rs = cn.Execute("[COLOR=red][fox_samp in sample.abc][/color]", , [COLOR=red]adCmdTable[/color])
Stop
rs.Close
End Sub

I have no clue why your code (with small changes) didn't work for me the first time.


Ok, back to the drawing board.

Disregard all of the above! In between the two attempts I had copied the [tt]fox_samp.dbf[/tt] into [tt]c:\temp[/tt] and that's what I was picking up on all my subsequent efforts.

Sheesh.

I'll take another look.

 

[strongm]

I'm pretty certain that I recall reading somewhere that Jet and the Dbase ISAM wouldn't work with non-standard Dbase file-extensions (the VB3 article notwithstanding). Trouble is I can't find the article where I read that ...

 

[dilettante]

Yes, my earlier post was flawed in exactly the same way.

 

[dilettante]

Ok, I have a solution... you might not want to use it, but it works.

I had to copy my [tt]fox_samp.dbf[/tt] to [tt]c:\temp\fox_samp.abc[/tt] because I was not able to get the table name separated from the file name syntax via [tt]IN[/tt].

I also had to install Microsoft OLE DB Provider for Visual FoxPro 9.0, a 2.5MB download.

Private Sub Form_Load()
    Dim strCn As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    strCn = "Provider=vfpoledb.1;Data Source=c:\temp;Collating Sequence=machine;"

    Set cn = New ADODB.Connection
    cn.Open strCn
    Set rs = New ADODB.Recordset
    rs.Open "select * from [fox_samp.abc]", cn
    Stop
    rs.Close
End Sub

Other logic variations should work as well this way. For example this of course works:

Private Sub Form_Load()
    Dim strCn As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    strCn = "Provider=vfpoledb.1;Data Source=c:\temp;Collating Sequence=machine;"
    
    Set cn = New ADODB.Connection
    cn.Open strCn
    Set rs = cn.Execute("[fox_samp.abc]", , adCmdTable)
    Stop
    rs.Close
End Sub

Sorry for my blunder earlier. I really had myself fooled.

 

[BobRodes]

Wow. I kind of had the feeling that Foxpro could find a way to handle this requirement. Anyway, I did the same thing and everything worked for me too. Personally, then, I would recommend using the Foxpro OLE DB provider and ADO over the older version of DAO solution, since it's more current.

Bob