Data Access Using ADO And Combobox Speed
I am using VB6 and have an Access Database (using ADO) with a table that has 6000 names. I want to create a Combo Box object so that when the progam starts all surnames are put into memory in an invisible Combo Box during the splash screen loading sequence.
Then when I need to display the combo box I can just use Combo1.Show command and the Combo Box can be used for that particular form without having to reload all the Surnames into the Combo box everytime a form loads up and needs that Surname Combobox.
Please help me as I am tearing my hear out. Thanks.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Fw: Oracle And VB...Data Access Speed
Hi,
I have ve developed an application using VB 6.0 where I'm usingDataGrid to
bind the data from Oracle 8i Server. At a time the exe is being used
by 23 users on LAN.
Now the problem is that as the size of table increases the
data access speed in the grid reduces tremendously.
I'll be really grateful, if anyone can tell me as how to retain the
speed of data access.
Sangeeta.
Call Data To Combobox Using SQL From Access
I would like to list out data from my table in Access into my combobox without using ADODB connection. Is it possible for me to do that ? At the moment i tried using SQL query but i could not list out the data in my ComboBox.
Below are my codes. Any advise or assistance would be very much appreciated.
Private Sub form1_load()
Dim rs As Recordset
Set rs = New Recordset
Dim StrSQL As String
StrSQL = "SELECT [ID] from test"
Set db = OpenDatabase(App.Path & "db1.MDB")
Set rs = db.OpenRecordset("SELECT * from [test]")
If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
Do While rs.EOF
Combo1.List rs!ID
Combo1.AddItem(Combo1.ListCount) = rs!test
rs.MoveNext
Loop
End If
Set rs = Nothing
End Sub
Thank you.
DATA ACCESS TO COMBOBOX FROM A TABLE
HI,
I designed a form where I have a combobox, in which I want to access a
column of a table. I am using ADOBD connection and connecting database
through a udl file.
Can anybody help me.
--
WITH REGARDS:
VIVEK RAJ
I Want To Take Data From A Combobox And Write To Access
hello everyone, i have a small problem that i'm sure most of you can help me with. I have a combobox that the user can enter new data in the text area. I know how to add the new entry to the combobox but how do i take the new data and write it to the file that populates the combobox so that next time it shows up in the combobox? Any help would be appreciated. Thanks much, bob mx125 #249
Insert Data Into Access Using Combobox And List Box
rs.Open "INSERT INTO BookReturnTable(Book-ID, Student-ID, ReturnDate, Status, Fine, AmountPaid)Values ('" & TxtBookId1 & "', '" & Text1 & "', '" & Text2 & "' , '" & x & "', '" & TxtTitle & "', '" & TxtAuthor & "')", cn
I'm using such statement to insert the data but no idea in inserting the data using list box and combo box...Need some guidance as well as some example...Thank you
Combobox Listing Access Database Data?
I keep getting a Run-time error '94'
Invalid use of a Null
highlight is on line SALES.AddItem rst.Fields(1)
I'm trying to make my SALES combo box's line items equal to each salesperson's name in each row under the one column entitled SALESPERSON in the .mdb database! Here's the code I'm using.
Code:
Private Sub sales_people()
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SERVER & "disp-1_be.mdb;Persist Security Info=False"
cnn.Open
Set rst = New ADODB.Recordset
rst.Open "salesperson", cnn
rst.MoveFirst
Do Until rst.EOF
SALES.AddItem rst.Fields(1)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
Please help. THanks!
Stephen
Displaying Data From An Access DB Table Into A Combobox
I have an Access DB with a few tables.
One's called tblTech with about 5 names in it.
In VB.NET I have a form and one of the objects is a combobox named comboTech.
When I debug the form and run it I'd like the comboTech combobox to display the names from the tblTech table in a dropdown list.
Data Connections (Solutions Explorer)
ACCESS.c:...DatabaseDatabase.mdb.Admin I can expand the tree of this DB in Solution Explorer and see my tblTech and the names in it.
Also...
When I created this connection... under the provider tab I selected "Microsoft Jet 4.0 OLE DB Provider"
Speed Of Combobox
I want to load approximately 5000 names (from an Access table) into a combobox. But it takes forever!!!! Is there any way to speed this process up?
I am using the "combo1.additem" method. I need the combobox because I can type a few letters of a name, and it will automatically scroll down to that name. Is there some other way to accomplish this, so that I don't have to use a combobox?
Need Help..COMBOBOX Instead Of DATA Command...n Link With ACCESS File
1. I am using VB 6.0. I have a customer database. I have created a program using DATA command from the toolbar (and linked it with Access file) such that when i select the customer code ...the whole info of that customer appears in the respective text box.
But here i want to use Combo Box instead of DATA command so that i can select the customer code form the pull down menu of the combo box.
How can i do this....NEED HELP....????
2. Also i gAve the link of DATA command to an ACCESS file (.mdb) but when i run my program on some other computer.....the program gives error asking for the same location for the Ascess file which was on the first system. How to rectify this....
because i need to create the programs and send it to somebody ......... the other person may copy the folder (containing program file and the Access file) on any drive may be C:,D:,E:,.....
please help...
thanks....
Update A Combobox When Add New Data In A Table In Access Aplication
I have a access aplication. In a form i have a combobox with display the data from a user's table. i can add in to the user's table without problem with a comand button open a form to register data to user's table but the trouble is the combobox don't update without close and open the form. I know exist different way to do this update, but i search a easy way to do that.
I find a code in other page, but the problem is i'm a newbie using ADO, DAO, and that solution used this aplication.
Only i need what code to use in the on notalist event of the form.
If need more information to understand what I need please only say me.
Speed Up ComboBox Loading
I am adding about at least 1500 to 3000 names to a combobox as show below.
Is there a better way of getting the names in the box? As I add more and more names it takes progressively longer to add a name to the list.
Any suggestions would be greatly appreciated.
Code:
For i = 1 to Ubound(username)
Me.cboOldUserName.AddItem (TempUserName)
Me.cboMACLsUserName.AddItem (TempUserName)
Next i
CD-ROM Access Speed
The form I was working on in this thread works great, except for one very odd thing.
When I insert a CD the first time and then select it from the Browse For Folder dialog, it fires up the CD and counts the files quickly. However, if I close the form and the do a recount, the CD-ROM doesn't spin as fast as it did (apparent by the lack of noise) and the counting is considerably slower, by a couple minutes. 5464 files take about 19 seconds to load at first, but then 2+ mins after that. I should add that the count is always accurate.
Any ideas on how to "light up" the cd-rom (spin at full speed) every time I access it?
Access Db Speed
this code is fast enough on a few hundred records, but once i get records over 1000 it slows down. any ideas on how to sped it up???
I need to know which records have the particular jobnumber.
'-------------------------------------------------------------------------------
For MMM = 1 To Material.RecordCount
Material.AbsolutePosition = MMM
If Trim$(Material("jobnumber").Value & "") = SelectedJob Then
List2.AddItem Material("id").Value & ""
End If
VV:
Next
'--------------------------------------------------------------------------------
Access Speed Problem
I have several VB6/ADO/Access applications with a similar problem:
Once the database grows over 100Mb and is opened Shared, multirecord DELETE instructions produce errors: "Disk or Network error."
The network seems quite responsive, and Selects are fast.
I have not noted any probs with INSERTs (I never INSERT more than a record at the time.)
With the program and database on single PC, all is well.
With database on the network all is well as long as the database <~ 100 MB
If I open the database exclusively all is well even > 100 MB (This is not a practical solution)
The problem also occurs with Delete queries from within Access.
Our IT department says all is well. No in depth Access knowledge is at hand.
Is there a way to tune Access (or any other way) to resolve this problem?
Thanks
Pete
Quote:
"I am not young enough to know everything." (Oscar Wilde)
SPEED UP THE COMBO ACCESS
How can i speed up the selection combo. As i am selecting itemname in the combo, from itemmaster table. I have clust.index on itemcode table.
pls. suggest / help me
kumar
mumbai / india
9322027344
Access Database Speed
I have an Access 2000 database which can grow to about 500MB.
I have normalized the database to make it more efficient.
However it still takes a couple of minutes to complete the Query and the database is only 50MB right now.
What am I doing wrong? Is there a more efficient way to open and query the database?
Should I use MS SQL Server instead?
Below is my code for querying the database and some examples of my SQL strings.
My queries work fine they just take forever once the database starts getting big.
Thanks for any help.
Sub GetODBCValue(strQuery as string, txtMenuValue as TextBox, strFormat as string)
Dim conStatistics as ADODB.Connection
Dim rstValue as ADODB.Recordset
on error GoTo ErrHandler
'//Open The Connection
set conStatistics = new ADODB.Connection
set rstValue = new ADODB.Recordset
'//Query Database
conStatistics.Open "DSN=ODBCErrorLog;uid=sa;pwd=;"
rstValue.Open strQuery, conStatistics, adOpenStatic, adLockReadOnly
'//Update Field on Menu Page
If Not IsNull(rstValue.Fields(0)) then
txtMenuValue = Format(rstValue.Fields(0), strFormat)
else
txtMenuValue = Format(0, strFormat)
End If
'//Close Recordset And Connection
rstValue.Close
conStatistics.Close
'//set to nothing to Prevent Memory Leaks
set rstValue = nothing
set conStatistics = nothing
Exit Sub
ErrHandler:
ProccessError ("GetODBCValue")
End Sub
SQL Strings:
strQuery = "SELECT Sum([DownTime].[Duration]) " & _
"as [Duration] FROM DownTime " & _
"WHERE([DownTime].[Scheduled] = 2)" & _
"AND ([DownTime].[Start time]) > (#" & _
DateValue(txtShftDate.Text) & " " & TimeValue(txtShftTime.Text) & "#)"
'//get Value from Db
GetODBCValue strQuery, txtShftBypass, "0.00"
'//Define Query string
strQuery = "SELECT Sum([CycleTime].[Cycle time])/60 " & _
"as [Production time]FROM CycleTime " & _
"WHERE (([CycleTime].[Cycle time] > 0) " & _
"AND ([CycleTime].[Start time]) > (#" & _
DateValue(txtShftDate.Text) & " " & TimeValue(txtShftTime.Text) & "#))"
'//get Value from Db
GetODBCValue strQuery, txtShftProdTime, "0.00"
'//Define Query string
strQuery = "SELECT Sum([DownTime]![Duration]) " & _
"as [Scheduled Downtime] From DownTime " & _
"WHERE ((([DownTime]![Scheduled])=0) " & _
"AND ([DownTime].[Start time]) > (#" & _
DateValue(txtShftDate.Text) & " " & TimeValue(txtShftTime.Text) & "#))"
'//get Value from Db
GetODBCValue strQuery, txtShftScedTime, "0.00"
'//Define Query string
strQuery = "SELECT 24*60*(Max([ErrorLog].[time Occured])" & _
"-Min([ErrorLog].[time Occured]))/(Count([ErrorLog].[time Occured])-1)" & _
"as MTBA From ErrorLog " & _
"WHERE ((ErrorLog.[Task Index]>0) and (ErrorLog.[Total time]>0) " & _
"AND (ErrorLog.[error Code] > -13000) " & _
"AND (([ErrorLog].[time Occured]) > (#" & _
DateValue(txtShftDate.Text) & " " & TimeValue(txtShftTime.Text) & "#)))"
'//get Value from Db
GetODBCValue strQuery, txtShftMTBA, "0.00"
Speed Of Data Sent (mscomm)
I'm sending data to a ticket printer via mscomm. If I step through my code, the data is received and the ticket is printed. If I run the executed code, the ticket is not printed and no errors are generated.
How do I slow things down? Is this the problem? I've tried sending the ticket data and then using sleep. Also tried with DoEvents but still no luck.
Here's the code snippet:
Code:
...
' Sets the printer to "on-demand" mode
Call ResetPrinter(cOperationCommand & "|C|^")
' Set the string of info to be printed.
strTicketInfo = "^P|0|1|" & strTicketInfo & strBarCodeNo & "|^"
' Print the ticket.
SendDataToPrinter (strTicketInfo)
kstrTestResult = "Passed."
...
Public Sub SendDataToPrinter(pvntData As Variant)
MSComm1.Output = pvntData
End Sub
Speed Up Data Display
I am writing an app that populates a datagrid through SQL after an cmdGo button is pressed. This cmdGo executes a SQL instruction and populates a temp table. After that the user can click on an item in the datagrid and press the cmdTarifario to get all the info from the temp table using dynamic SQL SELECT and displaying it on another datagrid on form2. My problem is that this operation to get the info from form2 is taking far too long (8-9 secs), taking in consideration that the temp table is much smaller than the original table used in the first SQL.
Is there anything I can do to speed up this query and display the results in the form2.datagrid1 ?
Please have a look at the code attached.
Speed Up Data Write
I Have a routine that draw images on the screen and writes a single record as a user advances the screen. If I remove the data write the adjacent images are displayed in 0.3 sec, but if I do a write total time is between 2.3-2.7 sec for a single record. How can I speed this up. Sample below:
Private Sub NextRecord()
If txtUnitID = "" Then Exit Sub
If gRejected1 = False And gRejected2 = False And Rejected1 = False And Rejected2 = False And gSpRejected = False Then
GoodUnit = True
StoreValues
End If
mLite(0) = 0
mLite(1) = 0
gRejected1 = False
Rejected1 = False
gRejected2 = False
Rejected2 = False
gSpRejected = False
lblSpacerRej.Visible = False
If RecordsInMemory > 0 Then 'Minimum of two Units
ClearForm 'ClearScreen
CurrentPos = CurrentPos + 1
If RecordsInMemory < CurrentPos Then Exit Sub
FirstUnit
SteppedOff = SteppedOff + 1
If RecordsInMemory > 1 Then NextUnit
If Audible = 1 Then
Call cbDBitOut(BoardNum, FIRSTPORTA, 11, ShortCircuit)
Sleep (150)
Call cbDBitOut(BoardNum, FIRSTPORTA, 11, OpenCircuit)
End If
If SteppedOff > UnitsRecorded And ID <> "NA" Then
RecordGoodUnit
UnitsRecorded = UnitsRecorded + 1
TotalUnits = TotalUnits + 1
lblTotal = "Total: " & TotalUnits
End If
If ID = "NA" Then SteppedOff = SteppedOff - 1
If UnitsRecorded = 10 Then
RemoveWindow
UnitsRecorded = 5
SteppedOff = 5
CurrentPos = 5
End If
End If
End Sub
Private Sub RecordGoodUnit()
Dim pcnn As New ADODB.Connection
Dim prst As New ADODB.Recordset
If ID = "" Then
Exit Sub
End If
On Error Goto Errhandler
pcnn.CursorLocation = adUseClient
pcnn.Open "Provider=Microsoft.Jet.Oledb.4.0;" & _
"Data Source = " & FilePath & "AS400Trans400.mdb"
prst.Open "tbl_AccessToAS400", pcnn, adOpenDynamic, adLockPessimistic, adCmdTable
If GoodUnit = True Then
prst.AddNew
prst![fldSchedule] = Schedule
prst![fldUnitId] = ID
prst![fldShift] = gShift
prst![fldOrder] = Order
prst![fldPLine] = PLine
prst![fldCustomer] = Customer
prst![fldFlag] = "C"
prst![fldWorkstation] = "A"
prst![fldIGLine] = IGLineValue
prst![fldDate] = Format(Date, "yyyymmdd")
prst![fldTime] = Time
prst![fldMakeup] = MakeUp
prst![fldDimensions] = Dimension
prst.Update
End If
ExitProc:
GoodUnit = False
If prst.State = adStateOpen Then
prst.Close
Set prst = Nothing
End If
If pcnn.State = adStateOpen Then
pcnn.Close
Set pcnn = Nothing
End If
Exit Sub
Errhandler:
Resume ExitProc
End Sub
Data Retrieval Speed
my weapons:
VB,Sql Server,ADO
my requirement:
retrieve data from 12 fields of 2 tables and populate the Flexgrid at a single event
my intention :
reduce network traffic
my approach:
set index on the table
using a single stored procedure
retrieve data using a forward only cursor in the SP ( a date passed as parameter )
fetch data <= this date
insert the data in a table type variable
retrieve the records from this variable by recordset from the front end
populate the flexgrid putting the recordset in a loop
all this in a single stored procedure
this doing pretty well
my question and ur valuable advice:
is this the right approach?
tables contain almost 750 rows
it takes 2.3 seconds to get 631 rows when i checked it in the querry analyser
is 2.3 seconds to retrieve 631 rows at a time reasonable (biggest field length-50)?
any other methods?
if i'm too precise , i'll give detailed code
my return :
one and hundred thanks in advance.
ready to give more
Speed Up Data Retrieve
I had list box on an Access 97 form with linked tables to a SQL2k database.
On a sub, there is this code:
If Me.lblAccount <> "" Then
Me.lstLog.RowSource = "SELECT ..."
Me.lstLog.Requery
End If
It is very slow. I know how to fine tune the select statement. My question is: is there any better/faster way of data binding?
Thanks!
Speed Optimising In Vb-access Application
Hello Everyone.
I have written a program where in i am reading data from excel and inserting records in access database. Now the problem is if the number of records in excel are less the program runs fine but as the number of records go on increasing the process time to insert each record goes on reducing thus affecting the performance. Can someone help me how to tackle this problem.
Awaiting for reply.
its really urgent.
Thanks in advance
Regards
Reena
Alternatives To MS Access For Speed And Size, Please Help
Dear all.
I am in the creation phase of creating a multi user app for SME's with
5 - 30+ Users.
They will each be using this mostly all day to access a central database.
The Database will have about 15 Tables.
Some tables will be small but others may contain 100,000 + records.
(there may be a furure need to create a lot more tables, i.e. 100's)
I will also have a need to store some small data locally on the client side.
i.e. Lists of reports they have created etc.
I am using VB 6 SP5.
I have started off my app using MS Access for desing and testing.
I understand that Access will struggle with Large amounts of data and concurent users over 5+.
Can anyone sugges an alternative to Access.
Needs to meet the following Criteria...
1. Easily redistributable
(My Sql, or Sql Server) would require a dedicated DB installation at each site on the Server before I could install the app on each client)
2. Can handle large tables . ie. 100,000 + records upto say 1 million
3. Ability to store data locally aswell as on the server.
4. Can handle 30 - 40 users concurrently and robustly.
(Access suffers here, with corruption and speed reduction)
5. Is fast.
I appreciate any comments or suggestions you may have.
Best regards
GT.
Access Database Speed Problem?
Hello there...
I have a problem when loading character information from a msAccess database for the MUD server im coding.
The problem is not a problem with the database itself, but with the speed on the loading data sub. This means that, when i try to log X character for the 1st time, the server will get stucked in some part and not send the 'log in' information to the client. But if i close the client and try again, since the array is already with the client's data, it works perfectly.... this happens with every new character i want to log on the server...
This is part of the 'incomming data' sub that decrypts everything from the string gotten from client. (GetUserInfo returns X data from Y name)
Code:
If Split(data, "`")(0) = "login" Then
Dim Password As String
Dim Password2 As String
Name = Split(data, "`")(1)
Password = Split(data, "`")(2)
If client(Socket).Vrfy = False Then ' Veryfication Incorrect
'Ban_Ip socket
Logout_Client Socket
Exit Sub
End If
Password2 = GetUserInfo(Name, "Password")
If Password <> Password2 Then ' Password Incorrect
SendData Socket, "deny`Wrong Password!`"
Logout_Client Socket
Exit Sub
End If
client(Socket).Name = Name: DoEvents
LoadClient (Socket) ' Loads all the character information from DB to Array
AddCharToRoom client(Socket).Location, Socket
DoEvents
SendCritInfo Socket ' Sends character information (stats, name, div, etc)
DoEvents
SendRoomInfo Socket ' Sends the client, the room information
DoEvents
End If
Code:
Sub AddCharToRoom(Location As String, Socket As Integer)
' Adds a new socket number to the list of Active players in the room list
Room(client(Socket).Location).PlayerList = Room(client(Socket).Location).PlayerList & Socket & "`"
End Sub
Public Sub LoadClient(Socket As Integer)
Dim Number As Variant
Dim ClientDB As DAO.Database
Dim ClientRS As DAO.Recordset
Set ClientDB = OpenDatabase(App.Path & "users.mdb")
Dim StrSQL As String
Dim InfoToReturn
StrSQL = "SELECT * FROM Crits " & "WHERE Name='" & client(Socket).Name & "'"
Set ClientRS = ClientDB.OpenRecordset(StrSQL)
client(Socket).Password = ClientRS!Password
client(Socket).Class = ClientRS!BaseClass
client(Socket).ShowClass = ClientRS!ShowClass
client(Socket).Level = ClientRS!Level
client(Socket).Exp = ClientRS!Exp
client(Socket).Armor = ClientRS!Armor
client(Socket).Hp = ClientRS!Hp
client(Socket).HpMax = ClientRS!HpMax
client(Socket).Mp = ClientRS!Mp
client(Socket).MpMax = ClientRS!MpMax
client(Socket).Gold = ClientRS!Gold
client(Socket).Attacks = ClientRS!Attacks
client(Socket).Location = ClientRS!Location
client(Socket).Str = Split(ClientRS!Stats, "`")(0)
client(Socket).Int = Split(ClientRS!Stats, "`")(1)
client(Socket).Agi = Split(ClientRS!Stats, "`")(2)
client(Socket).End = Split(ClientRS!Stats, "`")(3)
client(Socket).Wis = Split(ClientRS!Stats, "`")(4)
client(Socket).Chr = Split(ClientRS!Stats, "`")(5)
client(Socket).Sex = ClientRS!Sex
client(Socket).Div = ClientRS!Div
client(Socket).Picture = ClientRS!Pic
client(Socket).Race = ClientRS!Race
End Sub
Private Sub SendCritInfo(Socket As Integer)
'Sends character information (stats, name, div, etc)
'Used for login information.
SendData Socket, "fdisplayupdate`" & client(Socket).Name & "`" & client(Socket).Password _
& "`" & client(Socket).Level & "`" & client(Socket).Armor & "`" & client(Socket).Str _
& "`" & client(Socket).Int & "`" & client(Socket).Agi & "`" & client(Socket).End _
& "`" & client(Socket).Wis & "`" & client(Socket).Chr & "`" & client(Socket).Mp & "`" _
& client(Socket).Class & "`" & client(Socket).ShowClass & "`" _
& client(Socket).Race & "`" & client(Socket).Sex & "`" & client(Socket).Div & "`" _
& client(Socket).Gold & "`" & client(Socket).Exp & "`Monthly`" & client(Socket).Picture & "``nc``" & Chr(10) & _
"statupdate`hits`" & client(Socket).Hp & "/" & client(Socket).HpMax & "`" _
& client(Socket).Hp & "`" & client(Socket).HpMax & "`Ready``" & Chr(10) & _
"statupdate`attack`" & client(Socket).Attacks & "`" & client(Socket).Attacks & "``" & Chr(10) & _
"statupdate`level`" & client(Socket).Level & "`" & Chr(10)
End Sub
Sub SendRoomInfo(Socket As Integer)
'Sends Information about the ROOM the client is on.
'This is used for when a client walks in a room
'or when a client logs on.
'
' * Sends : short decription of the room, userlist, monsterlist, itemlist,
' room picture, etc.
'move`Temple of Fire`000006`You are in the Temple of Fire. The exit to the south leads back to Commerce Circle.``DefaultMenu`4385`C@4364@`K```
Dim SndDec As String
Dim ClientList As String
Dim Loc As Integer
Dim NumSocks As Integer
Dim SockList As String
Dim TempSock As Integer
Loc = client(Socket).Location
NumSocks = NumTok(Room(Loc).PlayerList, "`")
SockList = Room(Loc).PlayerList
ClientList = "ListUpdate`" & NumSocks & "`Output`End`"
For I = 0 To NumSocks - 1
TempSock = Split(SockList, "`")(I)
ClientList = ClientList & client(TempSock).Name & "`" & client(TempSock).Picture & "`1`" & I & "`"
Next I
SndDec = "move`" & Room(Loc).Name & "`" & Room(Loc).Picture & "`" & Room(Loc).sDesc & "``" _
& Room(Loc).Menu & "`" & Loc & "`````" & Chr(10) & Room(Loc).Menu & "`" & Chr(10)
SendData Socket, SndDec & ClientList
End Sub
thats pretty much it what is causing me trouble... maybe the problem is that im putting everything in different subs when i could just do it all together???
can someone tell me where is the point that VB gets stucked or lose the order of things.
Thanks a lot
Bruno
Connection Speed MySQL Vs Access
I am using vb6 and a MySQL db.
The MySQL db is:
- On the local pc
- a MyODBC connection {MySQL ODBC 3.51 Driver}
- a DSNless ADO connection
When I was using an Access db which was:
- a local .mdb file
- a ODBC connection
- a DSN ADO connection
My question is when I run a group of procedures in VB6 the time taken to complete them is about 25 seconds. The same set of procedures took under 4 seconds with Access.
I needed a larger / sturdier db for use with 20+ Users hence the change to MySQL.
Any ideas as to why this is taking so long. Is it something to do with the Driver / Version..
Any thoughts would be much appreciated
I
Nice Tip To Speed Up Database Access !!
How to speed up database access
Here is a trick to loop through a recordset faster. Often when looping through a recordset people will use the following code:
Do While Not Records.EOF
Combo1.AddItem Records![Full Name]
Eecords.Movenext
Loop
The problem is that everytime the database moves to the next record it must make a check to see if it has reached the end of the file. This slows the looping down a great deal. When moving or searching throuch a large record set this can make a major difference. Here is a better way to do it.
Records.MoveLast
intRecCount=Records.RecordCount
Records.MoveFirst
For intCounter=1 To intRecCount
Combo1.AddItem Records![Full Name]
Records.MoveNext
Next intCounter
You should see about a 33% speed increase.
i have taken this article from http://www.vb-world.net/tips/tip57.html
Regards
Neeraj Vasudeva
Setting Mouse Speed In VBA Access
I'm trying to adjust the mouse speed in VBA Access by using the SystemParametersInfo function in the API. I can get the mouse speed using:
mouse_temp = SystemParametersInfo(SPI_GETMOUSESPEED, 0, tp, 0)
but when I try and set the mouse speed with:
mouse_temp = SystemParametersInfo(SPI_SETMOUSESPEED, speed_val, 0, SPIF_UPDATEINIFILE)
it is unsuccessful.
I have declared the function and variables as:
Public Const SPI_GETMOUSESPEED As Long = 112
Public Const SPI_SETMOUSESPEED As Long = 113
Declare Function SystemParametersInfo Lib "user32" Alias _
"SystemParametersInfoA" (ByVal uAction As Long, ByVal uParam As Long, _
ByRef lpvParam As Any, ByVal fuWinIni As Long) As Long
Is there another way to adjust the mouse speed through VBA?
Cheers,
Felicity
Does Less Data Sent Equal More Speed With Winsock?
If I send a smaller amount of data over winsock will it be faster and, if so, is there a size that it wouldn't get any faster if I made the data smaller? Basicly Im wanting to know whatever you can tell me about sending a small amount of data (4 to 20 bytes, could be a little more) over the internet as fast as posible. I searched the internet and forum but couldn't find any sort of answer.
Thank, Josh
Data Types, Precision And Speed
I've been testing some VBA code that I wrote in excell. I'm having a problem getting the precision necessary for my output without making my processing become very high.
I initalize a global constant "Intergal" that sets the precision of the function. The higher "Intergal" is set, the better the precision of the output.
I only need the output to be precise to about 2 decimal places. Testing the function with different numbers for "Intergal" ---- is yielding that the global constant needs to be set at about 1000000 and a lot of resources are used up fast.
I converted my variable x to a decimal datatype and this seemed to help a little. Does anyone have any ideas or solutions??
How To Speed Up Exporting Data To Excel?
Hello all,
Is there anyway to speed up this code?
vb Code:
Private Sub cmdPrintVehicleLogQuery_Click() On Error GoTo ErrorHandler Dim oXLApp As Excel.Application Dim oXLBook As Excel.Workbook Dim oXLSheet As Excel.Worksheet Dim oXLRange As Excel.Range Dim i As Integer Dim j As Integer Set oXLApp = New Excel.Application oXLApp.DisplayAlerts = False Set oXLBook = oXLApp.Workbooks.Add Set oXLSheet = oXLBook.ActiveSheet Set oXLRange = oXLSheet.Range("A1:Z1000") For i = 1 To oXLBook.Worksheets.Count Set oXLSheet = oXLBook.Worksheets(i) If Not oXLSheet.Name = "Sheet1" Then oXLSheet.Visible = xlSheetHidden Next i '______________________________________ GoSub PrintArea GoSub PrintHeader GoSub PrintDetails '_______________________________________ oXLApp.DisplayAlerts = False oXLApp.Visible = True Set oXLRange = Nothing Set oXLSheet = Nothing 'oXLBook.Close SaveChanges:=False Set oXLBook = Nothing 'oXLApp.Quit Set oXLApp = Nothing Exit Sub PrintArea: With oXLBook.Worksheets(1).PageSetup .PrintTitleRows = "$1:$5" .PrintGridlines = True .Orientation = xlLandscape End With PrintHeader: With oXLRange .Columns("A").HorizontalAlignment = xlCenter .Columns("B").HorizontalAlignment = xlCenter .Columns("C").HorizontalAlignment = xlCenter .Columns("D").HorizontalAlignment = xlCenter .Columns("E").HorizontalAlignment = xlCenter .Columns("F").HorizontalAlignment = xlCenter .Columns("G").HorizontalAlignment = xlCenter .Columns("H").HorizontalAlignment = xlCenter .Columns("I").HorizontalAlignment = xlCenter .Columns("J").HorizontalAlignment = xlCenter .Columns("K").HorizontalAlignment = xlCenter .Columns("L").HorizontalAlignment = xlCenter End With For i = 0 To rs.Fields.Count - 1 oXLRange.Cells(1, i + 1).Value = Trim(rs.Fields(i).Name) oXLRange.Cells(1, i + 1).Font.Bold = True oXLRange.Cells(1, i + 1).Font.Color = vbRed oXLRange.Cells(1, i + 1).Font.Size = 10 Next i Return PrintDetails: i = 2 Do While Not rs.EOF = True For j = 0 To rs.Fields.Count - 1 oXLRange.Cells(i, j + 1).Value = Trim(rs.Fields(j).Value) oXLRange.Cells(i, j + 1).EntireColumn.AutoFit oXLRange.Cells(i, j + 1).Font.Size = 10 Next j rs.MoveNext i = i + 1 Loop Return GoTo CleanExit ErrorHandler: MsgBox Err.Number & ": " & Err.DescriptionCleanExit: If Not oXLApp Is Nothing Then Set oXLApp = Nothing If Not oXLBook Is Nothing Then Set oXLBook = Nothing If Not oXLSheet Is Nothing Then Set oXLSheet = Nothing End Sub
Right now if I export 200 records from the database to Excel, it takes over 3 mins. I really need to speed this up.
Thanks ahead of time.
MSflexgrid Data Loading Speed
Can someone please tell me the fastest way of uploading the data from an Access database on to a MS flexgrid. I am currently changing the grids visible property to false and using a string with 8 fields from the database, seperated by vbtab's. I then using the addnew command to add the string to the grid and make the grid visible again once the data upload has finished.
Is there not a different and faster way of doing this?
Speed Up Transfer Of Data To Excel
How can I speed up the transfer of data from a text file to Excel? Here is the code I have so far but it is extremly slow. Is it because I am adding record by record? I would use ADO to do this but the text file has well over 150,000 lines. Any help would be appreciated. Thanks.
Set objExcel = New Excel.Application
Set objWorkbook = objExcel.Workbooks.Add
ctr = 2
x = 1
Call AddExcelHeader
For i = 0 To UBound(sFileNames)
Open FilePath & sFileNames(i) For Input As #1
Do While Not EOF(1)
Line Input #1, InputData
ctr = ctr + 1
If ctr Mod 133 = 0 Then
lblStatus.Caption = CStr(ctr) & " records exported!"
DoEvents
End If
If ctr Mod 65535 = 0 Then
' Formats the Excel spreadsheet data
objWorkbook.Worksheets(x).Range("A:E").Columns.AutoFit
objWorkbook.Worksheets(x).Range("A1:E1").Font.FontStyle = "Bold"
objWorkbook.Worksheets(x).Range("A1:E1").Rows.HorizontalAlignment = xlCenter
x = x + 1
ctr = 2
End If
Call AddExcelHeader
objWorkbook.Worksheets(x).Cells(ctr, 1) = Trim$(Mid$(InputData, 1, 10))
objWorkbook.Worksheets(x).Cells(ctr, 2) = Trim$(Mid$(InputData, 12, 10))
objWorkbook.Worksheets(x).Cells(ctr, 3) = "'" & Trim$(Mid$(InputData, 23, 5))
objWorkbook.Worksheets(x).Cells(ctr, 4) = Trim$(Mid$(InputData, 29, 8))
objWorkbook.Worksheets(x).Cells(ctr, 5) = Trim$(Mid$(InputData, 38, 8))
Loop
Close #1
Next
Swi
Data Fetching Speed Problem
Hi all,
I have developed one accounting Software with VB6-Oracle and Crystal report 8.5. following are the code that interact with database. the software is running since last three years and now because of huge amount of data my software takes too much time to show report. please help. the following is the code for one particular report which takes almost 3 min to open.
select trial_balance.party_name,op_bal from trial_balance,balance where trial_balance.CATEGORY <> 'TRADING A/C' and trial_balance.start_year = '2003-2004' AND balance.start_year = '2003-2004' AND trial_balance.party_name=
balance.party_name order by trial_balance.party_name
'------------- this recordset fetches more than 300 rows -----------
trial_balance.Open sql, con, adOpenKeyset, adLockPessimistic
'--------------------------------------------------------------------------
With trial_balance
While .EOF = False
If Left(.Fields(0), 4) = "CASH" Then
sql1 = "Select sum(receipt)-sum(payment) from cash_book where "
sql1 = sql1 & "to_date(cash_date,'dd/mm/yyyy') between " & "to_Date('" &
ledger_date & "','dd/mm/yyyy') and to_date( '" & CDate(DTP_date.Value)
& "' ,'dd/mm/yyyy')"
Else
sql1 = "Select sum(debit)-sum(credit) from register_ledger_book where "
sql1 = sql1 & "to_date(ldate,'dd/mm/yyyy') between " & "to_Date('" & ledger_date
& "','dd/mm/yyyy') and to_date( '" & CDate(DTP_date.Value)
& "' ,'dd/mm/yyyy')"
sql1 = sql1 & " and party_name = '" & .Fields(0) & "'"
End If
'---------for every record of trial_balance recordset rec will open and close
rec.Open sql1, con, adOpenKeyset, adLockOptimistic
If rec.EOF = False Then
sql = " UPDATE trial_balance set "
sql = sql & " cl_bal = " & "'" & IIf(IsNull(rec.Fields(0)), "0.00", rec.Fields(0))
+ .Fields(1) & "'"
sql = sql & " where start_year = " & "'" & mm & "'"
sql = sql & " and party_name = " & "'" & .Fields(0) & "'"
con.Execute sql
End If
rec.Close
.MoveNext
'--------------------------------------------------------------------------------------
Wend
'-------------trial_balance recordset closed-------------
.Close
'-----------------------------------------------------------
End With
I want to speed up this operation. please suggest something.
Pari_java
Bitmap Pixel Access Speed Trials
I have started work on a big image processing project (real time stereo CCTV) so speed is crucial. Thanks to help on this board ( OnError especially) I have got the first bits working OK. I had read comments that Point was slow and GetPixel quicker. They both seemed terribly slow to me so I wrote my own. Here are the speed times for looking at 6,000,000 pixels in a big file various versions (actually Point and GetPixel are even worse because you still have to extract the colours from the Long integer result)
Method: Point
Uncompiled: 9.6 secs
Compiled: 8.2 secs
Clock ticks/pixel: 3862
Method: GetPixel
Uncompiled: 8.9 secs
Compiled: 7.9 secs
Clock ticks/pixel: 3721
Method: My Function
Uncompiled: 3.48 secs
Compiled: 0.43 secs
Clock ticks/pixel: 203
Method: My code inline
Uncompiled: 2.26 secs
Compiled: 0.1 secs
Clock ticks/pixel: 47
To say the least this is surprising. The inline speed is so quick that it looks suspicious but on the other hand what is there to do - it just does some stuff then looks in an array. I have checked this carefully but maybe I have screwed up? Any comments?
Bitmap file format see:
http://web.uccs.edu/wbahn/ECE1021/ST...fileformat.htm
Here is the relevant code:
Code:
'This prog puts a bitmap image into a byte array then accesses the byte 'array directly and compares the result with other methods of pixel access
Option Explicit
Dim bytFile() As Byte 'Accumulate BYTE file from bitmap here
Dim lngImageWidth As Long
Dim lngHeight As Long
Dim lngOffset As Long
Dim lngLineLength As Long
'Takes x,y coords as input and finds the RGB value of that pixel as a Long Integer
Private Declare Function GetPixel Lib "gdi32" (ByVal hdc As Long, ByVal x As Long, ByVal y As Long) As Long
Private Sub Form_Load()
Dim intFileNum As Integer 'File number for binary download
Dim intSource As Integer 'File number for binary download
Dim lngFileLength As Long 'Length of file as found by FileLen(path)
Dim strPath As String 'Path of test.jpg
On Error GoTo Errorhandler
'bytFile comes from a file held on hard drive
strPath = "C:\_DownloadP1090049.bmp"
'Get File Length
lngFileLength = FileLen(strPath)
'Redimension bytFile to fit size of file
ReDim bytFile(lngFileLength)
'Get a free file number
intFileNum = FreeFile
'Open file for binary access
Open strPath For Binary As intFileNum
'Put the contents of file into "bytFile"
Get #intFileNum, , bytFile
Close #intFileNum
'Find basic information about the bitmap file that we have loaded into bytFile
'Note that lngX and lngY go down to zero!
lngImageWidth = GetImageWidth(bytFile)
lngHeight = GetImageHeight(bytFile)
lngOffset = GetImageOffset(bytFile)
'Line length must be divisible by 4
lngLineLength = 3 * lngImageWidth
Select Case lngLineLength Mod 4
Case Is = 1
lngLineLength = lngLineLength + 3
Case Is = 2
lngLineLength = lngLineLength + 2
Case Is = 3
lngLineLength = lngLineLength + 1
Case Else
End Select
'Put File into the picture box
Set picImage.Picture = LoadPicture(strPath)
Exit Sub
Errorhandler:
End Sub
Private Sub Command1_Click()
Dim intBlue As Integer 'Value of Blue in Byte Format
Dim intGreen As Integer 'Value of Green in Byte Format
Dim intRed As Integer 'Value of Red in Byte Format
Dim lngPosition As Long 'Indicates position of pixel in file
Dim lngRGB as Long
Dim lngX As Long
Dim lngY As Long
Dim varTimer
varTimer = Timer
For lngY = 0 To 2111
For lngX = 0 To 2815
'Method 1
'lngRGB = picImage.Point(lngX, lngY)
'Method 2
'lngRGB = GetPixel(picImage.hdc, lngX, lngY)
'Method 3
'GetImagePixel bytFile, lngOffset, lngHeight, lngLineLength, lngX, lngY, intRed, intGreen, intBlue
'Method 4
lngPosition = lngOffset + (lngX * 3) + (lngHeight - 1 - lngY) * lngLineLength
intBlue = bytFile(lngPosition)
intGreen = bytFile(lngPosition + 1)
intRed = bytFile(lngPosition + 2)
Next
Next
MsgBox Timer - varTimer
End Sub
Public Function GetImagePixel(bytSample() As Byte, _
lngOffset As Long, _
lngHeight As Long, _
lngLineLength As Long, _
ByVal lngX As Long, _
ByVal lngY As Long, _
intRed As Integer, _
intGreen As Integer, _
intBlue As Integer _
) As Long
'Gets byte values of Red Green Blue from a Byte Array uploaded from
'a bimap image file.
Dim lngPosition As Long 'Indicates position of pixel in file
lngPosition = lngOffset + (lngX * 3) + (lngHeight - 1 - lngY) * lngLineLength
intBlue = bytSample(lngPosition)
intGreen = bytSample(lngPosition + 1)
intRed = bytSample(lngPosition + 2)
End Function
Public Function GetImageOffset(bytSample() As Byte) As Long
'Finds the offset in a byte file which has been loaded
'from a hard drive (ie where the real picture data starts
Dim strHex As String
strHex = "&H" & Hex(bytSample(13)) & Hex(bytSample(12)) & Hex(bytSample(11)) & Hex(bytSample(10))
GetImageOffset = CLng(strHex)
End Function
Public Function GetImageWidth(bytSample() As Byte) As Long
'Finds the WIDTH of image from the Image itself in a byte file which has been loaded
'from a hard drive (ie where the real picture data starts
'We could use picImage.Width but this is for interest!
Dim strHex As String
strHex = "&H" & Hex(bytSample(21)) & Hex(bytSample(20)) & Hex(bytSample(19)) & Hex(bytSample(18))
GetImageWidth = CLng(strHex)
End Function
Public Function GetImageHeight(bytSample() As Byte) As Long
'Finds the HEIGHT of image from the Image itself in a byte file which has been loaded
'from a hard drive (ie where the real picture data starts
'We could use picImage.Height but this is for interest!
Dim strHex As String
strHex = "&H" & Hex(bytSample(25)) & Hex(bytSample(24)) & Hex(bytSample(23)) & Hex(bytSample(22))
GetImageHeight = CLng(strHex)
End Function
Access 2000 - Report Speed Issue
I AM ATTEMPTING TO SPEED UP A REPORT. IT IS A SIMPLE LITTLE REPORT. NO CACULATED FIELDS. THERE ARE TWO MAIN TABLES OF DATA IT PULLS, AND A COUPLE REALLY DINKY TABLES (20 RECORDS OR LESS) THAT FILL IN SOME CATAGORY INFORMATION ON THE REPORT. IT IS TAKING ABOUT TEN SECONDS TO RUN ON P3 667 COMPUTERS. NOTHING I DO SEEMS TO ACCELERATE THE PROCESS.
TO MAKE THINGS A TOUCH MORE CONFUSING, WHEN I ATTEMPT TO PRINT THE REPORT WITHOUT OPENING IT IT TAKES A LITTLE LONGER, IF I SEND IT INTO PRINT PREVIEW, ITS QUICKER.
I KNOW TABLE SIZE HAS SOMETHING TO DO WITH THIS.
THERE IS A DUPLICATE REPORT, ON A DUPLICATE SET OF TABLES, BUT THEY ARE SMALLER. THE FIRST REPORT RUNS ON THE TWO TABLES OF 88,000 / 1500 RECORSD AND THE SECOND REPORT IS SOMETHINGK LIKE 1500/100 IN SIZE. AND THAT REPORT JUST SNAPS OPEN.
I'VE CHANGED SOME INDEXS, ADDING, AND REMOVING, BUT WITH LITTLE EFFECT. ANY HELP WOULD BE GREATLY APPRECIATED.
THANK YOU
JIM
How Can You Access The System Counters - Such As CPU And Mem Usage, Dl/ul Speed, Etc?
anyone know how to access these counters, and add them to a VB app?
theres the sysinfo control, but is there any api or anything to make this less bulky? also, how can you get the values of a certain counter? (the control seems to just add the values within itself, not usable to be put in a string or something)
an example is www.winbar.nl ..
its written in VB
how can you access these counters?
How Do I Speed Up Floppy Disk (write) Access?
My application (for a library) records all transactions to a floppy disk (not my idea, the library wants backup info they can "hold"). Anyways, this slows down the speed of my program because of all the disk accesses! When I originally wrote this code in VB3 for Windows 3.1, there was a switch in Windows (or DOS) which would allow disk access (writes) to occur as background tasks. Now with Windows 98 (2nd version), I can't find such a similar function.
So... Is there any way I can program this access to occur as a background task within my program? Right now I am simply using a print file# command.
I appreaciate any help.
Oh, and speaking of speed, I just got a new DELL 1.7GHz Pentium 4 system, and it takes forever (over 60 seconds) to boot up in Windows 98!? Any thoughts here?
Speed Of Two Large Joined Access Tables!
I have a many (~240) field Access table with only a few records in it joined onto another similar table.
Populating these tables takes a long time - for the first record, then it seems to wizz along after that.
Anybody know what might be going on, or if there is a way to speed things up?
Regards,
Alex.
Database Access Speed: Foxpro ODBC?
I have been working on a relatively easy project. .at least it seemed so. I need to access a small number of records from a 54000 record table. Bad news, the table is not ordered in any good way, so I have to "brute force" my search for my records. I have tried several methods of access including ODBC data connections with SQL and data access objects. The SQL works as long as i don't apply a WHERE. The WHERE generates an divide by 0 error. Unfortuanatly, if the data is the cause there is nothing i can do about it. . .the data is "untouchable" per managements orders (yes i am opening it in read only mode!) Right now i have the program open the datasource with a data access object, do a movelast, then perform my work. It is not too bad this way. If i do it with the ODBC data connection and sql it takes about 3 minutes to open. anyone have any advice on how to speed up this process? Oh, one more thing. The data is on a remote server connected by a high speed WAN. Any ideas would be appreciated!
David Moore
dm7941@sbc.com
Access BIOS And Retrieving Settings From It (e.g. CPU Speed)
I really need to find a CPU speed on Windows 95 computer, registry works only for NT, please help. I found one solution that should work, it's from BIOS, now i'm stuck again, because i'm not that smart....how do i get CPU speed from BIOS????
Speed Up Writing Data In Word Process
I have an 2D array in my VB program called arrTLE.
It's dimensions are approximately arrTLE(0-10, 0-310).
I need to populate this array to a word document so that the user can use do what they like with the data in Word.
I am using a table in word to populate the data into so that the spacing is "proper" for the user to work with the data.
My problem is that this taken around 30 seconds to do and I'd like it to be faster. Anyone know how I can change this to be faster.
Here's the relevant code
[php]Code
'Open Word to populate the data
Set WordApp = New Word.Application
WordApp.Documents.Add
WordApp.Visible = True
'Write the File info to word document
WordApp.ActiveWindow.ActivePane.View.Zoom.Percentage = 75
If WordApp.ActiveWindow.View.SplitSpecial = wdPaneNone Then
WordApp.ActiveWindow.ActivePane.View.Type = wdPageView
Else
WordApp.ActiveWindow.View.Type = wdPageView
End If
WordApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
WordApp.Selection.TypeText Text:="Delta Sat - " & Date
WordApp.Selection.TypeParagraph
WordApp.Selection.TypeParagraph
WordApp.Selection.TypeParagraph
WordApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
WordApp.Selection.TypeText Text:="FileName: C: leTempFilesgeo_" & DateStamp & ".txt"
WordApp.Selection.TypeParagraph
WordApp.Selection.TypeText Text:=sDate
WordApp.Selection.TypeParagraph
WordApp.Selection.TypeParagraph
'Add table
WordApp.ActiveDocument.Tables.Add Range:=WordApp.Selection.Range, NumRows:=1, NumColumns:=7
WordApp.Selection.Tables(1).AutoFormat Format:=wdTableFormatSimple1, ApplyBorders:=True, ApplyShading:=True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows:=True, ApplyLastRow:=False, ApplyFirstColumn:=True, ApplyLastColumn:=False, AutoFit:=False
'Set the column widths
WordApp.Selection.Tables(1).Columns(1).Width = InchesToPoints(0.51)
WordApp.Selection.Tables(1).Columns(2).Width = InchesToPoints(0.7)
WordApp.Selection.Tables(1).Columns(3).Width = InchesToPoints(0.63)
WordApp.Selection.Tables(1).Columns(4).Width = InchesToPoints(0.69)
WordApp.Selection.Tables(1).Columns(5).Width = InchesToPoints(2.1)
WordApp.Selection.Tables(1).Columns(6).Width = InchesToPoints(1.06)
WordApp.Selection.Tables(1).Columns(7).Width = InchesToPoints(0.63)
'Loop through arrTLE and populate word table using wdright to go to next cell to right
' *next row is automatic when using wdright
Dim lC As Integer 'Local counter
Dim lC7 As Integer 'Local counter loops 1 to 7 then starts over
lC = 1 'Set default
lC7 = 1 'Set default
'Populate table with loop through each column in the array
Do Until lC = Col - 1
WordApp.Selection.TypeText Text:=lC
WordApp.Selection.MoveRight Unit:=wdCell
WordApp.Selection.TypeText Text:=arrTLE(10, lC)
WordApp.Selection.MoveRight Unit:=wdCell
WordApp.Selection.TypeText Text:=arrTLE(9, lC)
WordApp.Selection.MoveRight Unit:=wdCell
WordApp.Selection.TypeText Text:=arrTLE(9, lC)
WordApp.Selection.MoveRight Unit:=wdCell
WordApp.Selection.TypeText Text:=arrTLE(0, lC)
WordApp.Selection.MoveRight Unit:=wdCell
WordApp.Selection.TypeText Text:=arrTLE(1, lC) & " i"
WordApp.Selection.MoveRight Unit:=wdCell
WordApp.Selection.TypeText Text:=arrTLE(7, lC)
WordApp.Selection.MoveRight Unit:=wdCell
lC = lC + 1
Loop
</PHP>
Reading Data From Excel Into 2-D Array: How To Speed Things Up?
Hi,
i'm using data from an excelsheet in my VB-application through automation. This means that I constantly call the excelobject to read cellvalues. This really slow things down a lot when dealing with large excelfiles.
Is there an easy way to save the data in an 2-D array or collection or whatever, without the need to loop through each cell and thus avoiding the 'costly' calls to the excelobject through automation?
For example:
Is it faster to use a recordset (with excelsheet as the table) compared to calling the excelobject, or doesn't it make any difference?
I noticed that exporting to an excelsheet is a LOT faster when you first fill a dataarray and then call the excelobject just one time to put everything in it. So now I'm looking for a comparable way to import from an excelfile to avoid those cosltly calls.
Any tips, advice would be great
Tuur
His Royal Dudenss
Speed Optimization: How To Compare Memo Type Fields Fast? (Access)
Hello all, I'm new to both this forum and VB6, and don't know all the ins and outs with access either, so forgive me if I ask a dumb question.
I have a performance issue with an application. To illustrate I'll give a quick overview:
At a given interval, the application shell()'s a dump utility to extract all eventlogs from a computer (This because I don't know how to do this thru the API, and I couldnt find example code) It dumps in .csv format. In 5 days, some of these files can get 600MB big.
A second part kills all remaining procceses (by PID), renames the files, starts the dump again and starts importing the renamed files into a database.
It does this by opening the renamed .csv files as textstream and going over it line by line, checking if the attached Message is already in the database. If so, it adds a reference to the message. If not it adds the message to the database, and then adds the reference. Then the rest of the record is committed to the database.
Problem:
With 300k + records, the search process get's very slow, since I have to compare in VB (Because Access can't compare memo type fields)
My sollution:
Add a 'cache' which remembers the last 10k records with Message and MessaegID, and loop this array first when comparing new records. Use the first 10k records while still collecting new one's till we have 20k, then cut off the bottom part and do again till 20k, etc.
Problem with sollution:
Still slow. (Since Virus scanners and backup's will always have unique messages due to the inclusion of filename and date/time information in the log message, and will fill the cache with garbage)
Anybody any suggestions ? I would post code examples but I doný know how they would be relevant right now.
If needed I'll be happy to supply them.
Thanx,
Jeroen
Edited by - PLJ on 2/27/2006 12:03:01 AM
|
TRACKER
