which is faster ?

[leemin]

hi, does any one know which method is faster if I want to extract a set of records from a dBase III file and insert in a table of a .mdb ?
A - run a SQL query within the .mdb to extract records from dBase file (is it possible ?)

B - open both the dBASE and access table, and add the dbase record to the table in a 'do while loop'

Really apprec. if you had experience in this or come across a possible solution.

[Chris]

Sure SQL is much faster than the DO...loop

INSERT INTO newtable SELECT * FROM [C:\old.mdb].[oldtable];

[leemin]

hi chris, thanks for the reply.
But I'm trying to append the records from a dBASE into a table in a .mdb
Logically the sql string should work, but don't think it apply to my problem. I don't think by using a sql statement , vb will know which type of database it's dealing with.
maybe there's more to the sql string, care to elabrate ?

[Chris]

May be you can try to use ADO to open the dBASE database & then insert it into a new Access database. Since ADO can open different type of database.

[leemin]

hi chris, i am new in vb. do you think can help with sample codes with a progress bar ?
thanks for the time.

[Chris]

I'll try, but i need your dBase database. Do you mind to email ma a sample dBase database? [email protected]

[Chris]

Hi leemin, I just come out some code that base on the database that you email me. My code will create a new access database with the name "Txn.mdb and a table "TXN " as well. Then Just caopy all the records from the dBase III database into the newly create MS Access database.

I assume you have DAO 3.6 object ready in your development PC.

So what you need to do is just reference your project to either one of this object Project|Reference and paste the following code into it.

Option Explicit
Private dBaseOldDb As DAO.Database
Private dBaseNewDb As DAO.Database
Private dBaseTblFields As DAO.TableDef
Private dBaseTblDefs As DAO.TableDefs

Private sql1$
Private sql2$
Private TblCnt%
Private FldCnt%
Private TempStr$
Public Sub Main()
'STEP 1
'Create a new Access database
Set dBaseNewDb = DBEngine.CreateDatabase(App.Path & "\Txn.mdb", dbLangGeneral)
dBaseNewDb.Close
Set dBaseNewDb = DBEngine.OpenDatabase(App.Path & "\Txn.mdb", False, False)

'*******************************************************************************
'Note: (This quote take from MSDN Library January 2000 release.)

'The Example column lists the drive and the path, but not the file name.
'A file name is not required for these database formats because the folder
'(directory) in which the files are stored is considered to be the database.
'If you do list a file name, you may receive unexpected results.

'For example, to open a Microsoft FoxPro file named Employee.dbf located in
'the C:\Foxnwind folder, use the following syntax

'For more info please refer to the http://msdn.microsoft.com/library/default.asp
'*******************************************************************************

'STEP 2
'Open the Txn.dbf dBase database
Set dBaseOldDb = OpenDatabase(App.Path, False, False, "dBASE III;")
'Get all the table name from dBase database
Set dBaseTblDefs = dBaseOldDb.TableDefs
For TblCnt = 0 To dBaseTblDefs.Count - 1
    sql1 = "CREATE TABLE " & dBaseTblDefs(TblCnt).Name & " ("
    
    'Get all the Fields detail infomation from the open table
    Set dBaseTblFields = dBaseOldDb(dBaseTblDefs(TblCnt).Name)
        sql2 = ""
        For FldCnt = 0 To dBaseTblFields.Fields.Count - 1
            'Assume all fields is set to TEXT datatype.
            sql2 = sql2 & dBaseTblFields.Fields(FldCnt).Name & " TEXT (" & dBaseTblFields.Fields(FldCnt).Size & "), "
        Next
        'Get the reference field name
        TempStr = dBaseTblFields.Fields(FldCnt - 1).Name
        
        If sql2 <> "" Then
            sql2 = Left(sql2, Len(sql2) - 2)
            sql1 = sql1 & sql2 & ");"
            'Create table in new MS Access database
            dBaseNewDb.Execute sql1
            
            'STEP 3
            'Copy the records from old dBase databse into new create MS Access database
            sql1 = "INSERT INTO TXN IN '" & App.Path & "\Txn.mdb' 'Databse' " & _
                   "SELECT * FROM " & dBaseTblDefs(TblCnt).Name & " IN '" & App.Path & "' 'dBASE III;' " & _
                   "WHERE Len(" & TempStr & ") <> 0;"
            dBaseOldDb.Execute sql1
        End If
    Set dBaseTblFields = Nothing
Next
Set dBaseTblDefs = Nothing
dBaseOldDb.Close
dBaseNewDb.Close
Set dBaseOldDb = Nothing
Set dBaseNewDb = Nothing
MsgBox "Data transfer completed."
End Sub

Currently, this is using DAO3.6 object. And it just take few seconds to create and copy all the records into the new MS Access database. I think this will help you too.

May be later I'll come out another copy of code that using ADO 2.1 object.

Note: you must have the Txn.dbf dBase III databse under the same project directory.





[Edited by Chris on 06-08-2000 at 01:44 PM]