could any one explain this code for me ?

[tony007]

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

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

[Hack]

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.

[Ecniv]

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

[dannymking]

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)

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

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

[tony007]

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 ?