HELP! how to send data from table to text file

[daveofgv]

I am using

Option Compare Database
 
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Table1")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set WriteFile = fs.CreateTextFile("C:\test\test.text", True)
Do Until rst.EOF = True
WriteFile.writeline "Image Name"
WriteFile.writeline "Feild2" & Chr(9) & "Feild3" & Chr(9) & "Feild4" & Chr(9) & "Feild5"
WriteFile.writeline Chr(12)
rst.MoveNext
Loop
WriteFile.Close
End Sub

and everything works, however, I can not figure why the data in feild1, feild2, ect... is not writing over to the text file. Only the column names feild1, feild2 etc... are.

does anyone know how to send the actual data instead of the column names in the above code to a text file?

Thanks in advanced

daveofgv

[ajetrumpet]

it's not writing over because you're not telling it to. all you have is this:

WriteFile.writeline "Image Name"
WriteFile.writeline "Feild2" & Chr(9) & "Feild3" & Chr(9) & "Feild4" & Chr(9) & "Feild5"
WriteFile.writeline Chr(12)

there are no field references anywhere. you're writing a text file with names of columns for every record in the table.

why are you doing it this way anyway? what's wrong with docmd.transfertext?

[daveofgv]

To be honest, cause I don't know......

I have used Access, but not on the programming side of it.

If I don't use a WriteFile how would I use docmd.transfertext?

Also, can it have tab delimited?

Thanks

[daveofgv]

I got the table to transfer to a text file, however, I need it in a certain format.........

What I have now is

 DoCmd.TransferText acExportDelim, , "Table1", "C:\test\test.txt", True

and it puts the table like this:

"ID","Image Name","Field2","Field3","Field4","Field5"
1,"helloworld","Steve","Forbes","Age","height"
2,"goodbye","George","Bush","local","sexy"

and what I need is it in a tab delimited format as below, where tabs are in between feild2 feild3 etc...... (which by the way - I do know that feild is not spelt correctly - part of the way our work database was first created)

Image Name
Feild2 Feild3 Feild4 Feild5

Image Name
Feild2 Feild3 Feild4 Feild5

Any suggestions?

Thanks

daveofgv

[ajetrumpet]

dave,

"spelt" is not the way to say that in the context you put it, either. (sorry, had to! just kidding)

anyway, on to more important things....there is no specification in the actual method you used in code. you can do two things to get a tab delim'd file:

1) manually export the table through the interface's wizard.
2) create a spec in that wizard, save it with a name, and specify that name in the arg of the method in vba code.

that's all I would know how to do. If you don't have to do it via code, then I wouldn't, unless you want to bite the bullet and create the spec first. at that point at least, it's at your disposal whenever you want it.

[daveofgv]

thanks for the english..... lol..... I didn't attend too much skool.......lol

Right now, I am using Access 2010 and have been googleing how to create specs....

When the Access export feature is used, apparently, there is not place to save a spec. It pretty much just says "ok, you want to export then there you go" type deal

I appreaciate your help and if I find a way i will post it for future use for others....

[ajetrumpet]

there IS a way to save a spec, Dave. But it's hard to find. I remember testing it one time, but it's not traditional.

As a workaround, do this:

• run the export you want EXACTLY, through the interface wizard
• save it as a named export
• run this code then, after that, at any time via vba:

docmd.RunSavedImportExport "name"

that'll be all you need, bud.

[daveofgv]

I appreciate all your help. I finally found how to save the export spec, however, it still dosn't do what I want (even though I saved it as a Tab delimited)

I will have to try to find another work around to make the file look like we need.

daveofgv

[thhui]

You can somehow change the format by using schema.ini when doing the export wizard of Access.

[daveofgv]

How do I use schema.ini?

[ajetrumpet]

How do I use schema.ini?

with your eyes closed, unless you're a professional developer.

[thhui]

There are quite few websites talking about schema.ini now.

Microsoft just delete that help in Access Online Help!!??
But I still find some useful here
http://office.microsoft.com/en-us/ac...001032166.aspx

Just quote some websites that have a few examples to follow.

http://www.aspdotnetcodes.com/Import...chema.ini.aspx

http://forums.devx.com/showthread.php?t=51543