Would someone please take a look at this for me please. I downloaded this routine from MSDN. I cannot figure out why I can't change the name of the input text file.
Here is the code.

Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim PathtoTextFile As String Dim PathtoMDB As String Dim myarray() As Variant Private Sub Form_Load()   PathtoTextFile = "D:\"   PathtoMDB = "C:\"   cmdOpen.Caption = "Open textfile and display field value"   cmdInsert.Caption = "Insert textfile values into MDB" End Sub Private Sub CmdOpen_Click()   '============================method 1=================================   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _           "Data Source=" & PathtoTextFile & ";" & _           "Extended Properties=""text;HDR=YES;FMT=Delimited"""   rs.Open "select * from tmpFile.txt", _           cn, adOpenStatic, adLockReadOnly, adCmdText   'MsgBox rs(0)   rs.Close   cn.Close End Sub Private Sub CmdInsert_Click()   Set Cat = New ADOX.Catalog   Set objTable = New ADOX.Table   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _           "Data Source=" & PathtoMDB & "tst2.mdb"     sConString = sProvider & sSource & sOptions   'Open the Catalog   Set Cat.ActiveConnection = cn   'delete the table if it exists   On Error Resume Next   Cat.Tables.Delete "table1"   'Create the table   objTable.Name = "Table1"   'Create and Append a new fields to the "table1" Columns Collection   objTable.Columns.Append "SL", adVarWChar   objTable.Columns.Append "Item", adVarWChar   objTable.Columns.Append "Part Number", adVarWChar   objTable.Columns.Append "Title", adWChar   objTable.Columns.Append "Material Description", adVarWChar   objTable.Columns.Append "Material", adVarWChar   objTable.Columns.Append "Designer", adVarWChar   objTable.Columns.Append "Parent", adVarWChar   objTable.Columns.Append "Int Name", adVarWChar   Cat.Tables.Append objTable   'Insert into table1 the contents of textfile.txt   cn.Execute "INSERT INTO Table1 SELECT * FROM " & _              "[Text;Database=" & PathtoTextFile & ";HDR=yes].[textFile.txt]"              MsgBox cn   cn.Close   MsgBox "Finished Inserting into MDB" End Sub

On the fifth to the last line , the cn.Execute command uses
.[textFile.txt]  <----- input file name
If I make an exact copy of textfile.txt and rename it to something else (tmpfile.txt) and put this file name in the brackets I get no results. I must be missing something simple but I can't find it.

Thanks,
Keith




--edited for VBCode tags for clarity jlrodgers--

[Edited by JLRodgers on 02-03-2005 at 12:54 PM GMT]

Make sure you alter the "PathToTextfile"... it's currently looking at the "D:\" (D drive)...

unless that's where it is on yours....

I am assuming you didn't change the code.

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

That part is fine. I have 2 identical files in the same directory (d:\)
textfile.text (which works) and tmpfile.txt (which doesn't work)?

I'm thinking it has something to do with the way it is connecting or the connection string.

keith
by the way thanks for cleaning up the code. How ja do that?

If anyone has tima to look at this, here is the data file that works. Make a second copy of this file and rename it  and see what it takes to get it to work. I am just adding it to a blank MDB file : tst2.mdb.

Thanks again.

Sorry the attachment did not work.

textfile.txt :

"SL","Item","Part Number","Title","Material Description","Material","Designer","Parent","Int Name"
"0","1","30-2-1-00-989","CLAMP BLOCK","2x1 1/2x2 1/2 LG FLAT","Aluminum-6061","KP1","30-2-1-00-980_REVB","30E96C0B-44A6-6CC3-DBB1-BDB0C733AED9"
"0","2","30-2-1-00-990","ASY,DRIVE WHEEL","N/A","N/A","Keith","30-2-1-00-989","99E3CD21-4539-AABE-CA32-7B82FA191901"
"1","3","980_O-Ring","BELT,POLYCORD,5 mmx11.36 LG","POLYCORD, 5mm","N/A","Keith","30-2-1-00-989","9D7CF49D-4A47-343C-5EE6-9B80D9CBDB0C"
"1","4","30-2-1-00-991","DRIVE WHEEL","4 DIAx5/8 LG RND","Aluminum-6061","Keith","30-2-1-00-989","9D7CF49D-4A47-343C-5EE6-9B80D9CBDB0C"
"0","5","30-2-1-00-992","GUIDE PLATE,BOTTOM","1/4x4x3 LG FLAT","Aluminum-6061","Keith","30-2-1-00-980_REVB","A208A7B4-482E-8B89-63AD-26B883227E63"
"0","6","30-2-1-00-993","GUIDE PLATE,TOP","1/4x2x3 LG FLAT","Aluminum-6061","Keith","30-2-1-00-980_REVB","A208A7B4-482E-8B89-63AD-26B883227E63"
"0","7","30-2-1-00-994","MTG ANGLE,GUIDE SHAFT","1 1/4x1 1/4x3/16x1 1/2 LG ANGLE","Aluminum-6061","Keith","30-2-1-00-980_REVB","8CD600CF-46FF-B7AB-1CE4-FB8C2352801F"
"0","8","30-2-1-00-995","MOD,SET COLLAR,3/8","3/8 SET COLLAR","Steel","Keith","30-2-1-00-980_REVB","1E6A2484-445F-0D61-B8E7-AF84FFBC2DA8"
"0","9","30-2-1-00-981","MOD,PLATE,GUIDE BELT","1/2x2 1/2x3 1/2 LG FLAT","Aluminum-6061","Keith","30-2-1-00-980_REVB","C10B8988-43B4-E4B2-2A91-719B86527EF1"
"0","10","30-2-1-00-984","BRKT,CYL MNT","1/4x1x2 1/2 LG FLAT","Aluminum-6061","Keith","30-2-1-00-980_REVB","1F98235B-4BFE-6458-1FCC-DC8895D4EB4B"
"0","11","30-2-1-00-988","GUIDE SHAFT","3/8 DIAx6 LG RND","Steel, 1045 TG&P","Keith","30-2-1-00-980_REVB","1D5E034F-4305-C664-0974-C2A2FE6B9D81"
"0","12","PX-PNC-001","CYL,AIR,1 1/16x2,DBL SHFT,TAPPED FRNT HOLES","BIMBA; FT-092-3F","N/A","Keith","30-2-1-00-980_REVB","9B3BACB3-4BED-BA20-8F16-31B952C2D9A1"
"0","13","PX-MAG-063","GRMTR,1/125HP,220VAC,3PH,600RPM","ORIENTAL; 21K6GN-SW / 2GN18KA","N/A","Keith","30-2-1-00-980_REVB","A33A8A4A-4121-8330-72EA-BEBA9B6FF11C"
"0","14","GuideBrgBrnz","BRG,BRZ,3/8x1/2x1/2","BUNTING; EP060808","N/A","Keith","30-2-1-00-980_REVB","558643D3-408A-105C-74D2-DFB604E61074"
"0","15","GuideBrgBrnz","BRG,BRZ,3/8x1/2x1/2","BUNTING; EP060808","N/A","Keith","30-2-1-00-980_REVB","558643D3-408A-105C-74D2-DFB604E61074"
"0","16","30-2-1-00-99b","STOP NUT","Ø1x1/4 LG RND","Steel, 1045 TG&P","Keith","30-2-1-00-980_REVB","E7E4D394-4222-4C93-3BA1-299B30314765"
"0","17","30-2-1-00-99b","STOP NUT","Ø1x1/4 LG RND","Steel, 1045 TG&P","Keith","30-2-1-00-980_REVB","E7E4D394-4222-4C93-3BA1-299B30314765"
"0","18","30-2-1-00-997","BUMPER PAD,STOP NUT","Ø1x1/4 LG RND","UHMW, White","Keith","30-2-1-00-980_REVB","A4FFDA70-4667-28AA-9C8D-09AB4EFFFDC4"

Okay, this is madening!  
I am writing the tmpfile.txt with Inventor VBA. If I try to read that file in it does not work. BUT, If I cut & paste the data from tmpfile.txt to a different txt file it works. So the problem appears to be associated with the file structure and not the file name.  Could this have something to do with carriage returns or something like that?  maybe end of file something that cut & paste takes care of.
Any Ideas?

Thanks,
keith

Okay, I appoligize but I am very confused (as you can probably tell). I have zipped up 2 text files. tmpfile.txt was written by my VBA for Inventor application. It does not work with the application at the beginning of this thread. Then I opened tmpfile.txt, selected the text, cut it open a new text file and paste and name this file textfile.txt (or whatever) and it works. What in the heavens could be different between these two files?

I guess I can't attach a file? is ther anyway to share these text files?

Thanks for looking.
Keith

Put
[ VBCode ]

[ /VBCode ]

around the code... just without the spaces, and it is case sensitive.

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

Okay as a follow up. As unlikely as it seems, it appears that I chose a filename (tmpfile.txt) that for some reason the OS doesn't like. It was somhow confusing it with other tmp files. So a simple change of file name fixed the problem. I had tried this earlier but had other formatting problems at the time so it did not seem to fix the problem.

Thanks for the help.
Keith