<div>
is your code in Access, or VB?<br />
<br />
If in Access, you should attempt to read the data in to an Access table via the External Date option on the ribbon. &nbsp;<br />
<br />
Then, in the import wizard indicate that you want to use fixed width fields, drag the lines to define the fields or click the Advanced button (bottom left corner of the wizard) and enter the actual field widths. &nbsp;While viewing the advanced options, save your import specification. &nbsp;<br />
<br />
You can then use the docmd.TransferText method, and provide the import specification name you created to that method.
</div>


<div>
* your file isn't delimited, it contains fixed length fields.<br />
* you may need to create a schema.ini file<br />
* it looks like there might be some inter-record spacing issues. &nbsp;I see a blank line between the two non-blank lines.
</div>


<div>
Hi,<br />
@Dale,<br />
I have an Access ADP that is connected to SQL 2005 BE.<br />
Do you suggest I change the approach of reading the file to the above?
</div>


<div>
Just looking at your example, it appears to be a fixed width file rather than a CSV file. &nbsp;If it is fixed width, you can import it once manually as a fixed width file and save the import spec. &nbsp;Then you can automate it by using the TransferText method and referencing the import spec. &nbsp;Or it may be tab delimited. &nbsp;You can try importing it manually as tab delimited and see if that works. &nbsp;At some point in the import, click the Advanced button. &nbsp;You will be able to specify the delimiter and also specify a date format so the next to last column would import as a date and you won't have to fix it later.<br />
<br />
A properly formatted CSV file would look like this:<br />

<pre><code class="code-1 nolinks" id="code-20-40524480-1"><span>&quot;111-22-3333&quot;,&quot;W688&quot;,&quot;FIRST LAST&quot;,&quot;141213&quot;,30.00	</span>
<span>&quot;444-55-5555&quot;,&quot;W690&quot;,&quot;FNAME LNAME,PC&quot;,&quot;141206&quot;,44.50</span>
</code></pre>
A tab delimited file, which this may be, would contain the quotes but substitute tabs for the commas.
</div>


<div>
@aikimark,<br />
Everything works besides those records with commas.
</div>


<div>
@Daly, Pat,<br />
While trying to do the manually import process, I don't get the option to save it, its grayed out, see attached.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w01/890222/untitled.bmp" target="_blank" class="file-inline" title="import (1.2 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>untitled.bmp</a>
</div>


untitled.bmp

<div>
That's odd. &nbsp;Compile the database and fix any compile errors. &nbsp;Then compact and repair and try again. &nbsp;If it still fails, try with a new, empty database. &nbsp;If that fails, you will need to repair your Office installation.<br />
<br />
PS, you also need to check the leading zeros in dates box.
</div>


<div>
Hi Experts,<br />
<br />
It happen to be that I don't need the column containing the commas, the problem is that this comes from an external program that I don't have access to.<br />
<br />
Is it possible to open the file and read thru a loop each character and when it finds a comma replace it with empty string, then I close the file and run my original code?
</div>


<div>
It probably doesn't like the field name you gave the SSN field, although I've set mine up just like yours and my &quot;SaveAs&quot; button is enabled.<br />
<br />
BTW, I think you also need to change the name of your [YYMMDD] field to something like &quot;HireDate&quot; or something along those lines as well and probably want to change the data type of that field from long integer to text.
</div>


<div>
That is not going to be easier than getting the import spec to work but if you are comfortable writing code, have at it.<br />
<br />
The bad name that Dale mentioned might actually be keeping the spec from being saved or it could be the name.<br />
<br />
Also, if you want to import yymmdd as a real date, you need to get ALL the moving parts right. &nbsp;So, in addition to the other settings, you need to change the data type from long integer to date.
</div>


<div>
If you don't need the EmployeeName column, then in the import specification, check the &quot;Skip&quot; checkbox so that it doesn't import that column. &nbsp;<br />
<br />
Additionally, you might want to consider simply linking to the document rather than importing it, then you can write a query to pull in the non-NULL records and only import the fields you actually need into your production table.
</div>


