add recordet set into insert statement and print the statement to text file (1 Viewer)

[zentel]

Hi,
Can someone assist me with adding a recordset data to an insert sql statement in a VBA and printing that statement to a text file

I found the below code by DCrake here on access-programmers, but since i have little knowledge i am unable make the needed adjustments to achieve the desire results.
I will like the code to print line by line to the text file as follows:
INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30);
with the Values being the data from the recordset.
Please any help will be greatly appreciated. i am still learning and will really appreciate the help
Thank you


Dim fFile As Long
Dim strFile As String
Dim strString As String
Dim Rs As Recordset
Dim RsSql As String


fFile = Freefile
'Nominate the output text file path and name
strFile = "C:\FullPath\TextFileName.Txt"

'***You may want to put a check in here to test if the file
'***already exists, and as the user if they want to overwrite it first
'***Use the Kill strFile to delete it first

'Create a snapshot of data from your table with the desired fields and data
RsSql = "Select Field1, Field2, Field5, Etc From TblTableName Where Condition = '" & Criteria & "'"

'Open the recordset and test for EOF
Set Rs = CurrentDb.OpenRecordset(RsSql)

If Not Rs.EOF and Not Rs.BOF Then
Open strFile For Output As #fFile
Do Until Rs.EOF
'Loop across all fields in recordset delimiting them with a tab key
For i = 0 To Rs.Fields.Count -1
strString = StrString & Rs(i) & vbTab
Next
'Output the resulting string to the text file
Print #ffile, strString
'Reset the string to empty for the next record
strString = ""
'Move to the next record in the recordset
Rs.MoveNext
Loop
'Close the recordset and the text file
Rs.Close
Close #fFile
End If
'Destroy the instance of the recordset from memory
Set Rs = Nothing

 

[JHB]

Post a more readable code.

 

[zentel]

Please this is the code by DCrake...

Dim fFile As Long
Dim strFile As String
Dim strString As String
Dim Rs As Recordset
Dim RsSql As String


fFile = Freefile
'Nominate the output text file path and name
strFile = "C:\FullPath\TextFileName.Txt"

'***You may want to put a check in here to test if the file
'***already exists, and as the user if they want to overwrite it first
'***Use the Kill strFile to delete it first

'Create a snapshot of data from your table with the desired fields and data
RsSql = "Select Field1, Field2, Field5, Etc From TblTableName Where Condition = '" & Criteria & "'"

'Open the recordset and test for EOF
Set Rs = CurrentDb.OpenRecordset(RsSql)

If Not Rs.EOF and Not Rs.BOF Then
   Open strFile For Output As #fFile
      Do Until Rs.EOF
          'Loop across all fields in recordset delimiting them with a tab key
           For i = 0 To Rs.Fields.Count -1
               strString = StrString & Rs(i) & vbTab
           Next
           'Output the resulting string to the text file
           Print #ffile, strString
           'Reset the string to empty for the next record
           strString = ""
           'Move to the next record in the recordset
           Rs.MoveNext
      Loop
      'Close the recordset and the text file
      Rs.Close
      Close #fFile
End If
'Destroy the instance of the recordset from memory
Set Rs = Nothing

Post a more readable code.

 

[JHB]

You write:

INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30);
with the Values being the data from the recordset.

From what recordset?
Show what result you want to get in the text-file.

 

[zentel]

Thanks JHB,
Well, I have a query with the results as follows

Col1 col2 col3 col4 col5 col6 col7
EsY ty 1 7 goo 7 no
May no 4 9 try 8 yes

I want to have this results of the query insert into the
Values of the insert statement, then print the insert
Statement into a text file as shown before, line by line per the records in the query.

This way I will be able open the text file and see each insrrt sql statement that was generated.

Hope this works and again thank you for all the replies.

You write:

From what recordset?
Show what result you want to get in the text-file.