Sistemes gestors de bases de dades Access i VisualBasic Joan Pijuan Cinca / 2001 Metodes d'accés a la informació • Sequencial • Ocupen poc espai al disc. • Son lents per a manipular. • Dades van una darrere d'altre, encara que a l'hora d'actualitzar dades es imposible. S'ha de tornar a llegir, guarda'l en memòria i torna'l a escriure. • Van molt bé per arxius petits, i per a coses constants (mesos, dies de la setmana). • Directe • La seva diferència amb el sequencial es la forma de grabar les dades al disc. Es fan reserves d'espai. • L'advantatge es que a l'hora de modifica'l permet treure el registre sense necesitat de tocar els altres. • Inconvenients, ocupen molt més espai al disc. • Són ràpids. Recuperació de dades en un arxiu directe 1. Indexats : Són aquells arxius que mitjançant un index establert prèviament permet la recuperació d'un registre. • Trencat de clau : (amb el DNI per exemple) 45432614 es parteix pel mig 4543 + 2614 = 7157 i es sumen les parts Té grans problemes ja que apareixen claus repetides. Es poden trobar varies sol.lucions, però es generen sempre duplicats. • Algoritme del centre elevar al quadrat 422377142 = 1784024483945800 02448394 és la clau, encara que també pot generar duplicats • Arxius indexats (es generen dos arxius) Arxiu mestre Arxiu index1 (en ordre A−Z) 1 450001 Josep 111123 3 2 732430 Joan 450001 1 1

3 111123 XXX 732430 2 Arxiu index2 Joan 2 Josep 1 XXX 3 Objectius dels Sistemes Gestors de Bases de Dades • Independencia d'altres dades, tant física com lògica. • Reserva i seguretat de les dades. • Integritat de les dades. • Còpia i recuperació de les dades. • Redundància mínima. • Consistència. • Capacitat d'auditoria. • Control de concurrència (simultaneitat) • Capacitat de recerca. • Capacitat per a la representació de relacions. • Compartició de dades. • Disponabilitat d'un llenguatge d'interrogació. • Independència física: A l'hora de fer crèixer l'arxiu en camps no es perdi cap dada. Mantenir independència de la màquina on està posat. Independència lògica: Al fer una modificació en les dades, s'ha de seguir veient al canviar de programa. • Reserva i seguretat de les dades: Control d'accés a la informació per usuaris sense permís. La reserva fa referència al permís de la informació per part dels propietaris de la mateixa. • Integritat de les dades: • Fallades de hardware (apagades de llum...) • Defectes de software (accesos indeguts...) • Processos incomplerts (sumar 5% als preus i de sobte marxa la llum) • Inserció de dades no vàlides o incoherents (Data naixement = −100) • Valors de claus descontrolats o no provistos (lletra final del DNI) • Integritat referencial. • Còpia i recuperació de les dades: • Recuperació endavant : Agafant l'última còpia i restaurar. • Recuperació enrera: El sistema usa transaccions i guarda historial. • Redundància mínima: Cal evitar en la mesura posible la repetició de dades. • Consistència: Sempre les dades son iguals. • Capacitat d'auditoria: Capacitat d'emmagatzemar totes les accions fetes a les dades i qui les fa. (Guardar les accions/usuari). • Control de simultaneïtat (scheduler) • Disposem de dos programes (P1 i P2) que accesdeixen a les mateixes dades. Caldrà bloquejar els 2

