By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,763 Members | 1,609 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Data management with XML, Text files using VB 6.0: Part One

Dököll
Expert 100+
P: 2,364
Continued from:

http://www.thescripts.com/forum/thread762010.html

-VB 6.0 Professional
-Microsoft DAO 3.6 Reference

Search Database table...

An attempt to fetch data housed in Access:

(1) This program attempts to search actual data in an Access database
(2) Build an Access database table with 5 text fields
(3) Read code below, add these fields starting with Your_Price as names
(4) Each field must correspond with the field names shown here in VB
(5) You must have an array of 5 textboxes, first in line Text1(0).Text
(6) You will need a fancy command button called whatever you want, 'Seek'
(7) ADD Microsoft DAO 3.6 Object Library
(8) Add code below...

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Base 1 'this option makes it that the program starts at number 1, always...
  3. Dim io() As String 'dimensioning for array to return values
  4. Dim prs_calc As Integer 'dimensioning to record person instances
  5. Dim ndvdl, filenum1 As Integer 'dimensioning to record instances for each instance of a person
  6. Dim my_string As String
  7. 'Your input box for instances being entered
  8. 'Your buttons will disapear depending on number added in for each entry
  9. Private Sub Form_Load()
  10. ndvdl = Int(InputBox("Add a number to box to continue", "Data Mining Required Info", 1)) 'this is the pop-up box for entry of persons by the user
  11.  
  12. 'making sure only digits are entered
  13. If IsNumeric(ndvdl) = False Then
  14.     MsgBox ("Please add numeric data to continue...")
  15.     'LoadTFile.Visible = False
  16.     Else 'If IsNumeric(Text3.Text) = True Then
  17.  
  18. ReDim io(ndvdl, 5)  'redimensioned for the purpose of data rows calculator
  19.     prs_calc = 1
  20.     End If
  21. End Sub
  22.  
  23. 'this is searching for existing data in local Access database
  24. Private Sub Seek_Click()
  25. Dim my_database As Database           'dimension database as database so program knows where to look for data
  26. Dim my_record As Recordset
  27. Dim test As String
  28. test = Text1(1).Text
  29. Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")   'this function will open the database using the link to the access database (provided that it is closed access)
  30. Set my_record = my_database.OpenRecordset("SELECT * FROM LIBRARY WHERE Your_Price LIKE '" & Text1(0).Text & "'")    ' this is used to search by name, only if data already exists
  31.    Do While Not my_record.EOF  'this function will keep searching for fields matching each textbox
  32.    'MsgBox ("got here")
  33.         Text1(0).Text = my_record.fields("Your_Price")
  34.         Text1(1).Text = my_record.fields("Name")
  35.         Text1(2).Text = my_record.fields("Type")
  36.         Text1(3).Text = my_record.fields("Crime_Rate_1")
  37.         Text1(4).Text = my_record.fields("Crime_Rate_2")     
  38.  
  39.    my_record.MoveNext
  40.    Loop
  41.    my_database.Close
  42. End Sub
  43.  
  44.  
TIP: Download SQL Server Management Studio Express to manage data from ASP.NET/SQL DB:

http://www.thescripts.com/forum/thread762010.html

SQL Server Management Studio Express helps load data gathered there to local Access DB, or vice versa...

SQL Server Management Studio Express facilitates query building. Use the query builder, appropriately named, to allow querying Access databases and others.

The SQL Server management tool is essential to the data that must be available to VB/VBA for futher observation. Please download SQL Server Management Studio to make data available to VB/VBA applications

http://www.microsoft.com/downloads/d...displaylang=en


-VB 6.0 Professional
-Microsoft DAO 3.6 Reference

Add to Database table...

An attempt to submit data to Access database:

(1) This program attempts to add, delete, update data to the Access database
(2) Build an Access database table with 5 text fields
(3) Read code below, add these fields starting with Your_Price as names
(4) Each field must correspond with the field names shown here in VB
(5) You must have an array of 5 textboxes, first in line Text1(0).Text
(6) You will need a fancy command button called whatever you want, 'Seek'
(7) ADD Microsoft DAO 3.6 Object Library
(8) Add code below...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub subt_Click()   'this function will load entry into database
  3. 'dim as database to allow vb to interact with Access database seemlessly...
  4.         Dim my_database As Database
  5. 'open database to allow vb to add data to Access database seemlessly...
  6.         Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
  7. 'run insert statement query that will load data to your database
  8.         my_database.Execute "insert into Data_Central.LIBRARY(Your_Price, Name, Type, Crime_Rate_1, Crime_Rate_2) Values('" & Text1(0).Text & "','" & Text1(1).Text & "' , '" & Text1(2).Text & "' , '" & Text1(3).Text & "','" & Text1(4).Text & "')"
  9.         my_database.Close
  10. 'this variable serves to emptying your textboxes, part of reset button
  11.     R_Click
  12.  
  13. End Sub
  14.  
  15.  