<div>
The stray comma will still cause a problem with linking unless you use an import spec to ignore the column.
</div>


<div>
@Pat, Dale,<br />
<br />
Tried those suggestions (Compact, Change column names etc..) so far nothing helped, I might try soon with a another pc using &nbsp;different office version (mine is 2000 and that is 2003).<br />
<br />
However just wondering if this approach would work later on in all users pc's or I will need to repeat this import on each users pc?
</div>


<div>
@Dale,<br />
<br />
This is an Access project, not sure what you mean link to external file?<br />
<br />
Re the no need of the column, I meant to ask if by removing the comma is possible to avoid the problem I'm having with current code.
</div>


<div>
Ah, I missed the response which indicated that it is an ADP. &nbsp;I've never written an ADP, but that may well be an issue. &nbsp;If it were me, I would create a staging table in SQL Server to import the raw data into, and would then pull the fields you need from that staging table into your production table.
</div>


<div>
A2000 is very old. &nbsp;I don't even remember it. &nbsp;I think in earlier versions, you may have had to install certain features separately and this may be one of them. &nbsp;I don't recall there being any problem related to the import spec in an Access project but who knows. &nbsp;The file format for A97 was deprecated for A2013 so I don't imagine that A2K is far behind. &nbsp;Access projects have also been deprecated so it might be time to think about moving to a new version of Access. &nbsp;Skip A2007 but A2010 is solid. &nbsp;A2013 has nothing new for client/server but it deprecates a lot of important stuff so that may be a bridge too far. &nbsp;In addition to the two I mentioned, you also loose the ability to read .dbf files and you loose some ActiveX objects such as TreeView. &nbsp;Of course, we lost ULS with A2007, although most people found that unusable and that's why it bit the dust.
</div>


