Publicado el 11/04/2004 por Enrique Martínez Montejo. Enlace original
Contenido
- Introducción
- Crear una tabla
- Modificar la estructura de una tabla
- Añadir nuevos campos a la tabla
- Modificar el tipo de dato de un campo
- Eliminar un campo de la tabla
- Copiar una tabla
- Eliminar una tabla
Introducción
Las tablas constituyen la espina dorsal de una base de datos porque serán ellas las encargadas de almacenar los datos, por lo que es fundamental que las mismas se encuentren bien diseñadas a fin de garantizar que el mantenimiento de la base de datos en su conjunto se realice de una manera fácil.
En el capítulo anterior se describieron los distintos tipos de datos que podemos utilizar con el lenguaje SQL del motor Microsoft Jet. En este capítulo se verá como aplicar los tipos de datos a la creación de una tabla, y se estudiarán las sentencias y reglas sintácticas del lenguaje SQL para crear y modificar el diseño de las mismas, dejando para un capítulo posterior el estudio por separado de otra parte muy importante de la estructura de una tabla, como puede ser la encargada de crear los índices.
Por último, en este mismo capítulo también se abordará los temas relacionados con las operaciones del lenguaje de definición de datos de SQL encargadas de copiar y eliminar tablas de una base de datos, tanto nativas como procedentes de otros orígenes de datos a los que el motor Microsoft Jet puede tener acceso mediante los controladores ISAM instalables correspondientes.
Crear una tabla
Utilizando la instrucción CREATE TABLE del lenguaje de definición de datos del motor Microsoft Jet, podemos crear nuevas tablas, tanto en bases de datos Microsoft Access como en otros formatos de bases de datos de escritorio, como por ejemplo, archivos de dBASE, Excel y de Texto delimitado. Para estos últimos formatos necesitaremos tener instalado en nuestro sistema el controlador ISAM instalable correspondiente con el archivo que deseamos crear, con la única limitación de que no se podrá especificar ningún tipo de índices a la hora de ejecutar la consulta SQL de creación de tabla.
Sintaxis
CREATE TABLE tabla (
campo1 tipo [(tamaño)] [WITH COMPRESSION | WITH COMP] [NOT NULL | NULL] [DEFAULT valorDefecto1] [índice1]
[, campo2 tipo [(tamaño)] [WITH COMPRESSION | WITH COMP] [NOT NULL | NULL] [DEFAULT valorDefecto2] [índice2]
[, ...]]
[,CONSTRAINT índice_múltiples_campos[,...]]
)| Parámetro | Descripción |
| tabla | El nombre de la tabla que deseamos crear. |
| campo1, campo2 | El nombre de los campos que se van a crear. Al menos debe de crearse un campo. |
| tipo | El tipo de dato que tendrá el campo de la nueva tabla. |
| tamaño | El tamaño del campo en caracteres sólo para los campos de tipo texto carácter o BINARY. |
| WITH COMPRESSION | WITH COMP SÓLO ADO | Atributo opcional que implica comprimir a caracteres de un solo byte (SBCS) los caracteres Unicode. Sólo se puede utilizar con los tipos de datos CHARACTER y MEMO, así como por sus sinónimos. Se puede utilizar cualquiera de las dos palabras reservadas indicadas, siempre y cuando precedan al atributo NOT NULL | NULL, en el supuesto de que se especifique este atributo. |
| NOT NULL | NULL | De utilizarse significa que el campo es requerido, por lo que se exige que los nuevos registros tengan datos válidos en el campo. Este parámetro es opcional y su valor por defecto es NULL. |
| DEFAULT SÓLO ADO | Establece el valor por defecto que tendrá el campo para los nuevos registros que se creen. Este parámetro es opcional, pero si se especifica, deberá de aparecer al final de la declaración del campo. El valor por defecto indicado deberá corresponderse con el tipo de dato declarado. |
| índice1, índice2 | Una cláusula CONSTRAINT que define un índice de un solo campo, seguido del nombre del índice. Este parámetro es opcional. |
| índice_múltiples_campos | Una cláusula CONSTRAINT que define un índice de múltiples campos, seguido del nombre, tipo de índice y campos que lo forman. Este parámetro es opcional. |
Comentarios
La instrucción CREATE TABLE se utiliza como argumento del método Execute de un objeto Database abierto (biblioteca de DAO) o de un objeto Connection (biblioteca de ADO), así como por el método ExecuteNonQuery de un objeto OleDbConnection del espacio de nombre System.Data.OleDb de ADO .NET, y su objetivo es crear una nueva tabla en la base u origen de datos especificado, siempre y cuando se incluya en la consulta de creación de tabla la sintaxis adecuada para crear al menos un campo en la misma.
Si cualquier nombre de tabla, campo o índice está formado por varias palabras separadas, o estas contienen signos de puntuación, necesariamente deberán de incluirse entre corchetes [ ] a fin de evitar el correspondiente error de sintaxis en la instrucción CREATE TABLE, lo que también es válido para cualquier otra instrucción SQL.
Se ha añadido el atributo WITH COMPRESSION para las columnas de caracteres debido al soporte de estos campos para los caracteres Unicode, los cuales requieren siempre dos bytes para cada carácter. Al convertir al formato Microsoft Jet versión 4.0 las bases de datos creadas con anterioridad que contengan principalmente datos de tipo carácter, el tamaño del archivo de la base de datos puede aumentar el doble al convertirse al nuevo formato de Jet 4.0. Sin embargo, la representación Unicode de muchos juegos de caracteres, denominados anteriormente juegos de caracteres de un solo byte (SBCS), puede comprimirse fácilmente a caracteres de un solo byte. Por tanto, si definimos una columna tipo CHARACTER con el atributo WITH COMPRESSION, los datos se comprimirán automáticamente cuando se almacenen, y se descomprimirán cuando se recupere el dato de la columna. Indicar que este atributo sólo se puede especificar cuando utilicemos ADO y el proveedor Jet OLE DB 4.0 para definir la estructura de la tabla.
Las columnas tipo MEMO también pueden ser definidas para que almacenen datos en formato comprimido, con la excepción de que sólo se comprimirán aquellos datos de la columna que una vez comprimidos ocupen 4.096 bytes o menos, no comprimiéndose el resto del campo MEMO que supere dicha cifra, por lo que se puede dar el caso de que en un campo MEMO existan datos comprimidos y no comprimidos.
El atributo NOT NULL indica que el campo es requerido, por lo que no se podrá añadir un nuevo registro a la tabla si no se especifica un valor para el campo. El valor por defecto para todos los campos es NULL (no requerido), y su declaración es opcional.
La versión 4.0 del motor Microsoft Jet ha introducido la palabra reservada DEFAULT, con la cual se puede establecer el valor predeterminado que tendrá el campo, de tal forma que dicho valor aparecerá automáticamente en el campo cuando se cree un nuevo registro. El atributo DEFAULT deberá de incluirse al final de la declaración del campo y su valor se deberá de corresponder con el tipo de dato declarado para el campo. Sólo se puede utilizar DEFAULT con la biblioteca de ADO y por tanto, con el proveedor Microsoft Jet OLE DB 4.0. Si intenta incluir dicha palabra en una consulta SQL de creación o de modificación de tabla para ser ejecutada con la biblioteca de DAO o desde la interfaz de usuario de Microsoft Access, obtendrá el oportuno error de sintaxis.
Se puede crear un índice a la hora de crear una tabla en una base de datos Access, bien sobre un único campo de la tabla o a través de dos o más campos (índice multicolumnas), utilizando para ambos casos la cláusula CONSTRAINT, que deberá de aparecer al principio de la definición del índice y siempre que se utilice con las instrucciones CREATE TABLE y ALTER TABLE.
Los tipos de índices que se pueden designar mediante la cláusula CONSTRAINT son tres: UNIQUE, PRIMARY KEY y FOREIGN KEY. Puede incluirse también el atributo NOT NULL, lo que significará que los valores de los campos que conforman un índice multicolumna serán requeridos. Respecto a los índices de un sólo campo, dicho atributo puede omitirse en la cláusula CONSTRAINT y especificarse en la parte correspondiente a la definición del campo, si deseamos que el campo sea requerido.
Para más información sobre la creación de índices, vea el capítulo Cómo crear y eliminar los índices de las tablas.
Por último, en lo referente a crear tablas temporales en Microsoft Access con la misma funcionalidad que sus homónimas de Microsoft SQL Server, he de comentar que hasta la fecha no está soportada aún la cláusula TEMPORARY, a pesar de que en la documentación oficial sobre la sintaxis del SQL de Microsoft Jet 4.0 se haga referencia a la posibilidad de poder crearlas.
Ejemplos
1. Crear una tabla en una base de Access
El siguiente ejemplo creará una hipotética tabla de Clientes, donde la clave principal de la tabla será el campo IdCliente, siendo el campo CIF un índice donde no se permitirán valores duplicados. Observe que en algunas declaraciones de campo se especifica su valor por defecto mediante la palabra reservada DEFAULT:
Dim cnn As ADODB.Connection
Dim SQL As String
' Creo la cadena SQL
SQL = "CREATE TABLE Clientes ("
"IdCliente INTEGER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"[Nombre Completo] TEXT (50) WITH COMP NOT NULL, " & _
"CIF TEXT (9) WITH COMPRESSION NOT NULL CONSTRAINT IndiceCIF UNIQUE, " & _
"Domicilio TEXT (50) WITH COMP NULL, " & _
"CPostal INTEGER NULL DEFAULT 23000, " & _
"Localidad TEXT (30) WITH COMP NULL, " & _
"Provincia TEXT (20) WITH COMP NULL DEFAULT ""JAÉN"", " & _
"Pais TEXT (20) WITH COMP NULL DEFAULT ESPAÑA, " & _
"Telefono TEXT (10) WITH COMP NULL, " & _
"Fax TEXT (10) WITH COMP NULL, " & _
"[Fecha Alta] DATETIME DEFAULT DateValue(Now), " & _
"Observaciones TEXT WITH COMP)"
' Creo un objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mis documentos\bd1.mdb"
' Abro la conexión
.Open
' Ejecuto la consulta de creación de tabla
.Execute SQL, , adCmdText
' Cierro la conexión
.Close
End With2. Crear un archivo de dBASE versión 5.0
Antes de crear un archivo de dBASE utilizando el lenguaje SQL de Microsoft Jet, deberá de tener en cuenta las siguientes observaciones:
- Si el nombre del campo tiene más de diez caracteres, serán ignorados todos aquéllos caracteres que superen dicha cantidad, sustituyéndose los espacios en blanco que hubiere por caracteres de guion bajo: [Fecha_Alta].
- Se tomará las primeras ocho letras del nombre del archivo, ignorándose todas aquellas que superen la cantidad especificada, por lo que el nombre del archivo tendrá el clásico formato 8 + 3.
- No es necesario especificar la extensión del archivo, y ésta siempre será DBF, aunque se haya especificado otra extensión distinta.
- Si la consulta contiene campos de tipo MEMO, se creará en la misma carpeta un archivo con el mismo nombre que la base de datos, pero con extensión DBT.
- No se permite la cláusula CONSTRAINT, por lo que no se podrá crear índices en el archivo de dBASE. Asimismo, si en la carpeta existe un archivo de índice (archivo *.inf) con el mismo nombre del archivo que se desea crear, éste será eliminado automáticamente, por lo que deberá de tomar las precauciones oportunas si desea conservar el archivo de índice de dBASE existente.
El siguiente ejemplo creará un archivo de dBASE utilizando una consulta SQL de creación de tabla:
Dim cnn As ADODB.Connection
Dim SQL As String
' Creo la cadena SQL
SQL = "CREATE TABLE Clientes#dbf ("
"IdCliente INTEGER, " & _
"[Nombre Completo] TEXT (50), " & _
"CIF TEXT (9), " & _
"Domicilio TEXT (50), " & _
"CPostal INTEGER, " & _
"Localidad TEXT (30), " & _
"Provincia TEXT (20), " & _
"Pais TEXT (20), " & _
"Telefono TEXT (10), " & _
"Fax TEXT (10), " & _
"[Fecha Alta] DATETIME, " & _
"Observaciones TEXT)"
' Creo un objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mis documentos\"
.Properties("Extended Properties") = "dBASE 5.0;"
' Abro la conexión
.Open
' Ejecuto la consulta de creación de tabla
.Execute SQL, , adCmdText
' Cierro la conexión
.Close
End With3. Crear un archivo de texto delimitado
Las observaciones que deberá de tener en cuenta son las siguientes:
- Se puede asignar un nombre largo al nombre del archivo de texto, necesitando encerrarlo entre corchetes si el nombre contiene espacios en blanco.
- Necesariamente hay que especificar una extensión al nombre del archivo, y debe estar contemplada en el valor DisabledExtensions de la clave del registro de Windows HKLM\Software\Microsoft\Jet\4.0\Engines\Text, que por defecto tiene registradas las siguientes extensiones: !txt, csv, tab, asc, tmp, htm, html.
De especificar una extensión no registrada obtendremos el siguiente mensaje de error: No se puede modificar el diseño de la tabla ‘Nombre archivo#txt’. Esta es una base de datos de sólo lectura.
- Si al crear la consulta de creación de tabla no existe un archivo de configuración de esquema Schema.ini, automáticamente se creará uno en la misma carpeta, el cuál contendrá los nombres de los campos, el formato delimitador de campos entre otros cuántos parámetros más.
- Si el archivo Schema.ini ya contiene la información de esquema correspondiente al mismo archivo de texto que se desea crear, pero difiere en cuanto al nombre o el número de campos que la especificada en la instrucción CREATE TABLE, se producirá el error El motor de base de datos Microsoft Jet no pudo encontrar el objeto ‘NOMBRE#TXT’, por lo que deberá de poner especial atención si el archivo Schema.ini incluye una sección con el nombre de nuestro archivo de texto, ya que la consulta SQL se deberá de ajustar al contenido del esquema existente.
- De existir ya la información del archivo en la correspondiente sección del archivo Schema.ini, no se modificará ningún parámetro al ejecutar la consulta SQL de creación de tabla, aunque sean distintos los tipos de datos de los campos que se desean crean con los existentes en el archivo de configuración de esquema, y respecto al mismo nombre del campo de los ya existentes. Para que los datos incluidos en el archivo Schema.ini se correspondan con los especificados en la consulta SQL, edite primero el archivo de configuración de esquema y modifique los valores oportunos antes de ejecutar la consulta de creación de tabla.
- Será obligatoria la presencia de un archivo Schema.ini en los siguientes supuestos:
- Accedamos a datos de longitud fija.
- El archivo de texto contenga tipos de datos DateTime, Currency o Decimal.
- Deseamos elegir un juego de caracteres distinto al valor predeterminado existente en la configuración del registro de Windows.
El siguiente ejemplo muestra cómo crear un archivo de texto de ancho fijo mediante la instrucción CREATE TABLE. Para ello, cree primero un archivo Schema.ini en la carpeta donde se creará el archivo de texto, con los siguientes parámetros:
[Clientes.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=FixedLength
TextDelimiter=none
Col1=IdCliente Integer Width 8
Col2=Nombre Char Width 51
Col3=CIF Char Width 10
Col4=Domicilio Char Width 31
Col5=CPostal Integer Width 6
Col6=Localidad Char Width 31
Col7="Fecha Alta" Date Width 11
Col8=Notas LongChar Width 100A continuación ejecute el siguiente procedimiento:
Dim cnn As ADODB.Connection
Dim SQL As String
SQL = "CREATE TABLE Clientes#txt(" & _
"IdCliente INTEGER," & _
"Nombre TEXT (50)," & _
"CIF TEXT (9)," & _
"Domicilio TEXT (30)," & _
"CPostal INTEGER," & _
"Localidad TEXT (30)," & _
"[Fecha Alta] DATETIME," & _
"Notas TEXT)"
' Creo un objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mis documentos\"
.Properties("Extended Properties") = "TEXT;HDR=Yes"
' Abro la conexión
.Open
' Ejecuto la consulta de creación de tabla
.Execute SQL, , adCmdText
' Cierro la conexión
.Close
End WithObsérvese que en el archivo de configuración se ha definido un carácter más a la longitud de los campos de texto, para que haya un espacio en blanco de separación entre los campos. Respecto a los tipos de datos de los restantes campos, si desea que aparezca un espacio en blanco de separación, procure que la longitud del valor introducido sea una unidad menos que la definida en el archivo de configuración.
Si desea más información sobre cómo trabajar con el ISAM de Texto, consulte el siguiente artículo técnico: Trabajar con los datos de un archivo de texto (en mvp-acces.es/softjaen)
4. Crear una nueva hoja de cálculo de Excel
Si utiliza el ISAM de Excel para crear una nueva hoja de cálculo, deberá de tener en cuenta las siguientes observaciones:
- Si a la hora de establecer una conexión con el origen de datos de Excel no existe el archivo especificado en el parámetro Data Source, en lugar de obtener un error, el ISAM creará automáticamente un archivo temporal de Excel, cuya versión corresponderá con la utilizada en la cadena de conexión especificada. Dicho archivo temporal se eliminará al cerrar la conexión abierta, excepto si creamos una tabla (en este caso, una hoja de trabajo), en cuyo caso tendremos a nuestra disposición un flamante archivo de Excel.
- Al crear una hoja de cálculo, Excel hará caso omiso a los tipos de datos especificados en la instrucción CREATE TABLE, por lo que todas las celdas tendrán el formato General, a excepción de los campos que tengan un tipo de datos de fecha, en los que sí reconocerá dicho formato de celda. No obstante, siempre y cuando el archivo no se haya modificado y guardado desde el propio programa Microsoft Excel, el objeto Field de ADO reconocerá los tipos de datos especificados en la consulta SQL de creación de tabla, con los siguientes detalles:
- No es necesario especificar el tamaño de los campos de texto, ya que todos tendrán 255 caracteres de longitud, salvo que indiquemos expresamente en la sintaxis la palabra clave TEXT sin especificar el tamaño, en cuyo caso el tipo de dato devuelto por la propiedad Type del objeto Field será TEXT o MEMO.
- Siempre que se cree una nueva hoja de trabajo mediante la instrucción CREATE TABLE, se creará así mismo un nuevo rango con idéntico nombre que el especificado en la consulta SQL.
- No se puede especificar un rango de celdas sin nombre [Hoja1$A1:Z50], como tampoco se puede especificar el nombre de una hoja de cálculo completa [Hoja1$], en el parámetro correspondiente al nombre de la tabla que deseamos crear: el único nombre permitido es el utilizado para definir un rango de celdas con nombre:
| CREATE TABLE [Hoja1$A1:Z50] (Campo1 INTEGER) CREATE TABLE [Hoja1$] (Campo1 INTEGER) CREATE TABLE [Hoja1] (Campo1 INTEGER) | ‘ No es un nombre válido. ‘ No es un nombre válido. ‘ Sí es correcto el nombre de la tabla. |
El siguiente ejemplo utilizará ADO .NET para crear una nueva hoja de trabajo de Excel mediante el proveedor Microsoft Jet OLEDB. La misma sintaxis SQL se puede utilizar también con las bibliotecas de DAO y ADO:
' Importamos el siguiente espacio de nombre
'
Imports System.Data.OleDb
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
' Configuramos y abrimos la conexión.
'
Using cnn As New OleDbConnection( _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\Libro1.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes;'")
cnn.Open()
Dim cmd As OleDbCommand = cnn.CreateCommand()
' Creamos la consulta SQL.
'
cmd.CommandText = _
"CREATE TABLE Clientes(" & _
"IdCliente INTEGER," & _
"Nombre TEXT (50)," & _
"NIF TEXT (9)," & _
"Domicilio TEXT (30)," & _
"CodPostal INTEGER," & _
"Localidad TEXT (30)," & _
"[Fecha Alta] DATETIME," & _
"Notas TEXT)"
' Ejecutamos el comando.
'
cmd.ExecuteNonQuery()
MessageBox.Show("Se ha creado la hoja de trabajo.", _
"CREATE TABLE", MessageBoxButtons.OK, _
MessageBoxIcon.Information)
' Insertamos dos filas.
'
cmd.CommandText = _
"INSERT INTO Clientes VALUES (" & _
"1,'Juan López Pérez','25222011J'," & _
"'C/. Bernabé Soriano, 24 - 8º Izq.',23001,'JAÉN','28/04/1962',NULL)"
cmd.ExecuteNonQuery()
cmd.CommandText = _
"INSERT INTO Clientes VALUES (" & _
"2,'Félix Cuesta Rodríguez','25940103F'," & _
"'C/. Blas Infante, 35 - 7º A',23008,'JAÉN','21/08/1961','Esto es una nota')"
cmd.ExecuteNonQuery()
MessageBox.Show("Se han insertado los dos registros.", _
"INSERT INTO", MessageBoxButtons.OK, _
MessageBoxIcon.Information)
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End SubPara más información sobre el ISAM de Excel, consulte el siguiente artículo técnico: Trabajar con ADO, DAO y Excel (en mvp-access.es/softjaen)
Modificar la estructura de una tabla
Una vez creada la tabla, puede que resulte necesario modificar el diseño de la misma, bien para añadir nuevos campos, cambiar el tipo de dato de algún campo, o bien, eliminar un campo de la tabla. Para todas estas operaciones utilizaremos la instrucción ALTER TABLE seguida de la correspondiente cláusula SQL, dependiendo de la operación que desee ejecutar.
Hasta la llegada de la versión 4.0 del motor Microsoft Jet, las únicas operaciones permitidas por el lenguaje SQL relacionadas con los campos de una tabla, eran las que nos posibilitaba solamente añadir y eliminar columnas de la tabla. Ahora, también nos está permitido modificar el tipo de dato declarado inicialmente para un campo, operación esta que conlleva el peligro de una pérdida de información si los datos incluidos en el campo no se corresponden con el nuevo tipo de dato declarado.
Pero todavía continúa siendo una asignatura pendiente el poder renombrar el nombre de un campo existente en la tabla, por lo que tendremos que recurrir a la interfaz de usuario de Microsoft Access, o bien, implementar un procedimiento para realizar la operación mediante código fuente.
A continuación pasaré a detallar por separado las operaciones permitidas de modificación de la estructura de una tabla, teniendo en cuenta que solamente podrá modificar un campo cada vez que ejecute una instrucción ALTER TABLE.
Añadir nuevos campos a la tabla
Para añadir un campo a la tabla con la instrucción ALTER TABLE utilice la cláusula ADD COLUMN, donde deberá especificar el nombre del campo, el tipo de dato, un tamaño opcional para los campos de tipo Text y Binary, así como el valor por defecto, teniendo en cuenta que esta última opción sólo está disponible cuando la consulta SQL es ejecutada con la biblioteca de ADO y el proveedor Microsoft Jet OLE DB 4.0.
La sintaxis para añadir un nuevo campo es la siguiente:
ALTER TABLE nombreTabla ADD COLUMN nombreCampo tipoDato [(tamaño)]
[WITH COMPRESSION | WITH COMP]
[NOT NULL | NULL]
[DEFAULT valorDefecto]
[índice]El siguiente ejemplo añadirá un nuevo campo a una tabla cualquiera. Debido a que el campo será requerido (lo que significa que será obligatoria la entrada de datos), especificaremos un valor por defecto:
ALTER TABLE Tabla1 ADD COLUMN Campo1 VARCHAR (30)
WITH COMP NOT NULL DEFAULT ""Valor por defecto""Nota: Respecto a la forma de especificar el valor por defecto, encierre el mismo entre dos pares de comillas dobles si el valor contiene espacios en blanco.
A continuación añadiremos un nuevo campo Autonumérico a la tabla Alumnos que será la clave principal de dicha tabla. Note que en el campo contador se ha definido el valor de inicio (10) y el de incremento (5):
ALTER TABLE Alumnos ADD COLUMN IdAlumno COUNTER (10,5)
CONSTRAINT ClavePrincipal PRIMARY KEYModificar el tipo de dato de un campo
Podemos también utilizar la instrucción ALTER TABLE junto con la cláusula ALTER COLUMN, para modificar el tipo de dato de un campo, el tamaño opcional para los campos de tipo Text y Binary, o el valor por defecto del mismo, siendo su sintaxis la siguiente:
ALTER TABLE nombreTabla ALTER COLUMN nombreCampo tipoDato [(tamaño)]
[WITH COMPRESSION | WITH COMP]
[NOT NULL | NULL]
[DEFAULT valorDefecto]
[índice]Si solamente desea establecer o modificar el valor por defecto, puede utilizar, si así lo desea, la siguiente sintaxis:
ALTER TABLE nombreTabla ALTER COLUMN nombreCampo SET DEFAULT valorDefectoNo utilice la palabra reservada SET cuando modifique a la misma vez el tipo de dato y el valor por defecto del campo, ya que obtendrá un error de sintaxis. La palabra SET únicamente se utiliza cuando sólo se desea modificar o establecer el valor por defecto de un campo, en cuyo caso es completamente necesario incluirla, tal y como se muestra en el siguiente ejemplo:
ALTER TABLE Alumnos ALTER COLUMN Localidad SET DEFAULT ""Jaén""Advierta que el valor por defecto debe de especificarse encerrando el valor entre pares de comillas dobles, si el mismo es un valor alfanumérico, y deberá de corresponderse con el tipo de valor declarado para el campo. Si el valor está formado por una sola palabra, no es necesario que se especifique los pares de comillas, aunque sí es recomendable que se incluyan a fin de obtener una compatibilidad con Microsoft Access, el cual encierra los valores alfanuméricos por defecto entre comillas dobles en la vista Diseño de la tabla. En todo caso, si el valor por defecto incluye espacios en blancos, necesariamente tendrá que encerrar el valor entre pares de comillas dobles, dado que no sirve en este caso encerrar las palabras entre corchetes []. Por último, si desea eliminar el valor por defecto de un campo, no incluya ninguna palabra o número tras la palabra reservada DEFAULT:
ALTER TABLE Alumnos ALTER COLUMN Localidad SET DEFAULTA continuación modifiquemos el tipo de dato de un campo, de tal forma que su valor inicial de texto pasará ahora a ser del tipo numérico entero largo:
ALTER TABLE Alumnos ALTER COLUMN CodPostal INTEGER DEFAULTSi los valores existentes en el campo no se pueden convertir al nuevo tipo de dato definido, recibirá el siguiente mensaje de error: No coinciden los tipos de datos en la expresión de criterios.
En el ejemplo anterior, si algún registro de la tabla tiene un valor alfanumérico en el campo cuyo tipo de dato queremos modificar, no podremos llevar a cabo dicha operación si ejecutamos la consulta SQL utilizando los objetos de la biblioteca de ADO. En cambio, si ejecutamos la misma consulta SQL utilizando la biblioteca de DAO, o desde la interfaz de usuario de Microsoft Access, no obtendremos el citado mensaje de error, insertándose valores NULL en el campo de los registros que presenten valores alfanuméricos.
El siguiente ejemplo disminuirá el tamaño del campo de una columna definida como Text:
ALTER TABLE Clientes ALTER COLUMN Domicilio TEXT (30)Si establece un tamaño de la columna más pequeño que la longitud existente actualmente en algún registro del campo, no podrá modificar el tamaño del campo, recibiendo el siguiente mensaje de error: El campo es demasiado pequeño para aceptar la cantidad de datos que intenta agregar. Intente insertar o pegar menos datos.
Si tenemos una columna de texto definida inicialmente con un tamaño del campo de 50 caracteres, no podremos disminuir el tamaño del mismo si existen datos cuya longitud supera en caracteres el nuevo tamaño del campo que deseamos establecer. Por tanto, si los datos de un registro ocupan 38 caracteres, no podemos especificar un tamaño del campo inferior a esa cifra, si para ejecutar la consulta SQL de modificación de campo utilizamos los objetos de la biblioteca de ADO, cosa ésta que no ocurre si utilizamos nuevamente la biblioteca de DAO o la interfaz de usuario de Microsoft Access, en cuyo caso desaparecerán, comenzando por la derecha, los caracteres que superen el nuevo tamaño del campo indicado.
A continuación, modificaremos el tipo de dato de un campo, de tal forma que sea del tipo Autonumérico. Asimismo, indicaremos que su valor inicial sea 10, y que se incremente en valores de cinco unidades.
ALTER TABLE Clientes ALTER COLUMN IdCliente IDENTITY (10,5)Esta consulta SQL, únicamente será válida cuando deseemos modificar el tipo de dato de un campo, siempre y cuando no existan registros en la tabla, porque de contener un único registro, es suficiente para obtener el siguiente mensaje de error: Tipo de datos de campo no válido.
El ejemplo anterior también nos puede servir para modificar el valor de inicio o de incremento de un campo Autonumérico, en cuyo caso da igual que la tabla tenga o no registros insertados.
Por último, modifiquemos el tamaño de un campo numérico, el cual pasará de FLOAT a REAL:
ALTER TABLE Facturas ALTER COLUMN TotalFactura REAL DEFAULTLa pérdida de datos también puede ocurrir al cambiar entre los distintos tipos de datos numéricos existentes, por lo que el cambiar de Doble a Simple también puede conllevar una pérdida de datos, cuestión esta que es importante que siempre tenga en cuenta.
Al incluir en el ejemplo la palabra clave DEFAULT, sin un valor predeterminado en concreto, eliminaremos por completo el valor por defecto que tuviera el campo. De camino, como el nuevo valor es numérico, evitamos que tome el 0 como valor predeterminado.
Eliminar un campo de la tabla
Hemos visto como añadir un campo a una tabla ya existente, así como la posibilidad de modificar el tipo de dato inicialmente declarado, por lo que solo nos queda ver la forma de eliminar un campo de una tabla. Para ello también utilizaremos la instrucción ALTER TABLE, pero esta vez irá acompañada de una cláusula DROP COLUMN, siendo su sintaxis como se detalla a continuación:
ALTER TABLE nombreTabla DROP COLUMN nombreCampoEl siguiente ejemplo eliminará un campo cualquiera de una tabla:
ALTER TABLE Tabla1 DROP COLUMN Campo1Deberá de tener en cuenta que, si el campo que desea eliminar forma parte de un índice o de una relación existente, deberá eliminar primero el índice o la relación antes de proceder a eliminar el campo de la tabla.
Mediante el lenguaje SQL del motor Microsoft Jet aún no es posible renombrar el nombre de un campo de la tabla, por lo que si no queremos recurrir a Microsoft Access para tal cometido, tendremos que eliminar el campo y recrearlo de nuevo. Pero si eliminamos el campo, se eliminarán también los datos existentes, salvo que preservemos los datos pasando estos al nuevo campo, tal y como muestra el siguiente ejemplo:
' Creamos un nuevo campo, el cual tendrá el nuevo nombre.
'
ALTER TABLE Clientes ADD COLUMN IdCliente INTEGER
' Pasamos los datos al nuevo campo
'
UPDATE Clientes SET IdCliente = [Num Cliente]
' Eliminamos el campo
'
ALTER TABLE Clientes DROP COLUMN [Num Cliente]Deberá de tener especial cuidado para que el nuevo campo tenga el mismo tipo de datos y tamaño que el campo que desea eliminar, asi como tener en cuenta su posible pertenencia a un índice de la tabla o a una relación existente en la base de datos.
Copiar una tabla
Mediante la consulta de creación de tabla, representada por la instrucción SELECT…INTO, podemos crear copias de nuestras tablas, bien como medida de seguridad ante una posible pérdida de datos, o simplemente para importar o exportar los datos desde o hacia otra base de datos externa. No es necesario copiar los datos de la tabla, ya que puede suceder que sólo nos interese copiar la estructura de la tabla de tal forma que nos sirva de plantilla para una nueva tabla.
Sintaxis
Hay tres tipos de sintaxis diferente que puede utilizar, dependiendo de dónde se encuentren el origen y el destino de los datos:
1. El origen y el destino se encuentran en la misma base de datos:
SELECT campo1[, campo2[, ...]] INTO nuevaTabla FROM origen [WHERE False]2. La tabla de destino se encuentra en una base de datos externa al origen de los datos, que es la sintaxis a utilizar cuando se desea exportar datos a otra base de datos:
SELECT campo1,[ campo2[, ...]] INTO nuevaTabla IN baseDatosExterna FROM origen [WHERE False]3. La tabla de origen se encuentra en una base de datos externa distinta del destino de los datos, que es la sintaxis que debe de utilizar cuando desee importar datos a la base de datos actual:
SELECT campo1,[ campo2[, ...]] INTO nuevaTabla FROM origen IN baseDatosExterna [WHERE False]Cuando necesite indicar una base de datos externa Microsoft Access, podrá utilizar cualquiera de las dos sintaxis que a continuación se indican, teniendo en cuenta que si la ruta la encierra entre comillas simples, no deberá de utilizar los corchetes. En cambio, si el par de comillas simples va inmediatamente después de la cláusula IN, sí deberá de encerrar entre corchetes la cadena de conexión, indicando el tipo de ISAM a utilizar (MS Access, en el supuesto que la base de datos externa sea Microsoft Access), y la ruta completa de la base de datos en el parámetro DATABASE, así como cualquier otro parámetro opcional que sea requerido, como bien pudiera ser la contraseña de la base de datos.
Si la base de datos externa es distinta de Microsoft Access, necesariamente deberá de utilizar el formato de corchetes para especificar todos los parámetros requeridos para conectarse con el origen de datos.
| IN ‘Ruta base datos externa‘ | IN ‘C:\Mis documentos\Bd1.mdb‘ |
| IN »[MS Access;DATABASE=Ruta base datos externa;PWD=contraseña] | IN »[MS Access;DATABASE=C:\Mis documentos\Bd1.md;PWD=contraseña] |
| Parámetro | Descripción |
| campo1, campo2 | El nombre de los campos que se van a crear. Al menos debe de crearse un campo. |
| nuevaTabla | El nombre de la tabla que deseamos crear. |
| origen | El nombre de la tabla de origen. |
| baseDatosExterna | Ruta completa de la base de datos externa. Si la base de datos no es Microsoft Access, deberá de incluir igualmente el tipo de base ISAM y los parámetros necesarios para conectarse a la misma. |
| WHERE False | Si se indica la cláusula opcional WHERE False, sólo se copiará la estructura de la tabla indicada en el origen. También se puede especificar WHERE 1=0. |
Comentarios
Cuando se crea una nueva tabla mediante la instrucción SELECT…INTO, los campos solamente heredarán el tamaño y el tipo de dato correspondiente a cada campo de la tabla de origen especificada en la consulta, no transfiriéndose ninguna otra propiedad del campo o de la tabla. Igualmente, tampoco se transferirá ningún índice que pueda tener establecido la tabla de origen.
Si desea conocer los registros que se verán afectados por la consulta de creación de tabla, dispone de dos métodos para saber los registros que se seleccionarán, dependiendo si lo desea conocer antes o después de ejecutar la consulta:
- Antes de ejecutar la consulta de creación de tabla, puede examinar el resultado de una instrucción SELECT que utilice el mismo criterio de selección.
- Una vez ejecutada la consulta, podemos conocer el número de registros afectados leyendo el valor devuelto por el parámetro RecordsAffected del método Execute del objeto Connection de ADO, o leyendo el método ExecuteNonQuery de un objeto Command de ADO .NET, tal y como se puede apreciar en los siguientes ejemplos.
Para los usuarios de ADO:
Dim lngRegAfectados As Long
Dim rst As ADODB.Recordset
' Antes de ejecutar la consulta de creación de tabla, consultamos
' los registros que se seleccionarán
'
Set rst = New ADODB.Recordset
rst.Open "SELECT COUNT(*) AS TotalRegistros FROM Tabla1", cnn
MsgBox rst.Fields!TotalRegistros.Value
' Ejecutamos la consulta de creación de tabla, pasándole una variable
' del tipo Long que devolverá el número de registros afectados por la
' operación.
'
cnn.Execute _
"SELECT * INTO Tabla2 FROM Tabla1", lngRegAfectados, adCmdText
MsgBox lngRegAfectadosPara los usuarios de ADO .NET:
With cmd
.Connection = cnn
.CommandText = "SELECT * INTO Tabla2 FROM Tabla1"
' Ejecutamos la consulta de creación de tabla y leemos
' los registros afectados
'
MessageBox.Show(CInt(.ExecuteNonQuery()))
End WithEjemplos
1. El siguiente ejemplo mostrará cómo exportar todos los campos de una tabla de Access a otra base de datos externa Microsoft Access utilizando la biblioteca de ADO:
Dim cnn As ADODB.Connection
Dim lngRegAfectados As Long
Dim SQL As String
' Creo la cadena SQL
SQL = "SELECT * INTO [Copia Clientes] IN ''[C:\Mis documentos\Bd2.mdb] FROM Clientes"
' Creo un objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mis documentos\bd1.mdb"
' Abro la conexión
.Open
' Ejecuto la consulta de creación de tabla
.Execute SQL, lngRegAfectados, adExecuteNoRecords
' Cierro la conexión
.Close
End With2. A continuación, vamos a importar una tabla de una base de datos SQL Server a nuestra base de datos Microsoft Access, utilizando el driver ODBC para conectarnos a la base de datos de SQL Server:
Dim cnn As ADODB.Connection
Dim lngRegAfectados As Long
Dim SQL As String
' Creo la cadena SQL de creación de tabla
SQL = "SELECT * INTO [Employees] FROM Employees " & _
"IN ''[ODBC;Driver={SQL Server};" & _
"Server=Nombre_Servidor_SQL;" & _
"Database=Northwind;" & _
"UID=sa;PWD=]"
' Creo un objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión con la base de datos de Access
' donde deseo importar la tabla
'
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mis documentos\bd1.mdb"
' Abro la conexión
.Open
' Ejecutamos la consulta
.Execute SQL, lngRegAfectados, adCmdText
' Cierro la conexión
.Close
End With3. Por último, vamos a crear un archivo de texto delimitado con los datos de una tabla de Access 2007, utilizando ADO .NET:
' Importamos el siguiente espacio de nombre
'
Imports System.Data.OleDb
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
' Configuramos y abrimos la conexión con la base de
' de datos de Access.
'
Using cnn As New OleDbConnection( _
"Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Mis documentos\Database1.accdb")
cnn.Open()
' Creamos el objeto Command
'
Dim cmd As OleDbCommand = cnn.CreateCommand()
' Creamos la cadena SQL de creación de tabla para el exportar
' los datos de dos campos de la tabla Clientes.
'
cmd.CommandText = "SELECT IdCliente, Nombre INTO Clientes#txt" & _
" IN 'C:\Mis documentos' 'TEXT;HDR=Yes'" & _
" FROM Clientes"
' Ejecutamos la consulta.
'
cmd.ExecuteNonQuery()
MessageBox.Show("Se ha creado el archivo de texto.", _
"Exportar tabla", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Using
Catch ex As Exception
MessageBox.Show(ex.Message, _
"Error al crear el archivo de texto", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try
End SubEliminar una tabla
Mediante la instrucción DROP TABLE del lenguaje de definición de datos (DDL) de SQL, podemos eliminar tablas de una base de datos Access, así como de otros formatos de bases de datos de escritorio.
Sintaxis
DROP TABLE [nombre tabla]| Parámetro | Descripción |
| nombre tabla | El nombre de la tabla que se desea eliminar. |
Comentarios
Al igual que el resto de las instrucciones del lenguaje de definición de datos vistas con anterioridad, la instrucción DROP TABLE se utiliza como argumento del método Execute de un objeto Database abierto (DAO) o de un objeto Connection (ADO), al igual que por el método ExecuteNonQuery de un objeto OleDbConnection del espacio de nombre System.Data.OleDb de ADO .NET, siendo su único objetivo eliminar una tabla de una base de datos.
Hay que tener presente que el usuario deberá de tener los permisos necesarios para poder eliminar la tabla, porque de lo contrario, obtendremos el correspondiente error.
Asimismo, si la tabla que deseamos eliminar es la tabla principal de una relación de uno a varios con una tabla externa (es decir, el lado uno de dos tabla relacionadas), necesitaremos primero eliminar la relación para posteriormente poder eliminar la tabla. Por ejemplo, si tenemos una relación establecida de uno a varios entre las tablas Clientes (parte 1) y Facturas (parte varios), mediante la instrucción DROP TABLE no podemos eliminar la tabla Clientes, lo que no impide que sí podamos eliminar la tabla Facturas. Para eliminar la tabla Clientes, primero tendremos que eliminar la relación con la instrucción ALTER TABLE y posteriormente ejecutar la instrucción DROP TABLE.
' Eliminamos la relación existente
ALTER TABLE Facturas DROP CONSTRAINT ClientesFacturas
' Eliminamos la tabla Clientes
DROP TABLE ClientesAl igual que se puede crear una base de datos ISAM mediante instrucciones SQL, también podemos eliminar archivos de otros formatos de bases de datos con la instrucción DROP TABLE.
Por último indicar que, antes de ejecutar la instrucción DROP TABLE, asegúrese que la tabla no se encuentra abierta.
Ejemplos
1. El siguiente ejemplo eliminará del disco el archivo Clientes.txt. Nótese que si se tiene un archivo de configuración de esquema Schema.ini, también se eliminará del mismo las entradas correspondientes al archivo de texto eliminado.
Dim cnn As ADODB.Connection
' Creo un nuevo objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos"
cnn.Properties("Extended Properties") = "TEXT;"
' Abro la conexión
cnn.Open
' Ejecuto la consulta de eliminación de tabla
cnn.Execute "DROP TABLE [Nombre Archivo#txt]"
' Cierro la conexión
cnn.Close2. El siguiente ejemplo eliminará de nuestra base de datos una tabla cualquiera:
Dim cnn As ADODB.Connection
' Creo un nuevo objeto Connection
Set cnn = New ADODB.Connection
' Establezco la cadena de conexión
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0""
.ConnectionString = "Data Source = C:\Mis documentos\bdl.mdb"
' Abro la conexión
.Open
' Ejecuto la consulta de eliminación de tabla
.Execute "DROP TABLE [Nombre Tabla]"
' Cierro la conexión
.Close
End With3. Por último, mediante ADO .NET eliminaremos los valores existentes en una hoja de trabajo de Excel correspondiente con el rango de celdas con nombre especificado:
' Importamos el siguiente espacio de nombre
'
Imports System.Data.OleDb
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
' Configuramos y abrimos la conexión.
'
Using cnn As New OleDbConnection( _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\Libro1.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes;'")
cnn.Open()
Dim cmd As OleDbCommand = cnn.CreateCommand()
' Creamos la consulta SQL.
'
cmd.CommandText = "DROP TABLE [Clientes]"
' Ejecutamos el comando.
'
cmd.ExecuteNonQuery()
MessageBox.Show("Se ha eliminado la hoja de trabajo.", _
"DROP TABLE", MessageBoxButtons.OK, _
MessageBoxIcon.Information)
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
0 comentarios