Importar tabla de access 2007 a sqlSerrver Express

Question

Hola a todos:

Estoy intentando importar los datos de una tabla a SplServer Express desde Access 2007, utilizo dos funciones para exportar y para importar, la función exportar funciona perfectamente, compruebo si lleva registros el datable y así es. Ahora bien, en la función importar hace la lectura de los registros correctamente en el For Each, pero después pierde esos registros y el resultado es importación 0 registros.

Se me hace difícil encontrar en la función importación donde puede estar el error, ya que no da absolutamente ningún error, simplemente me indica el messageBox que los registros importados son 0.

Os pongo las dos funciones y el evento click del botón que tiene que ordenar el trabajo de las funciones.

Función par exportar y crear el datatable con los datos:

  Public Shared Function GetDataAccess() As DataTable

        Try
            Using cnn As New OleDbConnection( _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=|DataDirectory|\gemmafin.accdb;Jet OLEDB:Database Password='xxxxxxx'")

                Dim sql As String = "SELECT * FROM Empresas"

                'Creo el adpatador de datos para Access 
                Dim da As New OleDbDataAdapter(sql, cnn)

                da.MissingSchemaAction = MissingSchemaAction.AddWithKey

                'Creo un datatable con los campos de la tabla 'Empresas'
                Dim dt As New DataTable("Empresas")

                'Relleno el datatable con los datos del adaptador
                da.Fill(dt)

                Return dt

            End Using

        Catch ex As Exception
            Throw

        End Try

    End Function

Función para importar, esta es la que falla. 

 Public Shared Function ImportToSqlServer(ByVal dt As DataTable) As Integer

        Try
            ' Recorremos todas las filas del objeto DataTable  para establecer como añadido cada objeto DataRow.
            '
            For Each row As DataRow In dt.Rows
                row.SetAdded()
            Next

            Using cnn As New SqlConnection("Data Source= .\SQLEXPRESS;Initial Catalog=PerseoSqlEx;Integrated Security=true")

                ' Seleccionamos únicamente los campos comunes a las tablas de Access y de SQL Server.
                '
                Dim sql As String = "SELECT Cod_Empresa, Empresa, NIF, Clave, CNAE_93, TIPO_CAE, Descripción, FechaAlta, PlanContable, Cuadre, MesPrimero  " & _
                                    "FROM Empresas"


                Dim da As New SqlDataAdapter(sql, cnn)

                da.MissingSchemaAction = MissingSchemaAction.AddWithKey

                Dim cb As New SqlCommandBuilder(da)

                With da
                    .InsertCommand = cb.GetInsertCommand()
                    .UpdateCommand = cb.GetUpdateCommand()
                    .DeleteCommand = cb.GetDeleteCommand()
                End With

                ' Indicamos que continuaremos insertando registros a pesar de que se produzcan errores por duplicidad 
                ' de clave principal, registros únicos, etc.
                '
                da.ContinueUpdateOnError = True

                ' Enviamos los datos del objeto DataTable a la base de SQL Server.
                Dim n As Integer = da.Update(dt)

                'Compruebo si lleva registros el datatable
                Dim nrorows As Integer = dt.Rows.Count


                ' Aceptamos los cambios
                dt.AcceptChanges()

                ' Devolvemos el número de registros afectados
                Return n

            End Using

        Catch ex As Exception
            ' Rechazamos los cambios efectuados en el objeto DataTable, y por tanto, el atributo Modificado
            ' establecido al comienzo del procedimento.
            '
            dt.RejectChanges()

            Throw

        End Try
    End Function

Evento click del botón de llamada a las dos funciones.

 Private Sub ExportAccessSqlExpress_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            ' Recuperamos un objeto DataTable con los datos de la tabla de Access 2007.
            Dim dt As DataTable = Configuracion.GetDataAccess()

            'Comprobamos si el datable lleva registros. (Borrar después de pruebas)
            Dim nrorows As Integer = dt.Rows.Count

            ' Actualizamos la tabla de SQL Server.
            Dim n As Integer = Configuracion.ImportToSqlServer(dt)

            MessageBox.Show("Nº de registros afectados: " & CStr(n))     '(Borrar después de pruebas)

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub

Muchas gracias a todos.

Un saludo.

Gemma

Answer 1

"gemma_campillo" escribió:

> Se me hace difícil encontrar en la función importación donde puede estar
> el error, ya que no da absolutamente ningún error, simplemente me indica
> el messageBox que los registros importados son 0.

Si se produce algún error, no podrás saber qué error se ha producido, ya que tu misma le estás diciendo que pase por alto los posibles errores que produzca el método Update del objeto SqlDataAdapter al ejecutar

    ' Indicamos que continuaremos insertando registros a pesar de que se produzcan errores por duplicidad 
    ' de clave principal, registros únicos, etc.
    '
    da.ContinueUpdateOnError = True

Comenta esa línea de código y mira a ver si obtienes algún error.

A la hora de obtener el objeto DataTable de la base de datos de Access ejecutas:

    Dim sql As String = "SELECT * FROM Empresas"

Es decir, estás seleccionando todos los campos de la tabla Empresas, sin importar el orden en que éstos se encuentren. Pero a la hora de configurar el comando que le pasas al objeto SqlDataAdapter, ejecutas ésta otra consulta SQL de selección:

    Dim sql As String = "SELECT Cod_Empresa, Empresa, NIF, Clave, CNAE_93, TIPO_CAE," &
                        "Descripción, FechaAlta, PlanContable, Cuadre, MesPrimero FROM Empresas"

Quiero pensar que se corresponden todos los campos de la tabla Empresas de Access con los de la misma tabla de la base de SQL Server, y en su mismo orden.

Comprueba si una vez eliminada la línea que te he comentado obtienes algún error.

---

Enrique Martínez Montejo
[MS MVP - Visual Studio y Tecnologías de Desarrollo]

Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción Option Strict.

Answer 2

Hola Enrique:

Funciona perfectamente, he cambiado los campos a insertar de la tabla sql por toda la tabla "Empresas" que realmente es lo que necesito. Es decir las dos instrucciones Select de Access y de SQL funcionan a la perfección.

Ya hemos dado en el clavo, para que el usuario pueda cambiar de base de datos sin afectar para nada a la GetFactory ya que se crean dos conexiones diferentes para el proceso de exportación e importación. Ni me creo que eso haya funcionado después de lo de ayer. De cualquier manera solo para ver si da error voy a efectuar el proceso que me has indicado.

Uf, menudo alivio, ahora a ponerlo bien, todo en su sitio y a seguir.

Muchas gracias querido maestro, con esas dos funciones podemos hacer lo que queramos, siempre y cuando todos los campos sean de la misma naturaleza.

Un fuerte abrazo Enrique.

Gemma

Answer 3

Hola maestro:

Efectivamente, ahora da error de clave duplicada ya que estoy insertando registros con la misma clave. Lo que hare antes. será que en la tabla que recibe los datos, en este ejemplo la de sql, antes de importar los mismos, realice un borrado de los registros que contiene, por lo tanto la dejaremos en blanco antes de insertar, aparte le pondré una transacción por si hubiera un error en el delete, para que no haga la importación.

De cualquier manera, también tengo que valorar si es mejor borrar los datos o toda la tbla y volver a crearla con los datos.

Si te parece bien, lo haré así.

Un fuerte abrazo.

Gemma

Answer 4

"gemma_campillo" escribió:

> Efectivamente, ahora da error de clave duplicada ya que estoy
> insertando registros con la misma clave. Lo que hare antes. será
> que en la tabla que recibe los datos, en este ejemplo la de sql,
> antes de importar los mismos, realice un borrado de los registros
> que contiene, por lo tanto la dejaremos en blanco antes de insertar, ...

Pero ¿es que la tabla de SQL Server contiene registros? Creía que deseabas exportar los datos que tienes en Access a su tabla correspondiente de la nueva base de datos de SQL Server, por lo que se comprende, o al menos así lo entendía yo, que la tablas de la base de SQL Server están totalmente limpias de datos.

> De cualquier manera, también tengo que valorar si es mejor borrar
> los datos o toda la tbla y volver a crearla con los datos.

Mediante una consulta ODBC como la que te indiqué ayer, pero en lugar de ejecutar INSERT INTO hay que ejecutar SELECT * INTO, puedes transferir los datos a la vez que se crea la tabla en la base de datos de SQL Server, aunque los índices y clave principal no se transfieren. Pero desde luego eso no lo puedes hacer mediante objetos DataTable. Para hacerlo mediante estos últimos, las tablas ya tienen que estar creadas en la base de SQL Server, SQLite, MySQL, Oracle, etc., y por supuesto, limpias de datos para que no haya duplicidades en las claves principales.

---

Enrique Martínez Montejo
[MS MVP - Visual Studio y Tecnologías de Desarrollo]

Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción Option Strict.

Answer 5

Hola Enrique:

Todo está aclarado, seguiré importando y exportando los datos que me funciona bien. Lo otro del error por duplicidad lo he probado yo para ver que pasaba, es decir la misma exportación e importación la he hecho 2 veces y evidentemente se ha generado el error por duplicidad de la clave primaria.

En resumidas cuentas funciona perfectamente, lo probaré con todas las tablas a ver si no da problemas.

Un fuerte abrazo y buenas noches.

Gemma

Answer 6

"gemma_campillo" escribió:

> En resumidas cuentas funciona perfectamente, lo probaré con todas
> las tablas a ver si no da problemas.

Si vas a utilizar un objeto DataTable EXCLUSIVAMENTE (en mayúscula) para transferir los datos de Access a SQL Server, y siempre y cuando las tablas ya se encuentren creadas y debidamente configuradas en la base de datos de destino, mejor será que modifiques tus procedimientos para que se ejecuten como indico a continuación:

    Public Shared Function GetDataAccess() As DataTable

        Dim dt As New DataTable("Empresas")

        Using cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                          "Data Source=|DataDirectory|\gemmafin.accdb;Jet OLEDB:Database Password='xxxxxxx'")
            Dim cmd As OleDbCommand = cnn.CreateCommand()
            cmd.CommandText = "SELECT * FROM Empresas"

            'Crear el adapatador de datos para Access 
            Dim da As New OleDbDataAdapter(cmd)

            da.MissingSchemaAction = MissingSchemaAction.AddWithKey

            'Rellenar el datatable con los datos del adaptador
            da.Fill(dt)

            ' Si no se ha producido un error, guardamos en las
            ' propiedades extendidas del objeto DataTable la
            ' consulta SQL de selección que hemos utilizado
            ' para obtener los datos de la tabla de Access.
            '
            dt.ExtendedProperties.Add("CommandText", cmd.CommandText)

        End Using

        Return dt

    End Function