<div>
@Dale,<br />
Not sure what does it mean &quot;create a staging table in SQL Server to import the raw data into&quot;, are you referring to kind of a memo field that would hold the full contents of the file, including all special characters like spaces tabs etc?<br />
<br />
@Pat,<br />
&nbsp;thanks for those useful info, will keep in mind when holding by upgrading (it doesn't really depend on me, it needs approval from the higher ups..)<br />
<br />
@All,<br />
Tried with other version and doesn't let me save either, as Dale mentioned this looks like an ADP problem.<br />
I might try to save it in a MDB and then use it in ADP, but first would need to know if that would work for all users.<br />
What about &nbsp;reading the file character by character and replacing the commas, does someone have an example of this?
</div>


<div>
@bfuchs,<br />
<br />
No, a &quot;staging table&quot; generally has the same number of fields as are in the file to be imported. &nbsp;I normally make mine so that all of the fields in the staging table are text fields of the appropriate length, which accept NULL values. &nbsp;This allows you to import your data into this table first. &nbsp;You could then delete all of the records that are NULL (the file you posted may have had an extra CRLF and loaded a NULL record between the two good records). &nbsp;You can then perform a series of tests to confirm that the values in each of the fields is formatted properly (no decimal points for integer fields, &quot;-&quot; where appropriate for the SSN fields, etc...). &nbsp;Then you can run a query to upload the records that have all of the required fields and which meet the formatting criteria into your production table.<br />
<br />
I do this pretty consistently for all of the Excel, CSV, delimited, or fixed width text files I load into Access or SQL Server. &nbsp;There is nothing worse than expecting data in a specific format and having someone dump inappropriate data into a field and foul up an upload process.
</div>


<div>
@Dale,<br />
OK understood, however here the problem is that I cant get to read those records at the first place, as you can see from code originally posted, it tries reading line by line and when it comes to a record containing a comma it gets truncated. below is the full code I am using so you get a clear understanding of whats going on here.<br />

<pre><code class="code-10 nolinks" id="code-20-40524908-1"><span>Private Sub ImportEmpTovWeeklyHours()</span>
<span>   Dim conn As New ADODB.Connection</span>
<span>   Dim rs As New ADODB.Recordset</span>
<span>   Dim f As ADODB.Field</span>
<span>   Dim v As Variant</span>
<span>   Dim strSql As String</span>
<span>   conn.Open &quot;DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=\\tovserver\E\XFER;&quot;</span>
<span>   rs.Open &quot;SELECT * FROM [empwehr.txt]&quot;, conn, adOpenStatic, adLockReadOnly, adCmdText</span>
<span>    </span>
<span>   rs.MoveFirst</span>
<span>  'CurrentProject.Connection.Execute (&quot;delete  from dbo.FacilitiesTovLastInvoiceDate&quot;)</span>
<span> </span>
<span>On Error Resume Next</span>
<span>   While Not rs.EOF</span>
<span>        If Not IsNull(rs.Fields(0)) Then</span>
<span>          v = Split(rs.Fields(0), Chr(9))</span>
<span>          </span>
<span>        If UBound(v) &gt; 1 Then</span>
<span>            strSql = &quot;Insert into EmpTovWeeklyHours&quot; _</span>
<span>            &amp; &quot;(SocialSecurity,Day,Hours)&quot; _</span>
<span>            &amp; &quot; Values ('&quot; &amp; Replace(v(0), Chr(32), &quot;&quot;) &amp; &quot;','&quot; &amp; Mid(v(3), 3, 2) &amp; &quot;/&quot; &amp; Mid(v(3), 5, 2) &amp; &quot;/&quot; &amp; Mid(v(3), 1, 2) &amp; &quot;',&quot; &amp; Replace(Replace(v(4), Chr(32), &quot;&quot;), &quot;'&quot;, &quot;&quot;) &amp; &quot;)&quot;</span>
<span>            </span>
<span>            CurrentProject.Connection.Execute strSql</span>
<span>        End If</span>
<span>          </span>
<span>          'Debug.Print v(2)</span>
<span>        End If</span>
<span>          </span>
<span>      rs.MoveNext</span>
<span>   Wend</span>
<span>   rs.Close</span>
<span>End Sub</span>
</code></pre>
just to clarify, the actual file being imported contains thousands of records, they all work fine except for those with commas
</div>


<div>
I tried your driver and both the Jet and ACE providers to no avail. &nbsp;Looks like you're going to need to create a Schema.ini file (see attached example)<br />
<br />
Reading your sample file, produces the following data in the recordset:<br />

<pre><code class="code-10 nolinks" id="code-20-40524945-1"><span>F1            111-22-3333</span>
<span>F2            W688</span>
<span>F3            FIRST LAST</span>
<span>F4             141213 </span>
<span>F5             30 </span>
<span>F6            Null</span>
<span></span>
<span>F1            Null</span>
<span>F2            Null</span>
<span>F3            Null</span>
<span>F4            Null</span>
<span>F5            Null</span>
<span>F6            Null</span>
<span></span>
<span>F1            444-55-5555</span>
<span>F2            W690</span>
<span>F3            FNAME LNAME,PC</span>
<span>F4             141206 </span>
<span>F5             44.5 </span>
<span>F6            Null</span>
</code></pre>
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w01/890260/schema.ini" target="_blank" class="file-inline" title="schema.ini (56 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>schema.ini</a>
</div>


schema.ini

<div>
@aikimark,<br />
I cant open the attached, it gives me an error
</div>


<div>
You don't read it. &nbsp;You put it in the same folder as the <b>myfile.txt</b> file.
</div>


<div>
ok, sorry for asking this just never dealt with schema before.<br />
what do I do next?<br />
just run my original code or this works with the import procedure that Dale and Pat are suggesting?
</div>


<div>
yes. &nbsp;If that doesn't work, I'll post one or both provider strings that I tested tomorrow morning.
</div>


<div>
Did you try using DoCmd.TransferText to import the file? &nbsp;You also never said if the file is delimited or fixed.
</div>


<div>
@aikimark,@Pat,<br />
See attached.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w01/890374/Aki.bmp" target="_blank" class="file-inline" title="Aikimark (3.8 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Aki.bmp</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w01/890375/Pat.bmp" target="_blank" class="file-inline" title="Pat (3.8 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Pat.bmp</a>
</div>


Aki.bmp

Pat.bmp

<div>
@Pat,<br />
The file is delimited.
</div>


<div>
Did you put the schema file in the same folder as the tab delimited file, like I told you to do?<br />
<br />
schema.ini is not a substitute for an Access-resident specification. &nbsp;You can't use the schema.ini file with the TransferText method
</div>


<div>
@aikimark,<br />
<br />
Yes I did.<br />
<br />
Not sure what was your answer on my question, if that schema.ini was meant to work with my original code or with import methods, therefore I tried both..however neither worked.<br />
<br />
let me know what else I am missing here.<br />
Thanks
</div>


<div>
@bfuchs,<br />
You didn't answer the question about TransferText. &nbsp;What happens when you try to import as a delimited file using that method?
</div>


<div>
I get the error attached above.
</div>


<div>
This is the code I tested that produced the results I posted:<br />

<pre><code class="code-10 nolinks" id="code-20-40526186-1"><span>Sub testit()</span>
<span>    Dim conn As Object</span>
<span>    Dim rs As Object</span>
<span>    Const adOpenStatic = 3</span>
<span>    Const adLockReadOnly = 1</span>
<span>    Const adCmdText = 1</span>
<span>    Set conn = CreateObject(&quot;adodb.connection&quot;)</span>
<span>    Set rs = CreateObject(&quot;adodb.recordset&quot;)</span>
<span>                                                             'DBQ=\\myserver\E\XFER;</span>
<span>    conn.Open &quot;DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\users\aikimark\downloads;&quot;</span>
<span>'    conn.Open &quot;Provider=Microsoft.ACE.OLEDB.12.0;&quot; &amp; _</span>
<span>'           &quot;Data Source=c:\users\mark\downloads;&quot; &amp; _</span>
<span>'           &quot;Extended Properties=&quot;&quot;text;HDR=No;FMT=TabDelimited&quot;&quot;;&quot;</span>
<span>           </span>
<span>    rs.Open &quot;SELECT * FROM [myfile.txt]&quot;, conn, adOpenStatic, adLockReadOnly, adCmdText</span>
<span>    do until rs.eof</span>
<span>        for each fld in rs.Fields</span>
<span>            Debug.Print fld.name, fld.value</span>
<span>        next</span>
<span>        rs.movenext</span>
<span>    loop</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
Sorry I missed that. &nbsp;It looks like the file is improperly delimited. &nbsp;I think Access is not recognizing the tab as the delimiter, assuming that tab IS the delimiter in the file. <br />
<br />
The last argument specifies that the file has a header row and it doesn't look like yours does. &nbsp; Make sure you are not appending to an existing table. &nbsp;You can only do that if the column headers match and yours won't.<br />
<br />
Import the file into Excel and see what Excel thinks of it.
</div>


<div>
@aikimark,<br />
Try running the code you tested with the file I just attached, and for each record do debug.print rs.fields(0), you will see what I mean.<br />
<br />
@Pat,<br />
I tried executing the following<br />

<pre><code class="code-1 nolinks" id="code-20-40526308-1"><span>    DoCmd.TransferText acImportDelim, , &quot;F:\Fuchs\Temp\testIT2.xls&quot;, &quot;F:\Fuchs\Temp\12-31-14.txt&quot;, False</span>
</code></pre>
didn't gave me an error, however doesn't look like anything was created, I don't see a file there.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w01/890402/12-31-14---Copy.txt" target="_blank" class="file-inline" title="data file (261 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>12-31-14---Copy.txt</a>
</div>


12-31-14---Copy.txt

<div>
1. You have changed your data. &nbsp;This appears to have column headers -- please use the updated schema.ini file I have uploaded<br />
2. There is a problem with your data -- you put an extra tab character after &quot;Name ZZZZZ&quot;<br />
3. Since you still have Null records, I have changed the SQL in the following code to eliminate them.<br />

<pre><code class="code-10 nolinks" id="code-20-40526547-1"><span>Sub Q_28588664(Optional parmDrivertype As String = &quot;ISAM&quot;)</span>
<span>    Dim conn As Object</span>
<span>    Dim rs As Object</span>
<span>    Dim fld As Object</span>
<span>    Const adOpenStatic = 3</span>
<span>    Const adLockReadOnly = 1</span>
<span>    Const adCmdText = 1</span>
<span>    Const cPath As String = &quot;c:\users\aikimark\downloads&quot;    '\\myserver\E\XFER   'F:\fuchs\temp</span>
<span>    </span>
<span>    Set conn = CreateObject(&quot;adodb.connection&quot;)</span>
<span>    Set rs = CreateObject(&quot;adodb.recordset&quot;)</span>
<span>    </span>
<span>    Select Case UCase(parmDrivertype)</span>
<span>        Case &quot;ISAM&quot;</span>
<span>            conn.Open &quot;DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=&quot; &amp; cPath &amp; &quot;;&quot;</span>
<span>        Case &quot;JET&quot;</span>
<span>            conn.Open &quot;Provider=Microsoft.JET.OLEDB.4.0;&quot; &amp; _</span>
<span>                   &quot;Data Source=&quot; &amp; cPath &amp; &quot;;&quot; &amp; _</span>
<span>                   &quot;Extended Properties=&quot;&quot;text;HDR=Yes;FMT=TabDelimited&quot;&quot;;&quot;</span>
<span>        Case &quot;ACE&quot;</span>
<span>            conn.Open &quot;Provider=Microsoft.ACE.OLEDB.12.0;&quot; &amp; _</span>
<span>                   &quot;Data Source=&quot; &amp; cPath &amp; &quot;;&quot; &amp; _</span>
<span>                   &quot;Extended Properties=&quot;&quot;text;HDR=Yes;FMT=TabDelimited&quot;&quot;;&quot;</span>
<span>        Case Else</span>
<span>            MsgBox &quot;Drivertype parameter must be string with one of these values: [ISAM,ACE,JET]&quot;</span>
<span>    End Select</span>
<span>    </span>
<span>    rs.Open &quot;SELECT * FROM [myfile.txt] Where YYMMDD Is Not Null&quot;, conn, adOpenStatic, adLockReadOnly, adCmdText</span>
<span>    Do Until rs.EOF</span>
<span>        For Each fld In rs.Fields</span>
<span>            Debug.Print fld.Name, fld.Value</span>
<span>        Next</span>
<span>        Debug.Print</span>
<span>        rs.movenext</span>
<span>    Loop</span>
<span>End Sub</span>
</code></pre>
<b>Note:</b> The code allows you to specify a different driver [&quot;ACE&quot;, &quot;JET&quot;, &quot;ISAM&quot;]<br />
<br />
<b>Note:</b> You need to assign the correct path to the cPath constant before testing. &nbsp;Alternatively, you may parameterize the path value by tweaking the code.<br />
<br />
<b>Note:</b> [999-99-9999] is a bad name for a column.<br />
<br />
<b>Note:</b> Be aware that social security numbers are a <i><b>terrible</b></i> thing to store in a database or text file.<br />
<br />
All three drivers should produce the following results in the Immediate window.<br />

<pre><code class="code-10 nolinks" id="code-20-40526547-2"><span>999-99-9999   111-11-1111</span>
<span>TOV-CD        C1112</span>
<span>EMPLOYEE NAME                             Name ZZZZZ</span>
<span>YYMMDD         141213 </span>
<span>WEHRS          28 </span>
<span>NoName        Null</span>
<span></span>
<span>999-99-9999   222-22-2222</span>
<span>TOV-CD        C1113</span>
<span>EMPLOYEE NAME                             OSSSSS Csssssss,RN</span>
<span>YYMMDD         141213 </span>
<span>WEHRS          22.25 </span>
<span>NoName        Null</span>
<span></span>
<span>999-99-9999   333-33-3333</span>
<span>TOV-CD        C1116</span>
<span>EMPLOYEE NAME                             MWERRRR CQQQQ</span>
<span>YYMMDD         141213 </span>
<span>WEHRS          37.5 </span>
<span>NoName        Null</span>
</code></pre>
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890421/schema.ini" target="_blank" class="file-inline" title="schema.ini (55 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>schema.ini</a>
</div>


<div>
Hi aikimark,<br />
1-Did all the above, and got attached error when running the following line<br />

<pre><code class="code-1 nolinks" id="code-20-40527013-1"><span>rs.Open &quot;SELECT * FROM [myfile.txt] Where YYMMDD Is Not Null&quot;, conn, adOpenStatic, adLockReadOnly, adCmdText</span>
<span>    </span>
</code></pre>
2- As mentioned above, the file comes from an external program, there not much I can do about it.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890478/1-1-15.png" target="_blank" class="file-inline" title="screenshot (64 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>1-1-15.png</a>
</div>


1-1-15.png

<div>
what does your file look like? &nbsp;I tested the posted code with the most recent text file that you uploaded.
</div>


<div>
I used the same file posted last as you can see from picture, and nothing was changed on that file, attaching again.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890479/12-31-14---Copy.txt" target="_blank" class="file-inline" title="data (261 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>12-31-14---Copy.txt</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890480/1-1-15.png" target="_blank" class="file-inline" title="screenshot (67 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>1-1-15.png</a>
</div>


<div>
You have failed to fix the file. &nbsp;You have an extra tab character. &nbsp;That was one of my earlier comments.
</div>


<div>
Which driver option did you use in your test? &nbsp;Have you tested all three drivers?
</div>


<div>
OK, now fixed that and still the same error when not sending a param, when using Jet and Ace I get the error just attached.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890489/Jet.png" target="_blank" class="file-inline" title="Jet.png (64 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Jet.png</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890490/12-31-14---Copy.txt" target="_blank" class="file-inline" title="data (263 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>12-31-14---Copy.txt</a>
</div>


Jet.png

<div>
Please try this version of the file.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890495/12-31-14---Copy--1-.txt" target="_blank" class="file-inline" title="12-31-14---Copy--1-.txt (261 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>12-31-14---Copy--1-.txt</a>
</div>


12-31-14---Copy--1-.txt

<div>
same thing, see attached.<br />
tried with all 3 params.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890497/NoParam.png" target="_blank" class="file-inline" title="error (64 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>NoParam.png</a>
</div>


NoParam.png

<div>
And the most recent version of schema.ini is in the f:\fuchs\temp directory?
</div>


<div>
yes, see attached.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890501/schema.ini" target="_blank" class="file-inline" title="schema.ini (55 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>schema.ini</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2015/01_w01/890503/Schema.png" target="_blank" class="file-inline" title="screenshot (68 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Schema.png</a>
</div>


Schema.png

<div>
since schema.ini has file names, you need to either rename the text file that I corrected as myfile.txt or add another section for whatever named file you are referencing in your Select statement.
</div>


<div>
Oh, I just &nbsp;realized that, and after changing it actually gives me your output.<br />
so now in order to accomplish the original insert, I should change the logic, instead of using rs.fields(0) and parsing it, I should loop thru the fields as in your example, and check if name of field is 999 its social, and so on?
</div>


<div>
Because of the header row, your fields have specific names, instead of just F1, F2, etc.<br />
<br />
What are you trying to do with the text data?
</div>


<div>
there are 5 fields there, and I want to insert field 1, 4 and 5 into a table (as shown in original code above ID: 40524908).
</div>


<div>
Is EmpTovWeeklyHours an Access table?<br />
What are the data types of the destination fields?
</div>


<div>
Hi Aikimark,<br />
sql table varchar, datetime, decimal.<br />
I will be out of the office Tom, will reply on sunday, good night.<br />
Thanks
</div>


<div>
Also, how many rows will these tab-delimited files contain?
</div>


<div>
I'm thinking that SSIS would be a simpler and quicker way to import this data. &nbsp;Why are you using Access as a middle man between the tab-delimited data and the database?
</div>


<div>
<i>What about &nbsp;reading the file character by character and replacing the commas, does someone have an example of this? </i><br />
<br />
If you still remain interested in that at this point, it can be done.<br />
Dim fs As Object<br />
Set fs = CreateObject(&quot;Scripting.Fi<wbr />leSystemOb<wbr />ject&quot;)<br />
Dim f As TextStream<br />
Set f = fs.OpenTextFile(SomeValidW<wbr />indowsFull<wbr />Path, ForReading, TristateFalse)<br />
Dim TheLineContents As String<br />
' Read the file line by line<br />
Do While Not f.AtEndOfStream<br />
&nbsp; &nbsp; If f.AtEndOfStream = False Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; TheFileContents = f.ReadLine<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'here you'd code what you wanted to do.<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'since it's a large string, I suspect that you use Left, Right, Mid and Trim<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'to get the characters you want and commit them to a table <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;If f.AtEndOfStream Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Exit Do<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end if<br />
&nbsp; &nbsp; &nbsp; end if<br />
Loop<br />
<br />
f.Close<br />
Set f = Nothing<br />
set fs = Nothing
</div>


<div>
@Nick,<br />
I am really interested in that too, however at this point I just think its only fair to promote aikimark, after all the efforts he had put to solve this issue, and finally the latest suggestion seems to be working.<br />
Therefore I posted a new question focusing on this topic, please look at the following link.<br />
<a href="https://www.experts-exchange.com/questions/28590449/How-to-run-find-and-replace-command-in-a-text-file-from-VBA.html" rel="ugc">https://www.experts-exchange.com/questions/28590449/How-to-run-find-and-replace-command-in-a-text-file-from-VBA.html</a><br />
Thanks.
</div>


<div>
Hi aikimark,<br />
This latest (with minor modifications) seems to be working, well.<br />
Thanks very much for all your efforts!!<br />
Ben
</div>


<div>
<div class="content wysiwyg-content">
Hi Experts,<br />
<br />
I have a code that reads data from a text file and imports into a SQL table, however when some records contains a comma as part of the data then the data gets truncated there and the code fails.<br />
&nbsp;<br />
The code below is used:<br />

<pre><code class="code-1 nolinks" id="code-10-28588664-1"><span>   conn.Open &quot;DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=\\myserver\E\XFER;&quot;</span>
<span>   rs.Open &quot;SELECT * FROM [myfile.txt]&quot;, conn, adOpenStatic, adLockReadOnly, adCmdText</span>
<span>    </span>
<span>   rs.MoveFirst</span>
<span>   a=rs.Fields(0)</span>
</code></pre>
<br />
I am also attaching the sample of data file.<br />
Any idea how to make it work is appreciated.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w01/890214/12-30-14.txt" target="_blank" class="file-inline" title="data file (128 bytes)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>12-30-14.txt</a>
</div>
</div>


12-30-14.txt

Hi Experts,

I have a code that reads data from a text file and imports into a SQL table, however when some records contains a comma as part of the data then the data gets truncated there and the code fails.
 
The code below is used:
(CODE)

I am also attaching the sample of data file.
Any idea how to make it work is appreciated.

Error reading text file from VBA

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.