registres que treballen i actualitzar−los abans que un altre programa intenti llegir o modificar−los. • Capacitat de recerca: Un SGDBDD disposarà d'eines que permetin fer recerques eficaces (querys) sobre la BD. • Capacitat de la representació de relacions. • Compartició de dades: El SGDBDD, permetrà que diferents usuaris accedeixin a la informació. • Disposaran d'un llenguatge d'interrogació (en Acces es SQL stàndard) − Ex: SELECT1*FROM CLIENTES2 WHERE [Població]3 = Barcelona • Tots els camps • Nom de la Taula • Condició de la selecció. Gestió d'una BD sota Accés: − Consultes : Permeten recuperar informació seguint criteris concrets. Donem com a resultat de la recerca una vista que conté els registres que compleixen les condicions. Dintre del camp CRITERIO podem fer la prova: =[Missatge a l'usuari] (així l'usuari és qui selecciona el filtre de cerca) − Consultes d'eliminació: Permet esborrar registres d'una taula que compleixin una o varies condicions. Ex: Ens pot interesar esborrar registres d'una forma concreta, podem així eliminar grups de registres caducats, families de productes desfasades, etc... • Anar a Consulta • Crear una nova consulta • Incloure taules per treballar • Buscar CONSULTA DE ELIMINACION en el menu desplegable. • Triar els camps que permetran fer el filtre d'eliminació ( ex: [FAMILIA] i [ESTOC] ) Aixi poden crear una consulta on els camps del filtre sigui FAMILIA i el criteri d'eliminació sigui [ESTOC] = 0. − Consultes d'actualització: Permetrà mitjançant una Macro (per exemple) fer un recompte de entrades d'articles o moviments creats en una taula que tinguem d'entrades de productes i que no estiguin actualitzats. Aquesta consulta farà els següents passos: • Llegir tota la taula d'Entrades_articles cercant registres que no estigui actualitzats. • Quan en trobi un amb aquesta condició, incrementarà l'estoc amb la quantitat comprada i canviar el seu P_Cost. • Marcar el moviment com actualitzat. Ex: Dins del disseny d'aquesta consulta posarem que la Taula Article el camp [Existencias] s'actualitzi a =[EXISTENCIA]+[QUANTITAT]. El camp [P_Cost] de la mateixa taula quedarà actualitzat a P_Compra. El camp Actualitzat de la taula [E_articles] tingui com a criteri NO i quedi canviat a SI despres de fer 3

l'actualització. − Formularis: Un formulari és una vista d'un registre de la taula. Assistent de formularis : • Indicar quins camps es volen afegir. • Distribució dels camps. • Aspecte. • Titols. • Fi Ex: • Generar un formulari per la taula AMICS que contingui tots els camps. • Formulari que contingui : NOM, COGNOMS, TELEFON • Generar taula nova, anomenada poblacions, que contindrà només el nom de les poblacions de la taula amics. • Afegir poblacions que usarem normalment. • Crear un formulari en la taula poblacions, triar el tipus de tabulat. − Informes: Són llistats de dades que son imprimibles en paper. Per exemple podem fer una tria a l'hora de fer un informe. D'una taula principal podem fer una consulta on triarem només els clients de sabadell sobre aquest resultat fer un informe. Ex: Verificar que tenim d'alta una impresora. Generar un informe de la taula amics que mostri totes les dades ordenades pel cognom. Generar informe que utilitzi la consulta de poblacions. Generar informe de la taula amics que agrupin els registres per població i ordenats pel nom. − Relacions: Una relació és un lligam entre dues taules que garanteix coherència i consistència amb les dades contingudes en elles. Tipus de relacions: U a U: U a molts: 1 Gerent −> Joan Terrassa : Josep,Joan,Marta 2 Comptable −> Josep Barcelona : Pere,Martí 3 Admº −> Sònia Rubí : Sandra, Judith Molts a molts: Access permet aquesta relació on es fa mitjançant una taula conectora. Aixi un professor pot estar en varies clases i la mateixa clase tindre molts professors. Les relacions dins d'Access es realitzen mitjançant el seguent botó: • Demanarà les taules a relacionar. 4

