Question

Locked

VBA code to extract Access table to text file and vice versa

By mailtomesaroj ·
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.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

College Work?

by Bizzo In reply to VBA code to extract Acces ...

It seems you have some coursework you're stuck on. Have you tried running the sample code you've downloaded? Or tried to convert it to VBA for use in Access? Maybe you could try it in Access and see what happens.

I don't mean to be rude, but you might actually learn something. At least try it first and let us know the problems you get.

Back to Hardware Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums