Import/Export fixed length text files in MS Access 2007

[Sean Tech]

Hello Everyone,

I have two questions I hope someone can help me with. I currently have an access 2007 database with 1 table that hosts most of the main data. What I am looking for is to make a form to import data to the main table (tblMain) from a tab delimited text file. I also will need a form to export this data in a tab delimited text file based on a few variables (Company Name, and Year). How can I do this?

My next question has to do with reports. I have currently a 25+ page report in MS word that I use the data from above to create. This can be tedious since it is all in Excel. Mainly what I would like to do is import this data in my access database and produce this 25+ page report in MS word. There is information throughout this report that will need to be filled in. Along with information needing to be filled in at the end of the report I have to have tables generated. This problem I have is rather complex for me and any light that can be shed on this would be much appreciated.

I know a fair amount when it comes to access and VBA but I am still no power user.

Thank you for taking your time to look over my questions. If there is any other information that is needed please let me know.

Thank You,
Sean

[MMcCarthy]

What I am looking for is to make a form to import data to the main table (tblMain) from a tab delimited text file. I also will need a form to export this data in a tab delimited text file based on a few variables (Company Name, and Year). How can I do this?

Link the text file to the database as linked tables and create an append query to append the data to tblMain. For the exporting of the data. Manually export the data in the format you want and before finalising the export be sure to tick the "Save Export Steps" option. Then give it a name (make sure to remember the name).

Now in VBA using a command button you can add the following code to run the export any time.

Private Sub Command0_Click()

    DoCmd.TransferText acExportFixed, "Name of saved export steps", "Name of table/query to export", "Path and name of file to export to", "Export field names=True otherwise false"
    
End Sub

[MMcCarthy]

The answer to your second question is a lot more complex and will take someone some time working with you to resolve.

I would suggest creating a new thread with the second question so it can be handled separately. Hopefully it will be picked up by someone with a bit more time to give you than I have available at the moment unfortunately. Once you have created the new question send me a PM and I will edit this question as appropriate to avoid confusion.

Mary

[Sean Tech]

Thank You

Thank You for the information and advice. I will make a new post for the other question.