Data files and databases

1. Data files and databases

2. Data files and databases • Need a control to browse to a file • Standard controls for drive folder and list not much use • The CommonDialogs control offers • drive folder file browse to select file • print • font select • colour select • But you need to add it in..

3. Adding components

4. Using the Common Dialog Control Private Sub Command1_Click() CommonDialog1.ShowOpen MsgBox ("You selected " & CommonDialog1.FileName) .. code to process FileName End Sub

5. File open exercise Program to browse and display graphics files Use a common dialog control to select graphics files Set the filter of the dialog to something like: Image files | *.bmp; *.jpg; *.jpeg | All files | *.* Display the selected image in a Picture control (see graphics)

6. VB data file ideas 2 modes – • sequential (work through file start to end - character (external format)) • random (can move back and forth through file, binary (internal format) )

7. Sequential – write # and input# Open "c:\walter\vb\filing\test1.dat" For Output As #1 Write #1, 1, 2, 3.1, "Test1", "Test2" Close #1 Open "c:\walter\vb\filing\test1.dat" For Input As #1 Input #1, x, y, z, a, b Close #1

8. Sequential – Print # and Line Input# Open "c:\walter\vb\filing\test1.dat" For Output As #1 Print #1, 1, 2, "Test1" Close #1 Open "c:\walter\vb\filing\test1.dat" For Input As #1 Line Input #1, x Close #1 MsgBox (x)

9. Exercise – sequential filing • Fill an array with 100 random integers • Save them into a file ( write# ) • Read file back ( input# ) into a second array • Check arrays have same content

10. Random • Data in file as records • Must define size of record (fixed length good) • Can then seek to nth record • And read or write it

11. User-defined type (record) Private Type myRecord payroll As Integer department As Integer name As String * 14 End Type fixed length string length of record = 2 + 2 + 14 = 18 bytes

12. Writing to random access file Dim emp As myRecord emp.payroll = 23 emp.department = 3 emp.name = "Fred" Open "c:\walter\vb\filing\test.dat" For Random As #1 Len = 18 Put #1, 1, emp emp.payroll = 17 emp.department = 6 emp.name = "John" Put #1, 2, emp Put #1, 3, emp Close #1 record position 1 result on next slide

13. Random file contents record 1 record 2 record 3 2 bytes = integer = 23 string padded with spaces to length 14 2 bytes = 1 integer = 3

14. Reading Random File Dim emp As myRecord Open "c:\walter\vb\filing\test.dat" For Random As #1 Len = 18 Get #1, 2, emp Close #1 MsgBox (emp.payroll & " " & emp.department & " " &_ emp.name) read second record

15. Binary = random with record size 1 Dim b As Byte Open "c:\walter\vb\filing\test.dat" For Binary As #1 For bytePosition = 1 To 10 Get #1, bytePosition, b Debug.Print b Next Close #1

16. Exercise – data file • Using binary mode, write the characters A to Z into a file (ASCII 65 on) • Then read them back in reverse ie byte position 26 first • Debug.print them out

17. Working with a database – the hard way

18. Working with a database – the hard way

19. Working with a database – the hard way Private Sub Command1_Click() Data1.Recordset.Delete Data1.Refresh End Sub

20. Working with a database – the wizard Addins..Addin Manager

21. Working with a database – the wizard

22. Exercise – database access • Use Access to produce a simple database with just one table – personnel = ID name and department number (Access97 format) • In a VB project have two forms • one with a data control, hand-coded (explore insert delete buttons) • one created by the Data Form Wizard