tough one for you experts out there...

[softwareguy74]

tough one for you experts out there

Any idea why Command2 will not work but Command1 will?

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]

[softwareguy74]

Sorry about not using the code tags.. I usually do.. I edited the post to include the tags so it's more legible now..

Anyway, to answer DerFarm, even when invoking Command2 before Command1, I get the same error..

It has something to do with the fact that Command2 is trying to use a text file, I guess. Because that's the only difference from Command1.

I know that the method I use in Command2 is correct as far as connecting to the text file as a database but I think the problem is in the SQL syntax because it's not a real database.. Maybe I have to modify the connect string?

Any help would be appreciated..

Dan

[softwareguy74]

Thanks for your reply, however, I'm not familiar with creating "sessions". Using the code that I posted, can you give me a quick example of how I would create an additional session?

any help you can provide would be appreciated...

Dan