• Procedir amb la relació: • Els tipus de camp han de ser iguals, les llargades també • El camp de la relació 1 ha de ser el camp principal el camp 2 la clau principal, la relació es crea arrosegant el camp 1 sobre el 2 (drag'n'drop). • Un cop creada la relació ens demanarà si volen la integració referencial, això ens actualitzarà per exemple noms de poblacions (de bcn a Barcelona) en tots els camps automaticament, o borrarà per exemple tots els clients de Barcelona si eliminem la clau Barcelona de la Taula Poblacions. − Macros: Són objectes que executen una sèrie d'ordres a Access. Així per exemple podem obrir un formulari nomes iniciar una sessió amb una BD. • Crearem una macro (Nueva) • En el camp acción hi posarem Abrir formulario... • En la seguent acció podem posar per exemple Maximizar. * Per tal que aquesta macro s'executi automàticament al iniciar la BD, cal guardar−la amb el nom AUTOEXEC. Camps calculats: Els camps calculats són el resultat de càlculs amb altres camps, no es guarden mai en les taules. Els podem incluir en consultes, formularis i informes. Consulta: • Crear una consulta de selecció, sense condicions. • Posar els camps desitjats a la graella. • En el camp beneficis (p.ex) podem posar [PVP]−[P_cost], on el camp beneficits contindrà el resultat de restar el camp PVP menys el camp P_cost. Formulari: • Necesitem saber el nom dels camps a calcular. • Generar els camps allà on vulguem veure'ls. • Introduir la fòrmula de càlcul a l'objecte. • A la línia origen del control cal posar la fòrmula de càlcul. Origen del control: [PVP]−[P_Coste] • Podem inclus crear formules que es calculin automàticament al fer un formulari. Ex: ( [PVP]−[P_Cost] )*100 / [PVP] Informes: • Necesitem un llistat valorat de les nostres existències. • Crear informe amb l'assistent que tingui. • Obrir informe amb el botó DISEÑO, seguir els pasos anteriors. Exemples pràctics : Compra / Venda Per tal de controlar amb Access l'entrada / sortida de productes de la nostra empresa podem confeccionar una taula anomenada Entrada_Articles, on es reflexaran els moviments de compres d'articles. Aquesta pot 5

contindre els següents camps: Posicio | Autonumerico (camp clau), Data compra | Fecha/Hora, Article | Texto[12], Quantitat | Numerico, Preu cost | Numerico, Observacions | Texto, Actualitzat | Sí/No. Aquest formulari fara les anotacions de les compres d'articles. Un cop anotat sortirà el formulari i una macro dispararà la consulta d'actualització d'estocs que recalcularà tots els moviments no pasats (Actualizat = No). La consulta d'actualització haurà de llegir tota la taula Entrada_Articles, cercant els registres que no estiguin actualitzats. Quan en trobi algun, cercar l'article i incrementar el seu estoc amb la quantitat comprada i canviar el seu Preu de Cost. Després caldrà marcar el moviment com a actualitzat. Per tal de confeccionar els moviments de sortida (vendes) cal sapiguer uns passos previs. Els documents que intervenen en una venda son els seguents: • Albarà : És el justificant de un lliurament/recepció de material. El nostre client signarà el document. Normalment no hi ha totals. • Factura: És el document que recull els diferents albarans lliurats al llarg d'un periode (mes, setmana, ...). • Rebuts: Són els justificants que donarem al nostre client quan pagui la factura pendent de cobrament. Per a la confecció d'albarans : Camp Tipus Camp Tipus N_Albarà Numèric Contador Autonumerico Data Fecha/Hora N_albara Numèric Cod_Client Numèric Cod_article Text[12] Observacions Text[40] Quantitat Numèric P_unitari Numèric Capçalera Albarà (CAP_ALB) : El pas següent és crear un formulari sense cap assistent, anar a vista diseño i conectar el formulari amb la taula CAP_ALB. Afegir al quadre combinat tots els camps que necesitaran (codi, nom, adreça, CP i Poblacio), després composar les dades del quadre combinat a les etiquetes: • Fer clic amb el BD sobre el q.combinat. • Triar Propiedades. • Cercar l'event al hacer clic. • Obrir opció Generar código. · Sub Cuadro_combinado01_click() Etiqueta1 = Cuadrocombinado1.Columns(1) Etiqueta2 = Cuadrocombinado1.Columns(2) ... 6

