vb6 dao and begintrans & committrans

[luca90]

I have a old project with VB6 and DAO ( i have no time to modify this in ADO Jet:-(

To speed up rs.addnew (during import a value from a text file, approx 224.000 lines) i need:


for x = o to number of mylinetext
ws.begintrans '(ws is set as workspace)
rs.addnew
rs(kkkk)=Mid(line......) ecc...
rs(bbbb)=Mid(line......) ecc...

'here i need rs.update (????)
next x
ws.committrans

in effect, after the last rs bbbbb, i need the command rs.update or begin and commit trans update in "one shot only" the entir data into the access table?

note:

my conn and my table opned:

Public Sub CHECK_CONNESSIONE()

If DB Is Nothing Then
DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000
Set WS = DBEngine(0)
Set DB = WS.OpenDatabase("C:\L\QUADRA.mdb")
Set RS = DB.OpenRecordset("L", dbOpenDynaset)
Set RS1 = DB.OpenRecordset("L2", dbOpenDynaset)
Else
DB.Close
Set DB = Nothing
End If

End Sub

[DataMiser]

You really should consider using SQL Inserts rather than the RS.AddNew method

[Spoo]

Luca

Short answer is, I believe, that you need rs.Update for each record added,
although I think that you can eliminate the ws.begintrans and ws.committrans
statements.

So, something along these lines ,,,

' 1. set the MDB and RS
vMDB = myPATH + "\" + myMDB
vRS = myRS
Set DB91 = Workspaces(0).OpenDatabase(vMDB)
Set RS91 = DB91.OpenRecordset(vRS, dbOpenTable)
' 2. load the text file
fn = "c:\myFile.txt"
Close #1
Open fn For Input As #1
For ii = 1 to nn
    '
    Line Input #1, xtr
    '
    RS91.Addnew
    RS91(kkkk) = Mid(xtr, 1, 5)
    RS91(bbbb) = Mid(xtr, 6, 5)
    RS91.Update
    '
Next ii
Close #1
DB91.Close

[luca90]

Luca

Short answer is, I believe, that you need rs.Update for each record added,
although I think that you can eliminate the ws.begintrans and ws.committrans
statements.

So, something along these lines ,,,

' 1. set the MDB and RS
vMDB = myPATH + "\" + myMDB
vRS = myRS
Set DB91 = Workspaces(0).OpenDatabase(vMDB)
Set RS91 = DB91.OpenRecordset(vRS, dbOpenTable)
' 2. load the text file
fn = "c:\myFile.txt"
Close #1
Open fn For Input As #1
For ii = 1 to nn
    '
    Line Input #1, xtr
    '
    RS91.Addnew
    RS91(kkkk) = Mid(xtr, 1, 5)
    RS91(bbbb) = Mid(xtr, 6, 5)
    RS91.Update
    '
Next ii
Close #1
DB91.Close

I sPOO TKS!

But i need to close also the rs91 and set this nothing, or when i close the connection all object are destroied?

[Spoo]

Luca

I believe that once the statement DB91.Close is executed, RS91 is "automatically" closed.
That is, once the MDB is closed, all associated RS's are also closed.

Is that a satisfactory answer for you?
Recall, this is DAO, not ADO.

If you have another RS to work with before you close DB91, then, I must confess,
I don't know the syntax, although I presume it would be RS91.Close.

In my experience, the same thing is accomplished if you Set RS91 = ,,, (ie, to a different table name).
HTH

Spoo

[luca90]

Luca

Short answer is, I believe, that you need rs.Update for each record added,
although I think that you can eliminate the ws.begintrans and ws.committrans
statements.

So, something along these lines ,,,

' 1. set the MDB and RS
vMDB = myPATH + "\" + myMDB
vRS = myRS
Set DB91 = Workspaces(0).OpenDatabase(vMDB)
Set RS91 = DB91.OpenRecordset(vRS, dbOpenTable)
' 2. load the text file
fn = "c:\myFile.txt"
Close #1
Open fn For Input As #1
For ii = 1 to nn
    '
    Line Input #1, xtr
    '
    RS91.Addnew
    RS91(kkkk) = Mid(xtr, 1, 5)
    RS91(bbbb) = Mid(xtr, 6, 5)
    RS91.Update
    '
Next ii
Close #1
DB91.Close

Spoo... tested your code, work fine...but strange behavior

example, for each 100 or 200 records interted into the table code go slow.. attemp 2 or 5 seconds and after run normally, WHY?????

[Spoo]

Luca

example, for each 100 or 200 records interted into the table code go slow.. attemp 2 or 5 seconds and after run normally, WHY?????

I'm afraid that I do not understand your question ,,,
This is how I read it ,,,

You loop through the first 100 records .. that takes 2 seconds
The loop continues from 101 to nn and that works fine.

Is that what you are trying to describe?
If not, where did I go wrong?

Spoo

[luca90]

Luca



I'm afraid that I do not understand your question ,,,
This is how I read it ,,,

You loop through the first 100 records .. that takes 2 seconds
The loop continues from 101 to nn and that works fine.

Is that what you are trying to describe?
If not, where did I go wrong?

Spoo

yes!!!!

a part of that...

is possible to call directlly via vb6 code a query with the same connection disponible?

naturally via vb6 code and DAO object.

tks.

[Spoo]

Luca

I added to the earlier code frag ,, do a query (natch, before you close
the MDB).

' 1. set the MDB and RS
vMDB = myPATH + "\" + myMDB
vRS = myRS
Set DB91 = Workspaces(0).OpenDatabase(vMDB)
Set RS91 = DB91.OpenRecordset(vRS, dbOpenTable)
' 2. load the text file
fn = "c:\myFile.txt"
Close #1
Open fn For Input As #1
For ii = 1 to nn
    '
    Line Input #1, xtr
    '
    RS91.Addnew
    RS91(kkkk) = Mid(xtr, 1, 5)
    RS91(bbbb) = Mid(xtr, 6, 5)
    RS91.Update
    '
Next ii
Close #1
' 3. create a query
Dim sSQL As String
'
sSQL = "SELECT * " _
    + "FROM [strikes] " _
    + "WHERE Mid([cyclelen],4,2) = '01' " _
    + "ORDER BY [date]"
'
Set RSSQL = DB91.OpenRecordset(sSQL)
RSSQL.MoveLast
nn = RSSQL.RecordCount
RSSQL.MoveFirst
'
' 4. close the Query and MDB
DB91.Close

Note that ,,,
1. [strikes] is a table name (ie, a RecordSet contained in DB91)
2. [cyclelen] is a fieldname in the strikes RS
3. [date] is also a fieldname in the strikes RS

Modify it as needed

Spoo

[luca90]

Luca

I added to the earlier code frag ,, do a query (natch, before you close
the MDB).

' 1. set the MDB and RS
vMDB = myPATH + "\" + myMDB
vRS = myRS
Set DB91 = Workspaces(0).OpenDatabase(vMDB)
Set RS91 = DB91.OpenRecordset(vRS, dbOpenTable)
' 2. load the text file
fn = "c:\myFile.txt"
Close #1
Open fn For Input As #1
For ii = 1 to nn
    '
    Line Input #1, xtr
    '
    RS91.Addnew
    RS91(kkkk) = Mid(xtr, 1, 5)
    RS91(bbbb) = Mid(xtr, 6, 5)
    RS91.Update
    '
Next ii
Close #1
' 3. create a query
Dim sSQL As String
'
sSQL = "SELECT * " _
    + "FROM [strikes] " _
    + "WHERE Mid([cyclelen],4,2) = '01' " _
    + "ORDER BY [date]"
'
Set RSSQL = DB91.OpenRecordset(sSQL)
RSSQL.MoveLast
nn = RSSQL.RecordCount
RSSQL.MoveFirst
'
' 4. close the Query and MDB
DB91.Close

Note that ,,,
1. [strikes] is a table name (ie, a RecordSet contained in DB91)
2. [cyclelen] is a fieldname in the strikes RS
3. [date] is also a fieldname in the strikes RS

Modify it as needed

Spoo

not sure to understand .... i loop 123 ".txt" files in dir and use for all the same code:-(

perpahs i need to close connection and rset for each .txt file elaborate in to the loop and reopen connection and rset, or not?

[Spoo]

Luca

Sorry if I confused you regarding the "3. create a query" section.
It was intended to be a hypothetical ,, just to demonstrate the syntax.

It could just as easily have been in a separate Sub .. you would, however, need to "redefine"
DB91 prior to issuing the statement Set RSSQL = DB91.OpenRecordset(sSQL), ie, issue
a Set DB91 = Workspaces(0).OpenDatabase(,,something,,) statement.

As for loops, yes, you will need to modify my code frag to suit your needs. I do not see
any "harm" (ie, loss in speed) in issuing a DB91.Close often.

Perhaps you could post the code you have so far so we can get more specific in our replies.

Spoo

[luca90]

Luca

Sorry if I confused you regarding the "3. create a query" section.
It was intended to be a hypothetical ,, just to demonstrate the syntax.

It could just as easily have been in a separate Sub .. you would, however, need to "redefine"
DB91 prior to issuing the statement Set RSSQL = DB91.OpenRecordset(sSQL), ie, issue
a Set DB91 = Workspaces(0).OpenDatabase(,,something,,) statement.

As for loops, yes, you will need to modify my code frag to suit your needs. I do not see
any "harm" (ie, loss in speed) in issuing a DB91.Close often.

Perhaps you could post the code you have so far so we can get more specific in our replies.

Spoo

Sensible and reserved data are into the code, i can send in pvt?

[Spoo]

Luca

Sorry, but I would prefer that you did not send me a PM
The built-in editor here is much better.

Let's try to keep things generic.

The last question that you had was

i loop 123 ".txt" files in dir and use for all the same code:-(

If I understand you correctly, you are unclear how to deal with 123 text files,
and don't want to have to write 123 hardwired lines of code.

One possible solution would be to add a FileListBox control to your app.
Let's name it FB1.

Once you do that, you can specify the path to the subdirectory containing
the files, and then read each one in a loop. The single code frag we used before
can then be used to handle all 123 text files. Perhaps something like this ,,,

' 2. load the text files
' 2a. set the path to the FB
FBpath = "c:\abc\xyz"
With FB1
    .Top = 1000
    .Width = 2000
    .Left = 1000
    .Height = 5000
    .Pattern = "*.txt"
    .Path = FBpath
    v1 = .ListCount - 1
    .TopIndex = 0
End With
' 2b. read each of the text files
For ff = 0 to v1
    fn = FBpath + "\" + FB.List(ff)    
    Close #1
    Open fn For Input As #1
    For ii = 1 to nn
        '
        Line Input #1, xtr
        '
        RS91.Addnew
        RS91(kkkk) = Mid(xtr, 1, 5)
        RS91(bbbb) = Mid(xtr, 6, 5)
        RS91.Update
        '
    Next ii
    Close #1
Next ff

Hope that addresses your issue.

Spoo