Delete Data from database...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub dll_Click()
  3. 'dim as database to allow vb to interact with Access database seemlessly...
  4. Dim my_database As Database
  5. 'dim as Recordset to allow vb to interact with Access database seemlessly...
  6. Dim my_record As Recordset
  7. 'open database to allow vb to delete data from Access database seemlessly...
  8. Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
  9. 'run delete statement query that will remove data from your database
  10. Set my_record = my_database.OpenRecordset("select * from LIBRARY where Your_Price='" & Text1(0).Text & "'")
  11. If Not my_record.EOF Then
  12. my_record.Delete
  13. End If
  14. my_database.Close
  15. 'variable that should empty textboxes for future instances...
  16. R_Click
  17.  
  18. End Sub
  19.  
  20.  
Update Data in database...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Updt_Click()
  3. 'dim as database to allow vb to interact with Access database seemlessly...
  4. Dim my_database As Database
  5. 'dim as Recordset to allow vb to interact with Access database seemlessly...
  6. Dim my_record As Recordset
  7. 'run a little check to see if proper credentials are added before releasing info...
  8. If (Text1(0) = "") Then
  9. MsgBox ("Please put in Your price..."), vbOKOnly, "Data Mining Error"
  10. ElseIf (Text1(1) = "") Then
  11. MsgBox ("Please put in Item name..."), vbOKOnly, "Data Mining Error"
  12. Else
  13. 'open database to allow vb to update data to Access database seemlessly...
  14. Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
  15. 'run update statement query that will modify data to your database
  16. Set my_record = my_database.OpenRecordset("SELECT * FROM LIBRARY WHERE Your_Price='" & Text1(0).Text & "'")
  17. my_record.Edit
  18.                 my_record!Your_Price = Text1(0).Text
  19.                 my_record!Name = Text1(1).Text
  20.                 my_record!Type = Text1(2).Text
  21.                 my_record!Crime_Rate_1 = Text1(3).Text
  22.                 my_record!Crime_Rate_2 = Text1(4).Text
  23.  
  24. my_record.Update
  25. my_record.Close
  26. my_database.Close
  27.  
  28. End If
  29.  
  30. End Sub
  31.  
  32.  
Clear your textboxes...

Expand|Select|Wrap|Line Numbers
  1. Private Sub R_Click() 'this must clear the textbox upon entry of all data
  2.         Text1(0).Text = ""      
  3.         Text1(1).Text = ""
  4.         Text1(2).Text = ""
  5.         Text1(3).Text = ""
  6.         Text1(4).Text = ""
  7.  
  8. Text1(0).SetFocus       'this should set index back to first textbox
  9. End Sub
  10.  
Convert Data to XML file next...


-Add Microsoft XML v 3.0
-Add code below

Contiued from:

http://www.thescripts.com/forum/thread777267.html

-VB 6.0 Professional
-Microsoft XML v3.0 Reference

Converting Access Data to XML file...

An attempt to convert data from Access database to XML:

(1) This program attempts to transform Access data to XML
(2) Use Existing database table, load above code
(4) Each field must correspond with the field names shown here in VB
(5) You will need a fancy command button called whatever you want, 'Seek'
(6) ADD Microsoft XML v3.0 Object Library, in References
(7) Add Microsoft DAO 3.6 Object Library
(8) Add code below...

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Private Sub CreateXMFile_Click()
  4. 'dim as database to allow vb to interact with Access database seemlessly...
  5. Dim my_database As Database
  6. 'dim as Recordset to allow vb to interact with Access database seemlessly...
  7. Dim objRS As Recordset
  8.     dimension your FreeFile
  9.     Dim intFreeFile
  10. 'open database to allow vb to grab data from Access database seemlessly...    
  11.     Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
  12. 'run query that will collect data from your database
  13.     Set objRS = my_database.OpenRecordset("SELECT Your_Price, Name, Type, Crime_Rate_1, Crime_Rate_2 From LIBRARY")
  14. intFreeFile = FreeFile
  15. Open App.Path + "\App_Price.xml" For Output As #intFreeFile
  16.  
  17. 'build XML version number, and print column names for readibility...
  18. Print #intFreeFile, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>"
  19. Print #intFreeFile, "<!--  File Name: App_Price.xml -->"
  20.  
  21. 'printing out nodes and subnodes with data gathered from database...
  22. Print #intFreeFile, "<Find_It>"
  23.  
  24.     Do While Not objRS.EOF
  25.     Print #intFreeFile, "<Apartmnt>"
  26.         Print #intFreeFile, "<Your_Price>" & objRS.fields("Your_Price") & "</Your_Price>" _
  27.         & vbCrLf & "<Name>" & objRS.fields("Name") & "</Name>" & vbCrLf & "<Type>" & objRS.fields("Type") & "</Type>" _
  28.         & vbCrLf & "<Crime_Rate_1>" & objRS.fields("Crime_Rate_1") & "</Crime_Rate_1>" _
  29.         & vbCrLf & "<Crime_Rate_2>" & objRS.fields("Crime_Rate_2") & "</Crime_Rate_2>"
  30.     Print #intFreeFile, "</Apartmnt>"
  31.         objRS.MoveNext
  32.     Loop
  33.  
  34. Print #intFreeFile, "</Find_It>"
  35.  
  36.     objRS.Close
  37.     Set objRS = Nothing
  38.  Close intFreeFile
  39.     MsgBox ("What do you know, you have an XML file!")
  40. 'depending on number of instances added in input box upon entry, 
  41. 'you will have x amount of instances to enter, browse data through this application.  Button disappears after your max has been reached:-)  
  42.     If (prs_calc = ndvdl) Then     'this logic will take away button CreateXMFile if limit has been reached
  43.             CreateXMFile.Visible = False
  44.         End If
  45. End Sub
  46.  
  47.  