Disseny de les LINIES_ALB : Per poder disposar d'un numerador fiable de documents, modifiquen el camp N_Albara pel tipus Autonumérico, això garanteix un numero únic que no es repetirà mai. Per tal de crear les linies dissenyarem un formulari amb l'assistent del tipus Hoja de datos que quedarà així. Després utilitzar de la barra d'eines el botó: Subformulario / Subinforme. L'assistent demanarà d'on prové la informació del subformulari, triarem el disseny anterior. Posat que el nostre formulari reflexa les vendes de productes caldrà doncs fer un control d'estoc , així un cop tret l'albarà, caldrà disposar de les accions que permetin restar de l'estoc cada article que hem venut. • Una consulta que faci la resta. • Una consulta que posi les marques del moviment traspasat • Una macro que ho lligui tot. Eines i utilitats de MSAccess : • Analizar tablas: Utilitat per avaluar el disseny de la BD i proposa modificacions a la BD. • Analizador de rendimiento: Utilitat que mira d'utilitzar el funcionament / velocitat de la BD. • Informa de compactar la BD si té molts registres borrats. • Aconsella convertir la BD a format MDE (més simple i més ràpid). • El Documentador: Genera llistats de l'estroctura de les taules. • Convertir la BD: ATENCIÓ les migracions no son bidireccionals. • Compactar / Restaurar: Al compactar s'eliminen per sempre els registres esborrats, genera una BD més petita i accelera la gestió de la BD. Al restaurar s'intentarà recuperar una BD malmesa. • Crear archivo MDE: Genera una BD en format MDE. No permetrà ni modificar / crear / borrar / dissenyar les taules, consultes, informes i macros. Si es troba codi en VB el compila i genera codi més eficient i ràpid. • Menu inicio: • Titulo de la aplicación: És el nom que apareixerà a la barra superior de l'aplicació. • Icono de la aplicación, Barra de menús, Administrador del Panel de Control. • Disseny de Barres d'eines personalitzades : Hi ha de dos tipus, la barra de herramientas (son aquelles que floten per sobre de la aplicació) i la barra de menús (son les que apareixen a la part superior de l'aplicació). Per a crear una barra nova: Ver / Barra de herramientas / Personalizar. Crearem una barra personal nova, després premem sobre Propiedades de la Barra Personal, en el quadre de Tipo de barra seleccionarem la nostra barra de menús. • Arrosegarem la nova barra de menús a la zona superior de l'aplicació. • Prémer la opció Comandos. • Seleccionar Todos los formularios. • Si volem afegir un botó adicional que ens permeti sortir del programa, podem buscar per la secció de Categorias i buscar l'opció Salir, arroseguem l'icona a la nostra barra. • Podem modificar la imatge dels nostres botons amb el botó dret del ratolí (podrem afegir imatges...). • Si preferim crear una barra d'eines. La podem mostrar o bé amagar quan volguem, utilitzant un botó que cridi a una macro. 7

Gestió d'un BD sota Visual Basic : Vbasic és un llenguatge de programació orientat a l'event que treballa amb objectes. • Formularis : Són els contenidors d'objectes, és el que veu l'usuari final *.FRM o *.FRX (amb imatges). • Arxiu de projecte: És l'index de formularis que composen un projecte (*.VBP). • Moduls : Arxius que contenen definicions de variables (*.BAS). Events més bàsics: • Load : Quan carrega. • Activate : A l'activar la form. • UnLoad : al descarregar (quan es tanca) • Click / DblClick ... • MouseMove / Down / Up ... Ex1. Canviarà el color de fons de la nostra aplicació : El següent codi pot ser escrit dins de qualsevol event en un boto, quan es mou el ratolí... If FORM1.Background = &H00FF& then FORM1.Background = &FF00H& Else FORM1.Background = &H00FF& End if Consells bàsics per programar sota Vbasic : • Si volem mostrar el resultat d'una suma sota VB poden afegir a la form l'objecte Text, mostrant−lo per pantalla aixi: Numero = VAL(text1.text) Podrem sumar varis camps i mostrar el seu resultat en un camp sol. • Per definir variables utilitzarem la sentencia DIM tenint com a tipus de variables Integer, Currency, String.. Si volem tindre una variable de caràcter local, només caldrà definirla dins d'un objecte, serà accesible desde dins d'aquell objecte. Si volem que sigui de caràcter global la incluirem dins d'un módul, definintla com: Public Numero as ... Tipus de variables sota VB son : String : Cadena de caracters de 65535 caracters .

8

Integer : Nº sense decimals −32768 .. 32767 LongInteger: −2147483648..2147483647 SinglePrecision: Nº amb decimals coma flotant fins 7 digits. SoublePrecision: Nº amb decimals amb 300 digits i 16 digits de precisió. Currency : 14 xifres part sencera i 4 en decimal. Operadors: +,−,*,\,Mod,<,>,AND,OR,NOT ASCIII bàsic: 27 = ESC 8 = BACKSPACE 13 = ENTER 64 = @ 32 = SPACE 65 = A 90 = Z 48 = 0 57 = 9 45 = − 46 = . Instruccions per manipular cadenes de caracters: Space(n) = retorna un codi de n espais en blanc. String(n,caracter) = retorna una string de n caracter. Left/Right(cadena,n) = retorna el n caracter primer per l'esquerra / dreta. Mid(cadena,n,m) = retorna m caracter a partir de l'n. Len(cadena) = Nº caracters de la cadena. Lcase = converteix cadena a minuscules. Vcase = converterix cadena a MAJUSCULES. Trim : treu espais en blanc a les dues bandes de la cadena. Rtrim / Ltrim = treu els espais en blanc de la dreta / esquerra. Val = Converteix la cadena nombres a lletres. Misatges a l'usuari (MSGBOX) Crear primer una variable OPCIO per exemple que recullirà la opció triada per l'usuari. Opcio = MSGBOX (Misatge d'avís, tipus d'icona + botons, misatge de capçalera) Tipus de botó: 0 = Aceptar 1 = 0 + Cancelar (=ESC) 2 = Anular, Reint, Ignorar

9

3 = Sí/No/Cancelar 4 = Sí / No 5 = Reintentar / Cancelar Respostes (variable Opcio): 1 = Aceptar 2 = Cancelar 3=Anular 4=Reintentar 5=Ignorar 6 = Sí 7 = No Icones: 16 = Stop 64=Informació 32=Qüestió 48=Admiració Focus al botó: 0 = focus al 1er botó 256= focus al 2on botó 512=focus al 3er botó Mostrar les dades en altres formularis per pantalla : Form2.Visible = true Dim n as integer For n=1 to 10 Print Codi(n); Print tab(6);Nom(n); Print tab(20);Cognom(n); Print tab(60);Telefon(n) Next n ; no farà un salt de linia tab(n) colocarà la informacio a n tabulacions desde l'esquerra de la form. Ex2. Programa que faci sumes : Numero1 = VAL(text1.text) Numero2 = VAL(text2.text) Suma = Numero1+Numero2 Label1.Caption = STR(SUMA) Command1.Setfocus Per filtrar la informació introduida podem :

10

If KeyASCIII = 13 then `Tecla ENTER Text2.Setfocus KeyASCIII = 0 `aixi acaba la deteccio de l'ENTER Exit Sub If (KeyASCIII<48) or (KeyASCIII>57) then Msgbox ATENCIO caràcter invàlid `Ni lletres ni signes rars. KeyASCIII = 0 Text1.setFocus Gestió de BD sota Vbasic (amb l'objecte Data) : Es pot accedir a les dades mitjançant l'objecte Data, això ens permetrà una programació directa sobre les dades. Aixi un cop creada la BD amb Access per exemple (sense formularis ni consultes) la podrem lligar al nostre projecte de Visual amb l'objecte Data, amb el RecordSource de les seves propietats l'obrirem. Per lligar els texts amb el Data creat anirem a les seves propietats i modificarem el seu DataSource (posarem el data) i el DataField, on indicarem quin camp de la BD volem que aparegui. Tenim d'altres propietats amb l'objecte Data: • MoveFirst : Posa el punter al 1er registre. (Data1.RecordSet.MoveFirst) • MoveLast : Posa el punter al l'ultim registre. • MoveNext / Previous. • EOF / BOF : Indica si es inici o fi del fitxer. • Delete : Borra el registre actual. • AddNew : Posa el punter a l'ultim registre i en crea un de nou. • Edit : Bloqueja i permet modificar el registre actual. • Update : Opció que cal fer després d'afegir/esborrar els registres. (Només es pot fer un Update). Exemples: If Data1.RecordSet.EOF then Data1.Recordset.MoveLast End if Cal tindre cura amb els camps buits a l'hora d'afegir nous registres, una de les solucions més bones al crear el disseney de la BD és permetre la opció de permitir longitud cero en els camps de la nostra taula. Si no es fa així caldrà fer un control d'errors per a cada casella de text. Objecte MsFlexGrid per a manipular dades. Aquest objecte ens permetrà l'acces a informació d'una forma molt visual ja que ens mostrarà per pantalla tota 11

la informació de les taules en una graella semblant a una d'Excel. Aquest objecte es pot conectar amb un Data permetent aixi una gestió molt cómoda, tenint propietats com: *Aquest objecte no permet tindre 0 registres (0 fileres) per tant mai pot estar buit. DataSource : Camp que ens permetra seleccionar quin Data volem mostrar dins de la Grid. ColWidth(n)=m : permet modificar l'amplada m de la columna n de la grid. FormatString = Permet canviar els titols de les columnes, així : MsFlexGrid1.FormatString = Cognoms < Align esquerra > Align dreta ^ Align centre | Separador de columna Mètodes bàsics d'una MsFlexGrid: Clear AddItem (afegir filera) RemoveItem (Elimina filera creada) Sort (ordena per la columna indicada) Gestió d'una BD sense l'objecte DATA : Per tal d'accedir a la informació directament sense cap Data, afegirem uns nous tipus de variables: Dim BD as DataBase Dim Tb as recordset *Es posible que calgui activar en el menu de Proyecto / Referencias, les referències a Microsoft 3.5 DAO Library, per tal que VB acepti els tipus anteriors de variables. Al carregar el formulari caldrà obrir les BD i les taules que utilitzarem (Load del formulari) i tancar−les quan tanquem l'aplicació. Obrir BD Set BD = OpenDataBase (C:\~\exemple.mdb) Obrir una taula Set TB = BD.OpenRecordSet (Clients,DbOpentable) 12

