Originally Posted by
AccessMSSQL
Use DAO to get the values from your country table and use file system object to write to a text file: Here is some code:
Sub textfile()
Dim fs As Object
Dim file As Object
Dim sText As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set file = fs.CreateTextFile("C:\text.txt", True) 'NOTE - you may need to choose a file location with proper permissions if you are on Windows 7 or Vista.
sText = "ADD VALUE LABELS Country1" + vbCrLf
sText = sText & GetCountryList()
file.writeLine sText
file.writeLine
sText = "ADD VALUE LABELS Country2" + vbCrLf
sText = sText & GetCountryList()
file.writeLine sText
file.writeLine
sText = "ADD VALUE LABELS Country3" + vbCrLf
sText = sText & GetCountryList()
file.writeLine sText
file.writeLine
file.Close
End Sub
Private Function GetCountryList() As String
Dim rs As DAO.Recordset
Dim sValues As String
sValues = ""
Set rs = CurrentDb.OpenRecordset("select * from Country")
If Not rs.EOF Then
Do Until rs.EOF
sValues = sValues & rs("CountryID") & " "
sValues = sValues & rs("Country") & vbCrLf
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
GetCountryList = sValues
End Function