See next: http://bytes.com/forum/thread777278.html

-VB 6.0 Professional
-Microsoft DAO 3.6 Reference

Converting Access Data to TEXT file...

An attempt to convert data from Access database to .txt file:

(1) This program attempts to transform Access data to TEXT file
(2) Use Existing database table as in the above code
(3) Each field must correspond with the field names shown here in VB
(4) You will need a fancy command button called whatever you want, 'Seek'
(5) Add Microsoft DAO 3.6 Object Library
(6) Add code below...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub CreateTXTfile_Click()
  3. 'dim as database to tell vb we're using an Access database
  4. Dim my_database As Database
  5.  
  6. 'dim as Recordsetto tell vb we're using an Access database Recordset
  7. Dim objRS As Recordset
  8. 'add FreeFile so vb creates one locally
  9.     Dim intFreeFile
  10.  
  11. 'open the database to grab data
  12.     Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
  13.  
  14. 'open the Recordset to grab rows in a query
  15.     Set objRS = my_database.OpenRecordset("SELECT Your_Price, Name, Type, Crime_Rate_1, Crime_Rate_2 From LIBRARY")
  16. 'initiating FreeFile...
  17.     intFreeFile = FreeFile
  18. Open App.Path + "\App_Price.txt" For Output As #intFreeFile
  19.  
  20. 'Print column names for readibility
  21. Print #intFreeFile, "Your_Price" & vbTab & "Name" & vbTab & "Type" & vbTab & "Crime_Rate_1" & vbTab & "Crime_Rate_2"
  22.  
  23. 'run through database recordset until all data gathered to create TEXT file...   
  24.    Do While Not objRS.EOF
  25.  
  26.         Print #intFreeFile, objRS.fields("Your_Price") & vbTab _
  27.         & objRS.fields("Name") & vbTab & objRS.fields("Type") & vbTab _
  28.         & objRS.fields("Crime_Rate_1") & vbTab _
  29.         & objRS.fields("Crime_Rate_2")
  30.  
  31.         objRS.MoveNext
  32.     Loop
  33.  
  34.     'remember to do this...
  35.     objRS.Close
  36.     'IMPORTANT to do this to allow database to properly close
  37.     Set objRS = Nothing
  38.  
  39.         'primitive way of clearing textboxes, you can do better here
  40.         'do it with a for loop
  41.         Text1(0).Text = ""
  42.         Text1(1).Text = ""
  43.         Text1(2).Text = ""
  44.         Text1(3).Text = ""
  45.         Text1(4).Text = ""
  46.  
  47.  
  48.         'return to first textbox to continue searching, or other
  49.         Text1(0).SetFocus
  50.  
  51.         MsgBox ("What do you know, you have Text file(s)!")
  52.  
  53. 'close the file after you create it...    
  54. Close intFreeFile
  55.  
  56. End Sub
  57.  
  58.  
Load data newly created file...

This is a little different. It's necessary to add in a textbox to record number of values to return from the multidimentional array:

