I have created a table in access and for backup; I have to extract it in text file. V can do this by direct export command available or creating specification but I need something different.
I created table named ? table1? with data fields ?Id?, ?Name ?, ?Age ?, ? Occupation ?. Then I created a form named ? form1? and introduced a button named ? save ?. Now I will enter data values to table and view it in form. As I press save button, it should create a text file in C: or drive separating data fields with ? | ? symbol. [ Sample text output should be as follows:
| Id | Name | Age | Occupation |
01 | Ram | 22 | student | . . . .
] Note: there is shortcut method export. Pls do not prefer that method. Pls follow the following method: - go to properties of button created in form - go to on singleClick property - go to code mode( or select any of the three options available according to ur requirement) - create module
The following is code that I downloaded from internet. Pls refer to this code also if it can help u:
Thanking you,
Saroj Aryal
This code works in VB, you should be able to adapt it to Access VBA Notes: Exportpath is a variable that holds the drive, folder and file name. (C:\ExportFolder\ExportFile.txt) GetADORSP is a funtion that passes back the recordset, you just need to great a recordset how ever your program is doing it. Expand|Select|Wrap|Line Numbers
1. Private Sub cmdExport_Click() 2. On Error GoTo ErrHandler 3. Dim rs As ADODB.Recordset 4. Dim strSql As String 5. Const SW_SHOWNORMAL = 1 6. 7. 'Clear the text file 8. Open ExportPath For Output As #1 9. Print #1, "" 10. Close #1 11. If lstPersonnel.SelCount = 0 Then 'Get all records 12. iRow = 1 13. For i = 0 To lstPersonnel.ListCount - 1 14. 15. strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _ 16. "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _ 17. & lstPersonnel.ItemData(i) 18. 'MsgBox strSql 19. Set rs = GetADORSP(strSql) 20. Open ExportPath For Append As #1 21. Do While Not rs.eof 22. Print #1, rs.GetString(, , "|", vbCrLf, ""); 23. 24. Loop 25. Close #1 26. Set rs = Nothing 27. Next i 28. Else 'Get only the selected personnel data 29. 30. iRow = 1 31. For i = 0 To lstPersonnel.ListCount - 1 32. 33. If lstPersonnel.Selected(i) = True Then 34. 35. strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _ 36. "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _ 37. & lstPersonnel.ItemData(i) 38. Set rs = GetADORSP(strSql) 39. Open ExportPath For Append As #1 40. 41. Do While Not rs.eof 42. Print #1, rs.GetString(, , "|", vbCrLf, ""); 43. 44. Loop 45. Close #1 46. End If 47. Set rs = Nothing 48. 49. Next i 50. 51. 52. End If 53. Set rs = Nothing 54. 'Check to see if they want to review the text file before sending it out. 55. If MsgBox("Would you like to review the data file to be sent?", vbYesNo, "Attention") = vbYes Then 56. Call ShellExecute(Me.hWnd, "open", ExportPath, "", 0, SW_SHOWNORMAL) 57. End If 58. Exit Sub 59. ErrHandler: 60. MsgBox "Error exporting data. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error" 61. Set rs = Nothing 62. Close #1 63. End Sub
This conversation is currently closed to new comments.
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
How to extract Access table to text file and vice versa
I created table named ? table1? with data fields ?Id?, ?Name ?, ?Age ?, ? Occupation ?. Then I created a form named ? form1? and introduced a button named ? save ?.
Now I will enter data values to table and view it in form. As I press save button, it should create a text file in C: or
[
Sample text output should be as follows:
| Id | Name | Age | Occupation |
01 | Ram | 22 | student |
.
.
.
.
]
Note: there is shortcut method export. Pls do not prefer that method.
Pls follow the following method:
- go to properties of button created in form
- go to on singleClick property
- go to code mode( or select any of the three options available according to ur requirement)
- create module
The following is code that I downloaded from internet. Pls refer to this code also if it can help u:
Thanking you,
Saroj Aryal
This code works in VB, you should be able to adapt it to Access VBA
Notes: Exportpath is a variable that holds the drive, folder and file name.
(C:\ExportFolder\ExportFile.txt)
GetADORSP is a funtion that passes back the recordset, you just need to great a recordset how ever your program is doing it.
Expand|Select|Wrap|Line Numbers
1. Private Sub cmdExport_Click()
2. On Error GoTo ErrHandler
3. Dim rs As ADODB.Recordset
4. Dim strSql As String
5. Const SW_SHOWNORMAL = 1
6.
7. 'Clear the text file
8. Open ExportPath For Output As #1
9. Print #1, ""
10. Close #1
11. If lstPersonnel.SelCount = 0 Then 'Get all records
12. iRow = 1
13. For i = 0 To lstPersonnel.ListCount - 1
14.
15. strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _
16. "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _
17. & lstPersonnel.ItemData(i)
18. 'MsgBox strSql
19. Set rs = GetADORSP(strSql)
20. Open ExportPath For Append As #1
21. Do While Not rs.eof
22. Print #1, rs.GetString(, , "|", vbCrLf, "");
23.
24. Loop
25. Close #1
26. Set rs = Nothing
27. Next i
28. Else 'Get only the selected personnel data
29.
30. iRow = 1
31. For i = 0 To lstPersonnel.ListCount - 1
32.
33. If lstPersonnel.Selected(i) = True Then
34.
35. strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _
36. "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _
37. & lstPersonnel.ItemData(i)
38. Set rs = GetADORSP(strSql)
39. Open ExportPath For Append As #1
40.
41. Do While Not rs.eof
42. Print #1, rs.GetString(, , "|", vbCrLf, "");
43.
44. Loop
45. Close #1
46. End If
47. Set rs = Nothing
48.
49. Next i
50.
51.
52. End If
53. Set rs = Nothing
54. 'Check to see if they want to review the text file before sending it out.
55. If MsgBox("Would you like to review the data file to be sent?", vbYesNo, "Attention") = vbYes Then
56. Call ShellExecute(Me.hWnd, "open", ExportPath, "", 0, SW_SHOWNORMAL)
57. End If
58. Exit Sub
59. ErrHandler:
60. MsgBox "Error exporting data. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
61. Set rs = Nothing
62. Close #1
63. End Sub