Importing a Text file vb 2005


IDE : VS 2005,

I have been wracking my brain & searching the web for the past few days trying to solve this. I did find a method (from a couple sources) that i think i want to use to import a large (400,000 records into MS Access) text file into an existing table. The code I found, however does not work.
The part that fails is the commandtext/Select statement. I've tried other rendidtions of this but none of them seem to work.
Here is a sample of what fails:

Imports System.Data.OleDb

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mNewMSDB
connImport.ConnectionString = connString

c = "Insert into [raw] SELECT * FROM [TEXT;HDR=NO;DATABASE=D:\Projects\Committee\Committee\].[testvoters.txt]"
cmdImport.CommandText = c

If connImport.State = ConnectionState.Closed Then connImport.Open()
cmdImport.ExecuteNonQuery()
connImport.Close()

the error message i get implies that the name of the file its trying to open is blank and is:

System.Data.OleDb.OleDbException was caught
ErrorCode=-2147467259
Message="The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."
Source="Microsoft JET Database Engine"

I've tried other renditions of this select statment & get similar results.
So first off am i using this correctly? Are the any glaring errors.
Also, I'm not tied to this method, so if you know of any other methods that would work, I'm all ears.
Thanks
Gary


Edited by - NYWGUY on 5/1/2008 1:11:44 PM

Make sure you are not in the access database exclusively, or in the table,
Also when using a .txt file you need to do one of 2 things, either specifically name the columns in your sql
ie

Code:
c = "Insert into [raw](field1,field2,filed3) SELECT a.F1,a.F2,a.F3 FROM [TEXT;HDR=NO;DATABASE=D:\Projects\Committee\Committee\].[testvoters.txt] as a"


or create a shema.ini file in the same directory as your text file and then keep the same SQL as you had before

Code:
[testvoters.txt]
Format=delimited(,)
ColNameHeader=False
Col1=ID Integer
Col2=name char
Col3=address Char
CharacterSet=ANSI


source :http://www.vbcity.com

-------------------------------------------------------

Trik Gambar Bergerak

Trik Gambar-dimouse

Trik hapus pwd mysql

Trik insertin to db

Trik jadi root dilinux

Trik jam-distatus-bar

Trik Koneksi-ke database

Trik Koneksi-msql-php

Trik lihat-database-mysql

Trik membahas-fungsi-else

Trik member-area