Option Compare Database
Option Explicit
Public Function Export_Tab_Delimited(TableOrQueryName As String, FileNameAndPath As String)
'This uses the DAO database reference. IN the VB window (Ctrl+G) go to _
Tools --> References and select the Microsoft DAO 3.6 Library
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim I As Integer
Dim FileNum As Integer
'Dim FileNameAndPath As String
Dim OutputLine As String
FileNum = FreeFile()
'FileNameAndPath = "C:\MyDirectory\MyFile.txt"
Set DB = CurrentDb() 'Use the current database
Set RS = DB.OpenRecordset(TableOrQueryName ) 'actually open the recordset
If RS.EOF = False Then
RS.MoveFirst
Else
MsgBox "No Data", vbExclamation, "Exiting Fuction"
Set RS = Nothing
Set DB = Nothing
Exit Function
End If
'Open the file for output
Open FileNameAndPath For Output Access Write Lock Write As FileNum
I = 0
OutputLine = ""
'Output the field names as a header
For I = 0 To RS.Fields.count - 1
If I > 0 Then
OutputLine = OutputLine & Chr(9) & RS.Fields(I).NAME
Else
OutputLine = RS.Fields(I).NAME
End If
Next I
Print #FileNum, OutputLine
'Debug.Print OutputLine
I = 0
OutputLine = ""
'start outputting the data
Do Until RS.EOF
For I = 0 To RS.Fields.count - 1
If I > 0 Then
OutputLine = OutputLine & Chr(9) & RS.Fields(I).Value
Else
OutputLine = RS.Fields(I).Value
End If
Next I
Print #FileNum, OutputLine
Debug.Print OutputLine
OutputLine = ""
RS.MoveNext
Loop
Close #FileNum
Set RS = Nothing
Set DB = Nothing
End Function
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Author
Commented:'FileNameAndPath = "C:\MyDirectory\MyFile.txt
That is rem code for testing. I assume a valid from the end-user. I could probably build the FSO system, but that is not relevant for the code I'm posting.
Commented:
1) When I download the db and run the form, I get an error "MS database engine could not find the input table or query 'MyQueryName'. Make sure that it exists and that it's name is spelled correctly." So I can't get it to work in it's initial db. How do I fix this?
2) If I want use this in another db, can I just export the Form and the function to the other database? If not, what is needed to do that? This way I don't have to code anything (I hope).
Thanks,
Ed