Observa que guardamos la consulta SQL de selección ejecutada en las propiedades extendidas del objeto DataTable, por tanto, se guardará 'SELECT * FROM Empresas' o 'SELECT Campo1, Campo2, CampoN FROM Empresas', es decir, la consulta tal cual se la hayas indicado a la propiedad CommandText del objeto OleDbCommand.

    Public Shared Function ImportToSqlServer(dt As DataTable) As Integer

        ' Recorremos todas las filas del objeto DataTable  para establecer como añadido cada objeto DataRow.
        '
        For Each row As DataRow In dt.Rows
            row.SetAdded()
        Next

        Dim cnn As New SqlConnection("Data Source= .\SQLEXPRESS;Initial Catalog=PerseoSqlEx;Integrated Security=true")
        Dim transaction As SqlTransaction = Nothing

        Try
            cnn.Open()

            transaction = cnn.BeginTransaction()

            ' Recuperamos la consulta SQL de selección existente en
            ' las propiedades extendidas del objeto DataTable.
            '
            Dim commandText As String = Convert.ToString(dt.ExtendedProperties("CommandText"))

            If (commandText = String.Empty) Then
                Throw New ArgumentException("No se ha especificado la consulta SQL de selección.")
            End If

            Dim cmd As SqlCommand = cnn.CreateCommand()
            cmd.CommandText = commandText
            cmd.Transaction = transaction

            Dim da As New SqlDataAdapter(cmd)
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey

            Dim cb As New SqlCommandBuilder(da)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"

            ' Solamente vamos a insertar nuevos registros por lo que
            ' no hace falta configurar las propiedades UpdateCommand
            ' y DeleteCommand.
            '
            da.InsertCommand = cb.GetInsertCommand()
            ' da.UpdateCommand = cb.GetUpdateCommand()
            ' da.DeleteCommand = cb.GetDeleteCommand()

            ' Enviamos los datos del objeto DataTable a la base de SQL Server.
            Dim n As Integer = da.Update(dt)

            transaction.Commit()

            ' Devolvemos el número de registros afectados.
            Return n

        Catch ex As Exception
            transaction.Rollback()
            Throw

        Finally
            ' Cerrar y destruir la conexión.
            cnn.Dispose()

        End Try

    End Function

Y aquí hemos encerrado todo el proceso en una transacción para que se deshaga todo si falla la importación de datos. Observa que recuperamos la consulta SQL de selección ejecutada para obtener los datos de Access desde las propiedades extendidas del objeto DataTable, por lo que ni que decir tiene que los campos deben existir en la tabla de SQL Server.

Fíjate que la función ImportToSqlServer te puede servir para actualizar cualquier tabla existente en la base de datos de SQL Server, porque la consulta SQL con la que configuramos la propiedad SelectedCommand del adaptador de datos es la misma que la ejecutada a la hora de rellenar el objeto DataTable.

Y como solamente nos vamos a ocupar de insertar nuevos registros, no tienes por qué configurar las propiedades UpdateCommand y DeleteCommand del adaptador de datos de SQL Server.

Cuando desees ejecutar el proceso de exportación de datos, tan solo tienes que ejecutar:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Try
            ' Transferir los datos de la tabla Empresas de
            ' Access a la tabla Empresas de SQL Server.
            '
            Dim n As Integer = ImportToSqlServer(GetDataAccess())

            MessageBox.Show($"Registros exportados: {n}")

        Catch ex As Exception
            ' Se ha producido un error
            MessageBox.Show(ex.Message)

        End Try

    End Sub

¡Ah! Se me olvidaba comentarte que toda esta operación tiene su coste, dependiendo del número de registros que se vayan a insertar en la tabla de SQL Server, por lo que si hablamos de un número considerable de registros, no esperes que la exportación se haga en un abrir y cerrar de ojos. Lo mismo sería cuestión de ejecutar la operación de manera asíncrona en otro subproceso.

---

Enrique Martínez Montejo
[MS MVP - Visual Studio y Tecnologías de Desarrollo]

Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción Option Strict.

Answer 7

Hola maestro:

Eres un maldito perfeccionista.

Desde luego queda de película y he cambiado las funciones por las indicadas. Todo función perfectamente, por otro lado al usuario se le advierte del consumo de tiempo y aparte cuando hace el proceso, en un listview le voy mostrando la evolución del mismo.

Queda de película este tema, lo tienes perfecto.

Enrique, de aquí unos días te lo envío como actualización a ver si te gusta.

Querido amigo como siempre muchas gracias por tu inestimable y dedicada o delicada ayuda.

Un fuerte abrazo.

Gemma