Read a text file automatically 1

[CHTHOMAS]

Hi,<br>
Could any one tell or suggest how to read a text file in Access. I want Access to open the text file and read it automatically into <br>
a table. Any help will be appreciated.<br>
Regards,<br>
Charley

 

[DougP]

Well there is an Import routine...<br>
Click on the Tables TAB<br>
Right click in the white space<br>
Click &quot;Import&quot;<br>
Click &quot;Files of Type&quot; (at the bottom) and choose &quot;Text Files&quot;<br>
Click the &quot;Import&quot; Button<br>
<br>
Now you have 2 choices here...<br>
Access will try to determine what type of text file it is<br>
Delimited or Fixed width. It usualyy gets it right go with it's default choice.<br>
Click the &quot;Advanced&quot; button. This is whare you can make changes to the size of the fileds and give them names etc.<br>
Save your specification for use later by clicking the &quot;Save As&quot; button<br>
The &quot;Specs&quot; button is where you can retrieve your specification next time.<br>
Also If you create a specification it can be used in VBA code later to totally automate the whole process.<br>
That is done by using <br>
<br>
DoCmd.TransferText acImportDelim, &quot;SpecificationName&quot;, NewTable, &quot;Filename&quot;, HasFieldnames<br>
<br>
OK<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[CHTHOMAS]

Doug,<br>
<br>
I created a specification for importing the text file and used a macro to run the specification. I have 2 problems.<br>
<br>
1) When i run the macro, i am getting the error message &quot;Cannot Open the Specified file, Opened exclusively by another user or you need permission to view the data&quot;. I am sure i am the only person who has the db opened. (ie. i open the db, run the macro which tries to import the text file into the table)<br>
<br>
2) If there any way to view the specification once created?<br>
<br>
Regards,<br>
Charley

 

[Lehtoant]

Question 1:<br>
<br>
Are you sure that no one has that text file open? Usually when I get this error I'm the one to blame.<br>
<br>
For your question 2:<br>
<br>
You can view and modify existing specs as Doug described: File/Get External Data/Import, selct file and start wizard &quot;Advanced&quot; button, &quot;Specs&quot; button.<br>
<br>
Other way just to check the specs:<br>
Tools/Options/View/System Objects<br>
<br>
And look at tables MSysIMEXSpecs (=specifications you have made) and MSysIMEXColumns (=details of each specifications). These views are practical for documentation.<br>
<br>
Good luck,<br>
Al