(1) A record counter is needed to keep instances program loads per user
(2) An input box is also needed to record instances of your entries

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub LoadTFile_Click()
  3.       'this collection button serves to redeeming all data written to file
  4.       'to be inputted back into program, dimensioning data and renaming them as "Info"
  5.       'Info1,2,3 and so on represent io array 1,2,3 so on and so forth
  6.     Dim my_string As String
  7.     Dim Info1 As String
  8.     Dim Info2 As String
  9.     Dim Info3 As String
  10.     Dim Info4 As String
  11.     Dim Info5 As String
  12.     'Dim Info6 As String
  13.     'Dim Info7 As String
  14.     'Dim Info8 As String
  15.  
  16.     Dim record_cntr, location_cntr As Integer
  17.     Dim user_req As Integer
  18.     Dim bomb
  19.     Dim test_string As String
  20.     Dim X As Integer 'x is a counter
  21.     Dim my_char As String
  22.  
  23.  
  24.     Text1(0).Text = Info1
  25.     Text1(1).Text = Info2
  26.     Text1(2).Text = Info3
  27.     Text1(3).Text = Info4
  28.     Text1(4).Text = Info5
  29.     'Text1(5).Text = Info1
  30.     'Text1(6).Text = Info1
  31.     'Text1(7).Text = Info1
  32.  
  33.  
  34.  
  35.  
  36.     test_string = Text3.Text
  37.     'test_l = Len(test_string)
  38.     Do While X < 10
  39.         my_char = InStr(X, test_string)
  40.         Select Case my_char
  41.         Case "1"
  42.         Case "2"
  43.         Case "3"
  44.         Case "4"
  45.         Case "5"
  46.         Case "6"
  47.         Case "7"
  48.         Case "8"
  49.         Case "9"
  50.         Case "0"
  51.         Case Else
  52.             MsgBox ("You must enter a number!")
  53.             bomb = 99999
  54.         End Select
  55.         X = X + 1
  56.     Loop
  57.  
  58.     If IsNumeric(Text3.Text) = False Then
  59.     MsgBox ("Please add numeric data to continue...")
  60.     'LoadTFile.Visible = False
  61.     Else 'If IsNumeric(Text3.Text) = True Then
  62.  
  63.  
  64.     If (bomb <> 99999) Then
  65.  
  66.                 user_req = Int(Text3.Text)
  67.     record_cntr = 1
  68.  
  69.  
  70.                     filenum1 = FreeFile
  71.                 Open App.Path + "\App_Price.txt" For Input As #filenum1        'file is opened as input because it is putting back into the program
  72.  
  73.  
  74.                     Do While Not EOF(filenum1)                                    'this do while will work until the end of the file...otherwise it will keep going
  75.                             Input #filenum1, Info1, Info2, Info3, Info4, Info5 ', Info6, Info7, Info8
  76.                             record_cntr = record_cntr + 1
  77.                     Loop
  78.                     Close filenum1
  79.  
  80.  
  81.                     If record_cntr < user_req Then
  82.                         MsgBox ("There are only " & (record_cntr - 1) & " records in file, we will show you all records.")
  83.                     End If
  84.  
  85.                 Open App.Path + "\App_Price.txt" For Input As #filenum1        'file is opened as input because it is putting back into the program
  86.  
  87.                 location_cntr = 1
  88.                     Do While Not EOF(filenum1)                                    'this do while will work until the end of the file...otherwise it will keep going
  89.                         Input #filenum1, Info1, Info2, Info3, Info4, Info5 ', Info6, Info7, Info8
  90.                         'all info in textbox bellow will come through line by line using "my_string.....+vbcrlf"
  91.                             If (location_cntr >= (record_cntr - user_req)) Then
  92.                                     my_string = my_string + Info1 + vbCrLf + Info2 + vbCrLf + Info3 + vbCrLf + Info4 + vbCrLf + Info5 + vbCrLf
  93.                             End If
  94.                             location_cntr = location_cntr + 1
  95.                     Loop
  96.  
  97.                 Close filenum1
  98.                         Text2.Text = my_string    'this text box return all data which have been recalled from file on harddrive or disk
  99.  
  100.  
  101. If (prs_calc = ndvdl) Then     'this logic will take away button add if limit has been reached
  102.             LoadTFile.Visible = False
  103.         End If
  104. End If
  105. End If
  106. End Sub
  107.  
  108.  

Added info:


-A user counter is also needed with the existing record_cntr
-The record_cntr along with user_req counter allow search by specific number
-A location_cntr is added to pinpoint the exact location of newly added entries
-The location_cntr find the position of the data and record counter loads number
-Textbox mentioned previously will hold number of entries needed to return
Example: if 1 is added in the textbox one row in the multidimensional array is
returned. 2, 3 and so forth would have likely results


Note: Admin or power user pages, using ASP.NET code, were created to load data to an SQL Server Database. This data will now be loaded to a user-defined website for browsing ability. Data management through VB/VBA allows XML/XSL and/or text data to load to below HTML/JavaScript driven website:

http://bytes.com/forum/thread672308.html
Mar 1 '08 #1
Share this Article
Share on Google+