none
copy a dataset to an access table RRS feed

  • Question

  • i have 3 text files with approx 1500 records, each with different structure and i want to import them to an access database table.
    i thought to write these records to a dataset so that all will have the same structure (the same as the access database table). is there a way to "transfer" or copy the dataset directly to the database tables instead of 1 by 1?
    it takes more than 30 mins to copy the records from 1 file to the table
    if there is a way to do it in sql please also let me know because another person has this problem with sql

    any insight will be much helpfull
    Wednesday, September 2, 2009 8:30 AM

Answers

  • You can copy directly from the text files to the Access database using SQL. A DataSet is not required. Below is an example:

        Function ImportTextToAccess() As Boolean
    
            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Test Files\db1 XP.mdb")
    
            AccessConn.Open()
    
            'New table
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [tbl1] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[tbl1.txt]", AccessConn)
            'Existing table
            'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tbl1] (F1, F2, F3, F4, F5)  SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=d:\My Documents\TextFiles;].[tbl1.txt]", AccessConn)
    
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()
    
        End Function

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Wednesday, September 9, 2009 1:20 AM
    Wednesday, September 2, 2009 2:07 PM

All replies

  • You can copy directly from the text files to the Access database using SQL. A DataSet is not required. Below is an example:

        Function ImportTextToAccess() As Boolean
    
            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Test Files\db1 XP.mdb")
    
            AccessConn.Open()
    
            'New table
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [tbl1] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[tbl1.txt]", AccessConn)
            'Existing table
            'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tbl1] (F1, F2, F3, F4, F5)  SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=d:\My Documents\TextFiles;].[tbl1.txt]", AccessConn)
    
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()
    
        End Function

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Wednesday, September 9, 2009 1:20 AM
    Wednesday, September 2, 2009 2:07 PM
  • Thank you for your help
    Unfortunately i do not need it any more
    But i do appreciate your help.
    Thursday, December 10, 2009 6:24 AM
  • That's OK, others searching for the information can benefit from the answers. :-)
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, December 10, 2009 1:16 PM