vb6 dao and begintrans & committrans
:mad:I have a old project with VB6 and DAO ( i have no time to modify this in ADO Jet:-(:sick:
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
Re: vb6 dao and begintrans & committrans
You really should consider using SQL Inserts rather than the RS.AddNew method
Re: vb6 dao and begintrans & committrans
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 ,,,
Code:
' 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
Re: vb6 dao and begintrans & committrans
Quote:
Originally Posted by
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 ,,,
Code:
' 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?
Re: vb6 dao and begintrans & committrans
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
Re: vb6 dao and begintrans & committrans
Quote:
Originally Posted by
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 ,,,
Code:
' 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?????
Re: vb6 dao and begintrans & committrans
Luca
Quote:
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
Re: vb6 dao and begintrans & committrans
Quote:
Originally Posted by
Spoo
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.
Re: vb6 dao and begintrans & committrans
Luca
I added to the earlier code frag ,, do a query (natch, before you close
the MDB).
Code:
' 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
Re: vb6 dao and begintrans & committrans
Quote:
Originally Posted by
Spoo
Luca
I added to the earlier code frag ,, do a query (natch, before you close
the MDB).
Code:
' 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?
Re: vb6 dao and begintrans & committrans
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
Re: vb6 dao and begintrans & committrans
Quote:
Originally Posted by
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
Sensible and reserved data are into the code, i can send in pvt?
Re: vb6 dao and begintrans & committrans
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
Quote:
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 ,,,
Code:
' 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
