convert data in ms access to text file

Select the table and use File -> SaveAs/Export and follow the wizzard

Dave

Select the table and use File -> SaveAs/Export -> To an external db or file -> and follow the wizzard

Hi berq,

If you want to automate this process, then make a Report of what you want, then use

for rtf
DoCmd.OutputTo acOutputReport, "ReportName", acFormatRTF, "c:\FileName.rtf", False
OR for txt
DoCmd.OutputTo acOutputReport, "ReportName", acFormatTXT, "c:\FileName.txt", False

Hope this helps

Jaffer

berq,

like flavo said, use the wizard,
Use the wizard to create your Report.

References to ADO 2.1 or above and Microsoft's Scripting Runtime are required.

Copy the declarations and code below and paste directly into your VB project.And Give the Path of your database file and text file.

Option Explicit
Private Const mksConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\sample1\Test\expdb.mdb"


Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sSql As String
Dim strA As String
Dim fso As New Scripting.FileSystemObject
Dim txtFile As Scripting.TextStream

With cn
    .ConnectionString = mksConnectionString
    .Open
End With

sSql = "SELECT * from table1"

'Fires the query and then writes the output to a flat text file
'pipe delimeted
rst.Open sSql, cn

Set txtFile = fso.CreateTextFile("C:\Test.txt", True)

txtFile.Write (rst.GetString(adClipString, , "|", _
vbCrLf, ""))
   
End Sub

Above two examples Are through Vb Code....

I am Giving you link which provide information

http://support.microsoft.com/default.aspx?scid=kb;en-us;111244

Regards
Sparab

Berq: You mention you need to convert your Access DB data to text files to upload to another system. I presume your Access DB is a collection of tables, queries, forms, reports, macros, and modules. The forms and reports do not convert per se. Queries in their SQL form are nothing but text strings and can be copied over to other systems however, because of differences in SQL format between DB products, one type will seldom run in another DB product. Modules are essential VB text files and again because of differences in how VB is used in other types of DB they will seldom if ever run without modification. The posts above describe the method for converting a single table to a text file. This will have to be done for each table in your Access DB. What is your "other system".

ok thank guys.
ok now i'll tell my real situation.
so i have a ms database access. so of course it contain many table. so the data that i need to convert to textfile is coming from different table. So basicly i have to create query to gather up all the data. i also know that i can convert the data to textfile by using macro. but my real problem is in order to upload the data to the other system, the textfile have to be in the certain format that can be read by the other system. ok i give u example. so let say i have three table.
1. complaint table which contain field
     a. comlaint id
     b. complain title
     c. assetid ( lick with asset table )
     d. userid ( link to user table )
2. asset table
     a. asset id
     b. description
     c. type
     d. supplier
3. user id
     a. userid
     b. name
     c. department

so let say i need complain title, asset desription, type, name of user and department of user to be upload to the other system. so the other system will read like first 40 space is for complaint title, the next hundred space is for asset description, next 15 space is type, next 40 space is for name and the last 20 space is for department.
so how do i do take arrange the textfile so it can follow the format?

thank you in advance

berq:

Form:
On the Form where you will call your query, have a command button, call it command 1, paste the following code:

Private Sub Command0_Click()
Open "C:/Test.txt" For Output As #1
DoCmd.OpenQuery "QueryName"
Close #1

Query:
In your query,
in an empty field write:
AllFields: OutPutToText ([complain title],[asset desription],[type],[name of user],[department of user])

Module:
Make a module and save it as TextFile,
Paste the following in it:

Function OutPutToText(complaintitle,assetdesription,type1,nameofuser,departmentofuser)
Print #1, complaintitle; Tab(41); assetdesription; Tab(142); type1; Tab(158); nameofuser; Tab(199); departmentofuser '& chr(13)  <-- this will add a carrage return for the next line, you might not need it
End Function

'---------------------------
What is happening:
1-
In the Form, first we open test.txt fiel for output,
Then we are running a quey which have all the variables,
Then closing the open file.

2-
in the Query, we are sending the variables Record by Record to a Function in a module,

3-
In the module, the Function Prints 1 line at a time,
TAB means to print on that column number in the text file,
If after running the code, you get 1 infinite line, then add '& ch(13)' (without quotes) ar the end of the print line, this will add a carrage return at the end of the line.

jaffer

Dear berq

I think you should export data into text delimated format which is standard practice.

In both way it is convenient if you are using delimated format.Format which you want to use are may cause you problems in future.

Regards
Sparab