I am having a couple problems with importing information from a text file.
My first problem is getting Access to accept the type of text file it is. The file is save as FileName.rpt and Access doesn't seem to like the .rpt file extention. I have started by using DoCmd.TransferText, for example
DoCmd.TransferText acImportDelim, "Import Spec", "ToTableName", "C:\Filenamelocation.rpt", False
I can open the .rpt file in wordpad and resave the file as a .txt (file looks fine in wordpad no unrecognizable characters or anything out of place). Running the code above using a .txt file name works great, but I get a Run-Time error "3207", Cannot Update, Database or Object is Read Only when using .rtp file.
My second problem is, is this the best way to go about getting the information from the file. I am trying to automate a process that currently involves 3 excel spreadsheets, transferring data by hand, and another database and also store the information in a more usable format for the future use and review. The .rpt file is a report file generated by an instrument in our laboratory. It is not delimited in any uniform manner. The plan is to use the transfertext command (using a space delimited specification) to pull the file to a temporary table and then use code to pull specific the specific fields from specific rows from the temp table. Since the file is not delimited, using the space delimited import generates a table that has about 80 fields and 200 records. Also for this to work the information in the file must be in the exact same location everytime (I'm not sure yet if it is and this is a concern). Is there a better way to go about this?
Thank you
sTeven
My first problem is getting Access to accept the type of text file it is. The file is save as FileName.rpt and Access doesn't seem to like the .rpt file extention. I have started by using DoCmd.TransferText, for example
DoCmd.TransferText acImportDelim, "Import Spec", "ToTableName", "C:\Filenamelocation.rpt", False
I can open the .rpt file in wordpad and resave the file as a .txt (file looks fine in wordpad no unrecognizable characters or anything out of place). Running the code above using a .txt file name works great, but I get a Run-Time error "3207", Cannot Update, Database or Object is Read Only when using .rtp file.
My second problem is, is this the best way to go about getting the information from the file. I am trying to automate a process that currently involves 3 excel spreadsheets, transferring data by hand, and another database and also store the information in a more usable format for the future use and review. The .rpt file is a report file generated by an instrument in our laboratory. It is not delimited in any uniform manner. The plan is to use the transfertext command (using a space delimited specification) to pull the file to a temporary table and then use code to pull specific the specific fields from specific rows from the temp table. Since the file is not delimited, using the space delimited import generates a table that has about 80 fields and 200 records. Also for this to work the information in the file must be in the exact same location everytime (I'm not sure yet if it is and this is a concern). Is there a better way to go about this?
Thank you
sTeven
Last edited:
