none
Duplicar tabla Access Por Codigo VB.NET RRS feed

  • Pregunta

  • Hola a todos, necesito de su ayuda, estoy en un proyecto de Inventario, en donde todos los meses se debe hacer “cierre de mes”. Para ello los productos (o registros del mes anterior guardados en la tabla abril) que quedaron del mes pasado (Ejemplo Abril), serán los productos para el mes siguiente (Mayo).

    Para ello el usuario es el encargado de cerrar el mes, Mediante un botón o evento click, para ello necesito saber como es posible Duplicar la tabla “Abril” (datos y estructura) con un nuevo nombre “Mayo”, dentro de una misma base de datos Access .accdb, me urge su ayuda.

    Esperando su respuesta, les agradece de antemano Hernan Brito.

    viernes, 3 de mayo de 2019 6:01

Respuestas

  • Puedes usar un Select que te copie la tablaorigen como tabladestino :

    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\carpeta\bd.mdb"
    
    Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
    AccessConnection.Open()
    
    Dim AccessCommand = New System.Data.OleDb.OleDbCommand("SELECT * INTO [NombreTablaDestino] FROM [NombreTablaOrigen]", AccessConnection)
    AccessCommand.CommandType = CommandType.Text
    AccessCommand.ExecuteNonQuery()
    AccessConnection.Close()


    Saludos, Javier J

    viernes, 3 de mayo de 2019 6:50
  • Hola:

    De otra forma un poco mas larga y con bastante mas código, pero que puede valer (modificando algo)cuando las tablas están en diferentes bases.  

    En un Form con 1 Button, copia y pega el siguiente codigo

    Option Explicit On
    Option Strict On
    Imports System.Data.OleDb
    Imports System.Text
    Public Class Form1
        Private msCadenaACCESS As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\BD.accdb"
        Private mdtDataTable As New DataTable
        Private msTabla As String = "XXXXX"
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                Using loConexionAccess As New OleDbConnection(msCadenaACCESS)
                    Using loDataAdapter As New OleDbDataAdapter("Select * From prueba", loConexionAccess)
                        Dim cmdBuilder As New OleDbCommandBuilder(loDataAdapter)
                        loDataAdapter.Fill(mdtDataTable)
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub
        Private Sub btnDuplicar_Click(sender As Object, e As EventArgs) Handles btnDuplicar.Click
            Try
                'Crear la tabla nueva
                gP_CrearTabla()
                'Cargar la tabla nueva con el DataTable de la antigua
                gP_DataTableToTable()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub
        Private Sub gP_CrearTabla()
            If ((msTabla Is Nothing) OrElse (mdtDataTable.Columns.Count = 0)) Then
                Throw New ArgumentNullException("el datatable no tiene registros, o no tiene columnas, o es nulo")
            End If
            If msTabla Is Nothing OrElse msTabla.Trim().Length = 0 Then
                Throw New ArgumentNullException("el nombre de la tabla es nulo o una cadena Vacia")
            End If
            Dim lsbCrear As StringBuilder = New StringBuilder("Create Table " & msTabla.Trim() & " (")
            Dim liColumna As Integer = 0
            For Each Columna As DataColumn In mdtDataTable.Columns
                Select Case Columna.DataType.ToString
                    Case "System.Boolean"
                        lsbCrear.Append(Columna.ColumnName & " bit")
                    Case "System.String"
                        If Columna.MaxLength < 256 Then
                            lsbCrear.Append(Columna.ColumnName & " varchar (255)")
                        Else
                            lsbCrear.Append(Columna.ColumnName & " Memo")
                        End If
                    Case "System.Byte"
                        lsbCrear.Append(Columna.ColumnName & " byte")
                    Case "System.Char"
                        lsbCrear.Append(Columna.ColumnName & " char (" & CStr(Columna.MaxLength) & ")")
                    Case "System.DateTime"
                        lsbCrear.Append(Columna.ColumnName & " datetime")
                    Case "System.Decimal", "System.Double", "System.Single"
                        lsbCrear.Append(Columna.ColumnName & " double")
                    Case "System.Int16", "System.Int32"
                        lsbCrear.Append(Columna.ColumnName & " int")
                    Case "System.Byte[]"
                        lsbCrear.Append(Columna.ColumnName & " image")
                    Case Else
                        lsbCrear.Append(Columna.ColumnName & " varchar (255)")
                End Select
                'Verificamos que no sea la ultima columna
                If (liColumna < (mdtDataTable.Columns.Count - 1)) Then
                    lsbCrear.Append(", ")
                End If
                liColumna += 1
            Next
            lsbCrear.Append(")")
            Try
                Using loConexion As New OleDbConnection(msCadenaACCESS)
                    Using loComando As New OleDbCommand(lsbCrear.ToString(), loConexion)
                        loConexion.Open()
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
            Catch Exp As Exception
                MessageBox.Show(Exp.Message, "gP_CrearTabla", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub gP_DataTableToTable()
            For Each row As DataRow In mdtDataTable.Rows
                'Las filas recién agregadas se consideran filas insertadas
                row.SetAdded()
            Next
            Try
                Using loConexion As New OleDbConnection(msCadenaACCESS)
                    Using loDataAdapter As New OleDbDataAdapter("Select * From " & msTabla, loConexion)
                        Dim cmdBuilder As New OleDbCommandBuilder(loDataAdapter)
                        loDataAdapter.Update(mdtDataTable)
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message, "gP_DataTableToTable", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub
    End Class

    Un saludo desde Bilbo
    Carlos


    viernes, 3 de mayo de 2019 8:38

Todas las respuestas

  • Puedes usar un Select que te copie la tablaorigen como tabladestino :

    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\carpeta\bd.mdb"
    
    Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
    AccessConnection.Open()
    
    Dim AccessCommand = New System.Data.OleDb.OleDbCommand("SELECT * INTO [NombreTablaDestino] FROM [NombreTablaOrigen]", AccessConnection)
    AccessCommand.CommandType = CommandType.Text
    AccessCommand.ExecuteNonQuery()
    AccessConnection.Close()


    Saludos, Javier J

    viernes, 3 de mayo de 2019 6:50
  • Hola:

    De otra forma un poco mas larga y con bastante mas código, pero que puede valer (modificando algo)cuando las tablas están en diferentes bases.  

    En un Form con 1 Button, copia y pega el siguiente codigo

    Option Explicit On
    Option Strict On
    Imports System.Data.OleDb
    Imports System.Text
    Public Class Form1
        Private msCadenaACCESS As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\BD.accdb"
        Private mdtDataTable As New DataTable
        Private msTabla As String = "XXXXX"
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                Using loConexionAccess As New OleDbConnection(msCadenaACCESS)
                    Using loDataAdapter As New OleDbDataAdapter("Select * From prueba", loConexionAccess)
                        Dim cmdBuilder As New OleDbCommandBuilder(loDataAdapter)
                        loDataAdapter.Fill(mdtDataTable)
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub
        Private Sub btnDuplicar_Click(sender As Object, e As EventArgs) Handles btnDuplicar.Click
            Try
                'Crear la tabla nueva
                gP_CrearTabla()
                'Cargar la tabla nueva con el DataTable de la antigua
                gP_DataTableToTable()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub
        Private Sub gP_CrearTabla()
            If ((msTabla Is Nothing) OrElse (mdtDataTable.Columns.Count = 0)) Then
                Throw New ArgumentNullException("el datatable no tiene registros, o no tiene columnas, o es nulo")
            End If
            If msTabla Is Nothing OrElse msTabla.Trim().Length = 0 Then
                Throw New ArgumentNullException("el nombre de la tabla es nulo o una cadena Vacia")
            End If
            Dim lsbCrear As StringBuilder = New StringBuilder("Create Table " & msTabla.Trim() & " (")
            Dim liColumna As Integer = 0
            For Each Columna As DataColumn In mdtDataTable.Columns
                Select Case Columna.DataType.ToString
                    Case "System.Boolean"
                        lsbCrear.Append(Columna.ColumnName & " bit")
                    Case "System.String"
                        If Columna.MaxLength < 256 Then
                            lsbCrear.Append(Columna.ColumnName & " varchar (255)")
                        Else
                            lsbCrear.Append(Columna.ColumnName & " Memo")
                        End If
                    Case "System.Byte"
                        lsbCrear.Append(Columna.ColumnName & " byte")
                    Case "System.Char"
                        lsbCrear.Append(Columna.ColumnName & " char (" & CStr(Columna.MaxLength) & ")")
                    Case "System.DateTime"
                        lsbCrear.Append(Columna.ColumnName & " datetime")
                    Case "System.Decimal", "System.Double", "System.Single"
                        lsbCrear.Append(Columna.ColumnName & " double")
                    Case "System.Int16", "System.Int32"
                        lsbCrear.Append(Columna.ColumnName & " int")
                    Case "System.Byte[]"
                        lsbCrear.Append(Columna.ColumnName & " image")
                    Case Else
                        lsbCrear.Append(Columna.ColumnName & " varchar (255)")
                End Select
                'Verificamos que no sea la ultima columna
                If (liColumna < (mdtDataTable.Columns.Count - 1)) Then
                    lsbCrear.Append(", ")
                End If
                liColumna += 1
            Next
            lsbCrear.Append(")")
            Try
                Using loConexion As New OleDbConnection(msCadenaACCESS)
                    Using loComando As New OleDbCommand(lsbCrear.ToString(), loConexion)
                        loConexion.Open()
                        loComando.ExecuteNonQuery()
                    End Using
                End Using
            Catch Exp As Exception
                MessageBox.Show(Exp.Message, "gP_CrearTabla", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub

        Private Sub gP_DataTableToTable()
            For Each row As DataRow In mdtDataTable.Rows
                'Las filas recién agregadas se consideran filas insertadas
                row.SetAdded()
            Next
            Try
                Using loConexion As New OleDbConnection(msCadenaACCESS)
                    Using loDataAdapter As New OleDbDataAdapter("Select * From " & msTabla, loConexion)
                        Dim cmdBuilder As New OleDbCommandBuilder(loDataAdapter)
                        loDataAdapter.Update(mdtDataTable)
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message, "gP_DataTableToTable", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End Sub
    End Class

    Un saludo desde Bilbo
    Carlos


    viernes, 3 de mayo de 2019 8:38