tough one for you experts out there

Any idea why Command2 will not work but Command1 will?

Code:
Private txtDb As ADODB.Connection
Private Db As ADODB.Connection

Private Sub Form_Load()
    Dim strCn As String
    
    '-- set connection to acces db
    strCn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data.mdb;"
    
    Set Db = New ADODB.Connection
    Db.Open strCn
    
    '-- set connection to text file (courtesy: [email protected])
    strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
            "DBQ=C:\;" & _
            "DefaultDir=" & App.Path & ";" & _
            "Uid=Admin;Pwd=;"

    Set txtDb = New ADODB.Connection
    txtDb.Open strCn
    
End Sub
    

Private Sub Command1_Click()
    Dim strSQL As String
    
    strSQL = "INSERT INTO test ( FName, LName ) IN 'c:\test.mdb' " _
        & "SELECT data.FName, data.LName " _
        & "FROM data"
    
    '-- append data from table data (data.mdb) to table test (test.mdb)
    Db.Execute strSQL
    
End Sub

Private Sub Command2_Click()
    Dim strSQL As String
    
    strSQL = "INSERT INTO test ( FName, LName ) IN 'c:\test.mdb' " _
        & "SELECT data.FName, data.LName " _
        & "FROM data"
    
    '-- append data from table data (data.txt) to table test (test.mdb)
    txtDb.Execute strSQL
    
End Sub
Any idea on how to get Command2's code to work? It keeps telling me: "The Microsoft Jet database engine cannot open the file '(unkown)'...

In order to use my example, you will also need the following:

1) A Schema.ini file located in c:\ with the following contents:

[DATA.TXT]
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1=FName char width 50
Col2=LName char width 3

2) A text file named data.txt with 2 columns (seperated by commas) (needs to be populated with data)

3) An Access database named c:\data.mdb with a table named data with the 2 fields (FName,LName) (needs to be populated with data)

4) An Access database named c:\test.mdb with the same fields as data.mdb.

Any and all help that you can give me to get Command2 to append all data from data.txt
into test.mdb would be appreciated.

Please do not give me any other examples such as reading in the text file and then splitting the data and then putting it into the db via ADO.. I want to avoid that as it is way too slow.. Appending data (if I can get it to work, should be lightening quick...)

DAn

[Edited by dbassettt74 on 09-12-2000 at 02:46 PM]