Help!
I have a text file (created in the Mainframe world) with fixed-length fields and records. I want to import to an Access table from it, using Schema.ini to identify field names and lengths. The Access table has already been defined.
I copied the following code from MSDN (Greg Stemp's article, "Much ADO About Text Files", 5 Mar 2004. I changed the value "strPathToTextFile" and the text filename in "Select * from .....". When I run the sub, it gets to the "Wscript" loop and runs 4ever, with no output.

Does anyone have any ideas on what is wrong? I'm using MS Access 2003 SP1. Here is the code:

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Databases\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

objRecordset.Open "SELECT * FROM PhoneList.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
Wscript.Echo "Name: " & objRecordset.Fields.Item("FirstName")
Wscript.Echo "Department: " & objRecordset.Fields.Item("LastName")
Wscript.Echo "Extension: " & objRecordset.Fields.Item("ID")
objRecordset.MoveNext
Loop

Try removing the "On Error Resume Next" statement and the problem should be pretty apparent.

It seems you are making it more difficult than it is though. That script you copied was meant to be used in a VBS file, while VBA in Access is much more robust. You'll really want to use something like the following:
TransferText Method

Regards,

Ax

Hi Ax:

when I remove the 'On Error Resume Next', you are correct. It is obvious what was missing. After fixing the name however (and the field name in the loop) I now get the message: "Run-Time Error 3265 Item cannot be found in the collection corresponding to the requested name or ordinal".

I would have expected the table "ADD0DATA" would have been created by the "Select" statement earlier, but no table exists.

The problem I have with "TransferText" is that I would need to build an ImportSpecification. My preference is the Schema.ini, since the field layout and field length for each of the 15 or so tables in the collection may change from time to time, and I do want to automate the procedure as much as possible. Now if someone could help me automate the creation of the Import Specifications from an already-created table, that would solve the problem.

Thanks, and back to you.
jnlw09

The error means that the data you are trying to import does not match what is set up in the schema file. Double check your field names/lengths to make sure they are consistent between the two files.

From the link I sent you:
You can use the schema file.

Correct. I had extraneous fields at the end of the data .TXT file. Removed, and I can debug.print by field name. Great. However, as previously mentioned, I had expected the SELECT statemtent to populate the ADD0DATA
table, but it does not. If I modify it to say SELECT * INTO ADD0DATA FROM ADD0DATA.TXT, then I get a permissions error insisting that the file is already opened exclusively by another user, or I need permission to view its data.

I'm continuing to work on this, but if you have any suggestions in the interim, they would be gratefully received.

.jnlw09.

I'm still curious why the TransferText method will not work for you, but nonetheless...

A normal SELECT query will just select data, it doesn't do anything with it. The SELECT * INTO table creates a new table based on the query results. If the ADD0DATA table already exists, you will want to use INSERT INTO, rather than SELECT * INTO. Not sure if the syntax you have is correct though.

Here's a good resource on the methods available for importing text files into Access:
ImportText.exe Importing Text into Access with ADO/RDO/DAO/Filesys/Automation

You can modify the logic to use declared variables, rather than form fields.

Regards,

Ax