Using a CSV/Text-File as a RecordSource for a Report

I need开发者_开发技巧 an Access Report to use a CSV-File as a RecordSource.

I have searched and tried many things, yet, I've found no way to achieve this. A temporary table in some other database is not an option.

I've been trying to use a DAO RecordSet, but while I can read the RecordSet just fine I cannot set it as the report's RecordSet and I cannot use its Name as the RecordSource Property as both approaches lead to an error.

Please help me find a way!

I the Recordsource of your report, use the IN operator in the FROM clause to specify a connect string:

  SELECT [TestMailing#txt].*
  FROM [TestMailing#txt] IN 'C:\Documents and Settings\My Documents'[Text;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=1252];

Basically, the IN for text files has two parts, the first is the path where the file is, and the second is the connect string options inside brackets. I found out what the paramters were there by creating a link to the text file and examining its connect string.

Last of all, you refer to the text file as a table, using its filename with the # replacing the . for the extension.

When you use the Access query builder, you can put these options in the property sheet for the query, where the path is the Source Database property and the connect string options are the Source Connect Str property. Once you've put these in the property sheet, you can click the ADD TABLES toolbar icon to get a list of the files for the particular connect string in the designated folder.

If your report is going to use the same structure file, you can probably enhance reliability by creating a saved import spec, which can also be indicated in the connect string options. You can find this by creating the linked table to the text file and then examining its connect string.

However, if that's the case, there's precious little reason why you shouldn't just create the linked to the text file and leave it there permanently!

The only way to do this, AFAIK, is to link the csv file. You can link at runtime and unlink after the report is completed, if keeping it linked is a problem. The TransferText methods of the DoCmd object will allow you to link in VBA.

Further information: http://msdn.microsoft.com/en-us/library/aa220768(v=office.11).aspx