text transfer question

[softwareguy74]

Assume that I have a text file which is comma delimited and has 5 fields. Assume also
that I do not need to manipulate any of the data and that I want to import the complete
text file into an Access DB table which also has 5 fields. The 5 fields in the text
file are in the same order as the 5 fields in the Access DB so again, I do not have
to do any manipulation.

Here's what I'm currently doing:.

I am reading in the text file and splitting the fields up into an array. I'm then
using that array and looping through and inserting each field into the Access table via
ADO such as:

adoRS!ID = Array(0)
adoRS!Name = Array(1) and so on..

HOWEVER, it is way too slow when there are 1,000's of records.

My question is, is there any function available via VB6 which will allow me to quickly
transfer the text file into the Access table? In other words, I want to mimic the Import
text feature that's built into Access because it is super fast.. Some things to keep in mind.. I do not want to do this in Access.. I need to do in via VB6. Access will not be installed on the user's machine, just the .mdb file with VB6 as the front end.

Any and all help would be appreciated..

Dan

The array is most likely slowing things down. You could try inputing to variables and assigning to ado from the variables while reading the file like so (from within a loop):

' Open the file and start a Do While EOF Loop
Input #lngFileNumber, Field1, Field2, Field3, Field4, Field5
adoRS!ID = Field1
' Etc.


Paul

[softwareguy74]

Thanks Paul but it ended up not being the Array's that are slowing it down.. I did the following test without array's:

For i = intCntr to 5000

adoPrimaryRS!Field1 = "test"
adoPrimaryRS!Field2 = "test"

Next

The above code takes about 5 minutes but Importing a 5,000 row text file into Access directly takes seconds..

Any other ideas? I'm going to submit another post and see if I get any other advice..

thanks,

Dan

You could try doing SQL INSERTs directly from the connection object with the Execute method rather than using a recordset.

conYourConnection.Execute "INSERT INTO TableName (Field1, Field2, Field3, Field4, Field5) VALUES (Value1, Value2, Value3, Value4, Value5)"

Good luck,
Paul

The only other thing I can think of is to make sure you are using the best driver for your connection string. ODBC is slower than native drivers. Something like:

conYourConnection = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=PathToYourMDBFile"

...is what I use for Access97.

Paul

[softwareguy74]

Okay, I tried the INSERT method and it is a tiny bit faster but when moving through 1,000's of records, there is really no noticeable gain.. But I think we're getting somewhere here..

It seems to be the LOOPING that is causing the slow down..

Yes, I am using the driver that you mentioned.. I think what would make this thing lightening fast is if we can do an INSERT INTO statment which grabs all data from the text file and INSERTS it into the Access table without looping.

However, I can't seem to get this to work when doing an INSERT from a text file to the database, but it does work from a database to a database. Check out the following code and try it for yourself. Command1 works but Command2 returns an error.. If we can get Command2 to work, we got it made!!!


Private txtDb As ADODB.Connection
Private Db As ADODB.Connection

Private Sub Form_Load()
Dim strCn As String

'-- set connection to acces db
strCn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data.mdb;"

Set Db = New ADODB.Connection
Db.Open strCn

'-- set connection to text file (courtesy: Iain_McCairns@hotmail.com)
strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
"DBQ=C:\;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=Admin;Pwd=;"

Set txtDb = New ADODB.Connection
txtDb.Open strCn

End Sub


Private Sub Command1_Click()
Dim strSQL As String

strSQL = "INSERT INTO test ( FName, LName ) IN 'c:\test.mdb' " _
& "SELECT data.FName, data.LName " _
& "FROM data"

'-- append data from table data (data.mdb) to table test (test.mdb)
Db.Execute strSQL

End Sub

Private Sub Command2_Click()
Dim strSQL As String

strSQL = "INSERT INTO test ( FName, LName ) IN 'c:\test.mdb' " _
& "SELECT data.FName, data.LName " _
& "FROM data"

'-- append data from table data (data.mdb) to table test (test.mdb)
txtDb.Execute strSQL

End Sub


Any idea on how to get Command2's code to work? It keeps telling me: "The Microsoft Jet database engine cannot open the file '(unkown)'...

any and all help would be appreciated..

Dan

Hi Dan,
I'm afraid I've exhausted my knowledge on this and I haven't seen the syntax you're using before. I have nothing else to offer. :( It looks like you're on the right track if you can just figure out why you're getting the error message.

Good luck,
Paul