DbOpenDynaset DbOpenSnapshot Tancar taula / BD Tb.Close Db.Close Noms dels camps que hereden son : Noms a la taula CODI

Noms de les variables VB TB.RECORDSET!CODI

Noms generics TB.RECORDSET.FIELDS(0)

NOM

TB.RECORDSET!NOM

TB.RECORDSET.FIELDS(1)

COGNOMS

TB.RECORDSET!COGNOMS

TB.RECORDSET.FIELDS(2)

D_NAIXAMENT

TB.RECORDSET!D_NAIXAMENT TB.RECORDSET.FIELDS(3)

Per a pasar les dades desde la BD al text ho podem fer mitjançant un procediment : Carrega_text() Text1.text = TB.RecordSet!CODI Text2.text = TB.RecordSet!Nom Text3.text = TB.RecordSet!Cognoms Algoritmes de conversió de claus. Sistema gestor de bases de dades.

Albarans Factura ABC...3200

13

ACB....200 Total: 3400 Dades client Signatura Quantitat en € Dades del banc REBUT LOGOTIP EMPRESA Codi client Nom Adreça CP Poblacio Data: NºAlbarà Observacions: CODI DESCRIPCIÓ PREU UNITAT QUANTITAT TOTAL Taula CAP_ALBARA Taula LINIES_ALB Nº Albarà Article Descripció Quantitat Preu unitat Lligat a la taula d'articles OK

14

Cancelar ERROR GREU

15