Solved

Copy AccessDatabase to a flat file

Posted on 2000-05-08
5
169 Views
Last Modified: 2010-05-02
I have an AccessDatabase. I need to grab some info from a few fields in an Access table and copy those info to a text file. How should I approach this.
0
Comment
Question by:gabester
5 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2788774
Open a recordset, loop it writting data to your ASCII file.

Dim intNumFile As Integer

intNumFile = FreeFile
Open "C:\test.txt" For Output As #intNumFile
Do until rst.eof
   Print #intNumFile, rst!Field1 & ", " & rst!Field2
   rst.movenext
loop
Close #intNumFile
rst.close
Set rst = Nothing
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 100 total points
ID: 2788809
Set a reference to "Microsoft ActiveX Data Objects".. assuming you are using Access 97, you can use the following:

<----- Code Begin ----->

Dim dbGet As New ADODB.Connection
dbGet.Provider = "Microsoft.Jet.OLEDB.3.51"
dbGet.ConnectionString = "c:\MyPath\MyFile.mdb"
dbGet.Open

Dim rsGet As New ADODB.Recordset
rsGet.ActiveConnection = dbGet.ConnectionString
rsGet.Source _
   = "SELECT MyField1, MyField2 " _
   & "FROM MyTable "
rsGet.Open

Open "C:\MyPath\MyFile.Text" For OUTPUT As #1
   
With rsGet
   Do Until .EOF
      Write #1, !MyField1, !MyField2
     .MoveNext
   Loop
   .Close
End With

Close #1
     
Set rsGet = Nothing
Set dbGet = Nothing

<----- Code End ----->
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2788826
Emoreau's example will give you a pure Text File.. WSH2's will give you a CSV file suitable for Importing. (CSV means each field will be seperated by a column and the strings will be enclosed in double quotes)
0
 
LVL 2

Expert Comment

by:Simonac
ID: 2788837
U can also use the following function:
appAccess.DoCmd.TransferText

Where appaccess is an access.application
Its the equivalent of using (in access) Export as text file
0
 

Author Comment

by:gabester
ID: 2789247
WSH2 thanks. Your code worked really well.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now