none
Copy SQL Table to Access or vice versa RRS feed

  • Question

  • Copy SQL Table to Access in vb only or vice versa
    can anybody will help me to copy access data to sql table , its urgent.
    thanks in advance
    Wednesday, July 15, 2009 9:21 AM

Answers

  • To copy from Access to SQL Server you can use the SQLBulkCopy Class.

            Dim SQLConnectionString As String = "Data Source=(local);" & _
                                "Initial Catalog=Northwind;" & _
                                "Integrated Security=SSPI"
    
            ' Open a connection to the AdventureWorks database.
            Using SourceConnection As SqlConnection = _
               New SqlConnection(SQLConnectionString)
                SourceConnection.Open()
    
                ' Perform an initial count on the destination table.
                Dim CommandRowCount As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.Orders2;", _
                    SourceConnection)
                Dim CountStart As Long = _
                   System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                Console.WriteLine("Starting row count = {0}", CountStart)
    
                ' Get data from the source table as a AccessDataReader.
                Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                            "Data Source=C:\Test Files\db1 XP.mdb")
    
                Dim AccessCommand As New OleDbCommand("SELECT * FROM Orders2", AccessConnection)
                AccessConnection.Open()
                Dim AccessDataReader As OleDbDataReader
                AccessDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
    
                ' Open the destination connection. 
    Using DestinationConnection As SqlConnection = _ New SqlConnection(SQLConnectionString) DestinationConnection.Open() ' Set up the bulk copy object. ' The column positions in the source data reader ' match the column positions in the destination table, ' so there is no need to map columns. Using BulkCopy As SqlBulkCopy = _ New SqlBulkCopy(DestinationConnection) BulkCopy.DestinationTableName = _ "dbo.Orders2" Try ' Write from the source to the destination. BulkCopy.WriteToServer(AccessDataReader) Catch ex As Exception Console.WriteLine(ex.Message) Finally ' Close the AccessDataReader. The SqlBulkCopy ' object is automatically closed at the end ' of the Using block. AccessDataReader.Close() End Try End Using ' Perform a final count on the destination table ' to see how many rows were added. Dim CountEnd As Long = _ System.Convert.ToInt32(CommandRowCount.ExecuteScalar()) Console.WriteLine("Ending row count = {0}", CountEnd) Console.WriteLine("{0} rows were added.", CountEnd - CountStart) End Using End Using

    It's also possible to use a SQL statement:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                "Data Source=c:\test files\db1 XP.mdb")
    
            AccessConnection.Open()
    
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Orders2] SELECT * FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes]")
            AccessCommand.ExecuteNonQuery()
            AccessConnection.Close()
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 15, 2009 1:34 PM
  • Related thread:

    http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/1453b7e0-0d85-4b1a-a72a-f56f57af719a
    Wednesday, July 15, 2009 1:55 PM

All replies

  • hi,

    You can do this with Import and Export functionality of SQL Server.

    Regards,
    Vinil
    Wednesday, July 15, 2009 9:41 AM
  • To copy from Access to SQL Server you can use the SQLBulkCopy Class.

            Dim SQLConnectionString As String = "Data Source=(local);" & _
                                "Initial Catalog=Northwind;" & _
                                "Integrated Security=SSPI"
    
            ' Open a connection to the AdventureWorks database.
            Using SourceConnection As SqlConnection = _
               New SqlConnection(SQLConnectionString)
                SourceConnection.Open()
    
                ' Perform an initial count on the destination table.
                Dim CommandRowCount As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.Orders2;", _
                    SourceConnection)
                Dim CountStart As Long = _
                   System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                Console.WriteLine("Starting row count = {0}", CountStart)
    
                ' Get data from the source table as a AccessDataReader.
                Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                            "Data Source=C:\Test Files\db1 XP.mdb")
    
                Dim AccessCommand As New OleDbCommand("SELECT * FROM Orders2", AccessConnection)
                AccessConnection.Open()
                Dim AccessDataReader As OleDbDataReader
                AccessDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
    
                ' Open the destination connection. 
    Using DestinationConnection As SqlConnection = _ New SqlConnection(SQLConnectionString) DestinationConnection.Open() ' Set up the bulk copy object. ' The column positions in the source data reader ' match the column positions in the destination table, ' so there is no need to map columns. Using BulkCopy As SqlBulkCopy = _ New SqlBulkCopy(DestinationConnection) BulkCopy.DestinationTableName = _ "dbo.Orders2" Try ' Write from the source to the destination. BulkCopy.WriteToServer(AccessDataReader) Catch ex As Exception Console.WriteLine(ex.Message) Finally ' Close the AccessDataReader. The SqlBulkCopy ' object is automatically closed at the end ' of the Using block. AccessDataReader.Close() End Try End Using ' Perform a final count on the destination table ' to see how many rows were added. Dim CountEnd As Long = _ System.Convert.ToInt32(CommandRowCount.ExecuteScalar()) Console.WriteLine("Ending row count = {0}", CountEnd) Console.WriteLine("{0} rows were added.", CountEnd - CountStart) End Using End Using

    It's also possible to use a SQL statement:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                "Data Source=c:\test files\db1 XP.mdb")
    
            AccessConnection.Open()
    
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Orders2] SELECT * FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes]")
            AccessCommand.ExecuteNonQuery()
            AccessConnection.Close()
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 15, 2009 1:34 PM
  • Related thread:

    http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/1453b7e0-0d85-4b1a-a72a-f56f57af719a
    Wednesday, July 15, 2009 1:55 PM