could any one explain this code for me ?

Hi expert. could any one explain this code for me. I do not understand some parts of it. I know the output but not some parts of the code.Thanks

Code:

---





Private Sub processButton_Click()

  Dim fso      As New Scripting.FileSystemObject  <------
  Dim io        As Scripting.TextStream    <------

 
  Dim db        As DAO.Database
  Dim rst      As DAO.Recordset
  Dim fld      As DAO.Field
  '''Declreaing our variables
  Dim strBase  As String
  Dim strInsert As String
  Dim strFields As String
  Dim strValues As String
  Dim strTemp  As String
  Dim strFile  As String
  Dim strName  As String
 
  Set db = CurrentDb()
 
  Set rst = db.OpenRecordset(Me![ComboBox])
 
  strBase = "INSERT INTO " & Me![ComboBox] & "({%1}) VALUES ({%2})" <------

  strName = "c:\" & Me!ComboBox & " Data.sql"

  With rst  <------

    While Not .EOF
      strValues = ""  <------

   
            If Len(strFields) = 0 Then
        For Each fld In .Fields
          If Len(strFields) > 0 Then
     
            strFields = strFields & "," & fld.Name & ""
          Else
            '''strFields = "[" & fld.Name & "]"
            strFields = "" & fld.Name & ""
          End If
        Next fld
        strInsert = Replace(strBase, "{%1}", strFields)
      End If

          For Each fld In .Fields
        If Len(strValues) > 0 Then
          strValues = strValues & ","
        End If
       
       
        If IsNull(fld.Value) Then
       
      strValues = strValues & "null"
        Else
        v = fld.Value
        Select Case fld.Type
          Case dbMemo, dbText, dbChar
            strValues = strValues & "'" & v & "'"
          Case dbDate
            strValues = strValues & "#" & v & "#"
          Case Else
            strValues = strValues & v
        End Select
       
        End If
             
       
       
       
      Next fld
     
          strTemp = Replace(strInsert, "{%2}", strValues)
      strFile = strFile & strTemp & vbNewLine
     
      .MoveNext
    Wend
    rst.Close
  End With
 
    If Len(strFile) > 0 Then
    Set io = fso.CreateTextFile(strName)
    io.Write strFile
    io.Close
  End If
 
End Sub

---

The first two lines are using the FileSystemObject which can be added as a reference to your project by locating the Microsoft Scripting Runtime Library.

It looks like the INSERT code is taking the name of a table from a dropdown combo list, althought the %1 and %2 thing has me a bit baffled.

The With statement is simply performing actions on a recordset object (rst)

strValues = "" is emptying whatever was contained within the variable strValues.

%1 and %2 are markers - they are replaced further down in the code.

I see no need for the filesystemobject to be used here, a sequential file write will suffice therefore removing the need for the declaration on the FSO (It'll Speed the code up)

VB Code:

Dim fso As New Scripting.FileSystemObject
Dim io As Scripting.TextStream
'other code
'
If Len(strFile) > 0 Then
    Set io = fso.CreateTextFile(strName)
    io.Write strFile
    io.Close
End If

Replaces with

VB Code:

Dim FFile As Integer
'Other code
'
If Len(strFile) > 0 Then
  FFile = FreeFile
  Open strName For Output As #FFile
  Print #FFile, strFile
  Close #FFile
End If

Many thanks to all of you for your nice replies. I be happy if u explain to me the following:

1)I wonder why we do like this :
strFields = strFields & "," & fld.Name & ""
rather then just puting fld.name equal to strFields.

2)Does strInsert hold feild name only or any other part of sql statement ? what does replace do here?

strInsert = Replace(strBase, "{%1}", strFields)


3)what does strTemp holds ?

strTemp = Replace(strInsert, "{%2}", strValues)

4)Again why strFile after equal?

strFile = strFile & strTemp & vbNewLine
5) what does Wend doing ?

6) why we check Len(strFields) = 0 and Len(strFields) > 0 ?