none
create table in SQL Server 2005 RRS feed

  • Question

  • I am developing a project where i have to copy a table from ms-Access to ms-SQL Server, for which i wrote a code and it goes like this

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim cnn As New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=MyDB;Integrated Security=SSPI")

    Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Form1.OpenFileDialog1.FileName)

    cn.Open()

    Dim MDBCommand As New OleDbCommand("Select * FROM [ TableName ]", cn)

    Dim rdr2 As OleDbDataReader

    rdr2 = MDBCommand.ExecuteReader()

    Dim sbc As New SqlBulkCopy("Data Source=.\SQLExpress;Initial Catalog=MyDB;Integrated Security=SSPI")

    sbc.DestinationTableName = "Test"  ''''''Test Table already present in SQL Server

    sbc.WriteToServer(rdr2)

    sbc.Close()

    rdr2.Close()

    cn.Close()

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Here every thing is predecided, i.e i have already created a table in SQL Server with name Test and Assigned fields same as that of access.

    But i want to create a table in SQL Server 2005 dynamically with same table parameters or Structure of Access table

     

     

     

    Wednesday, June 8, 2011 9:30 AM

Answers

  • As you found out in your other post, SQLBulkCopy will not dynamically create a table in SQL Server. A shortcut method for doing this is to use a SQL SELECT INTO 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("SELECT * INTO [Test] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] FROM [Tablename]", AccessConnection)
    
    
    
      AccessCommand.ExecuteNonQuery()
    
      AccessConnection.Close()
    
    
    
    


    Note that this method will not create table constraints, such as a primary key. You will need to SQL DDL/DML for that:

    ALTER TABLE Persons
    
    ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)	
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, June 9, 2011 11:44 AM

All replies

  • how to Send access tables to SQL Server from VB.net
    Saturday, June 4, 2011 9:36 AM
  • Amit,

    I'm not sure if you ask this, but have a look at this sample.

    http://www.vb-tips.com/SQLServerUpdate.aspx


    Success
    Cor
    Saturday, June 4, 2011 10:03 AM
  • On 6/4/2011 5:36 AM, amit_kumar wrote:
    > how to Send access tables to SQL Server from VB.net
     
    You can send Access table data to SQL Server table from VB.NET yo using
    ADO.NET.
     
     
    You can use Bing or Google and find more examples.
     
    Saturday, June 4, 2011 1:33 PM
  • If you mean import/export database tables between databases, you can do it via CSV, XML or TXT files. 

    Check this article http://www.codeproject.com/KB/database/Cs_CSV_import_export.aspx C# - CSV Import Export.

    Saturday, June 4, 2011 5:28 PM
  • Never Mind i got the solution

     

    Public Sub ImportAccess()

            Dim cnn As New SqlConnection("Data Source=ServerName;Initial Catalog=Databasename;Integrated Security=True")

            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Form1.openFileDialog1.FileName)

            cn.Open()

            Dim MDBCommand As New OleDbCommand("Select * FROM [TableName]", cn)

            Dim rdr2 As OleDbDataReader

            rdr2 = MDBCommand.ExecuteReader()

            Dim sbc As New SqlBulkCopy("Data Source=Servername;Initial Catalog=DatabaseName;Integrated Security=True")

            sbc.DestinationTableName = "Test"               'this is the SQL Server table name 

            sbc.WriteToServer(rdr2)

           'close all opened connections

        End Sub

     


    • Proposed as answer by Cor Ligthert Wednesday, June 8, 2011 7:32 AM
    Wednesday, June 8, 2011 6:55 AM
  • Moderators will you merge this thread with this by Amit himself answered and is by me proposed as answer.

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/bb7e4cb3-48e1-40fd-a00b-91bbe769d3b8/#ff79c465-95dd-4d19-816f-098ed011f51b


    Success
    Cor
    Thursday, June 9, 2011 9:00 AM
  • As you found out in your other post, SQLBulkCopy will not dynamically create a table in SQL Server. A shortcut method for doing this is to use a SQL SELECT INTO 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("SELECT * INTO [Test] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] FROM [Tablename]", AccessConnection)
    
    
    
      AccessCommand.ExecuteNonQuery()
    
      AccessConnection.Close()
    
    
    
    


    Note that this method will not create table constraints, such as a primary key. You will need to SQL DDL/DML for that:

    ALTER TABLE Persons
    
    ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)	
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, June 9, 2011 11:44 AM