Efficiently Assemble Conn In Module Help

Dis-Assemble
I want to take a base word, and dis-assemble it into all the possiblities.

For example, i wanna take the word mike, and make these combos:

mike
mik
mke
mie
me
mi
mk
ik
ie
ke

Its basically removing a letter, and making combos for the letters there.. Does that make sense?

---

How To Assemble SQL Statement On This
I have this table:

DocIDCmpntMaterialNumberSubFlagDeleteFlag
73003853-902
73003905-901
73003987-901
7320714 Y
7323116 Y
7354298


and I want to pull the CmpntMaterialNumber's that <> to SubFlag (Red Text) and DeleteFlag (Orange Text). In my results, I keep pulling only one result, not the four that should appear (black text). I'm using this SQL statement to populate a list box and I just can't get it.

Thanks for any advice..

---

How To Do This Efficiently?
In my interface I have just finished my 2d version. I am now implementing the 3d version.(same thing but in 3 dimensions) I will basically have to use almost the same forms as I did in my 2d application but with a little modifications. So what I did is I just created new forms and copied the controls over(the text boxes and everything) and I am using new global variables to hold the variables to prevent confusion. Now I have almost 30 forms (and global variables for each form). I am beginning to get the feeling that maybe I am doing this inefficiently.. Could anyone please let me know if there is an efficient way to do this.

---

How Can I Efficiently Search An Array?
Hi,

I have a program which inserts numbers into an array. It first searches the array to determine if that number is already there. If the number isn't there, it will insert that number into the appropriate position in the array. The array doesn't need to be sorted at any point, because the number is inserted in numerical order, i.e. if the numbers in the array are 2,3,6,9 and the number to be inserted is 7, it will insert it between 6 and 9.

Once the array reaches a significant size - over 1000 entries say - the array searching algorithm becomes very inefficient. It searches the entire array until it finds the number, at which point it stops. Its a brute force approach and I'm sure there must be a more efficient way to do it. I suspect that an efficient algorithm would follow similar logic to a QuickSort routine (which is the fastest sorting algorithm).

Can anyone help?

Cheers.

---

Writing Efficiently From VBA To A Worksheet
Hi.
I’m trying to make a write operation to a worksheet as quick a possible.
I’ve made an array (one dimensional string, but I can use a Variant if necessary), and I want this to be written to a given row, staring in column A. One way to do it is too loop through the array using

Sheet(“sheet 1”).cells(row, i) = array(i)

in the loop. But as far as I know this is not the most efficient way of doing it.

I have also tried using

Sheets(“sheet 1”).range(<proper dimension>) = array

This works, but some of my array elements contain formulas, and these end up being written in the cell rather than the result of the formula (if array(0) contains (“=Sum(A1:A2)” then that text will appear in the cell rather then the sum of A1 and A2).

Anyone?

Thanks for your time
-Terry

---

Efficiently Writing To SQL Table Using VB6
Below is a sub that I am using to write data received from a modbus connection to a PLC. I am using the data to keep a history file in a SQL database. To be of any use the data must be gathered very frequently and written in to database very often. Is there a more efficient way of writing this data to SQL than the method below. It looks like my loop times are around 11 to 12 seconds and need to be 1 to 2.



Private Sub WriteDigitalDataToSQL()

Dim ConnectionOne As ADODB.Connection
Dim rstHistory As ADODB.Recordset
Dim ConnectionString As String
Dim SQL As String
Dim i As Integer
Dim DataType As String
Dim ServerName As String
Dim Database As String
Dim Count As Integer


ServerName = "10.1.1.2"
Database = "WIG"

Set ConnectionOne = New ADODB.Connection
ConnectionString = "driver={SQL Server};server=" & ServerName & ";database=" & Database & ";UID=sa;PWD="
ConnectionOne.Open ConnectionString
Set rstHistory = New ADODB.Recordset
rstHistory.CursorType = adOpenKeyset
rstHistory.LockType = adLockOptimistic

i = 0
Count = 0

If Mod1.Result = 0 Then

Do While i < 480

DataType = "Digitals"

If chkVbDigitalTable(i).Value = 1 Then
SQL = SQL + "Insert into history values (" & "'" & Now & "'" & "," & "'" & DataType & "'," & i & "," & VisualBasicReceiveDigitalsTable(i) & ")"
Count = Count + 1
End If

i = i + 1

Loop


rstHistory.Open SQL, ConnectionOne


ConnectionOne.Close

If rtbLog.Text = "" Then
rtbLog.Text = Now & ": " & "Added " & Count & " digital items to the history file"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": " & "Added " & Count & " digital items to the history file"
End If

Else

If rtbLog.Text = "" Then
rtbLog.Text = Now & ": No digital items written...loss of communication"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": No digital items written...loss of communication"
End If

End If


End Sub

---

Help With Deleting Efficiently From A Table.
Hi all
I have a program that utilises three tables. Table one is a master that is read from, table two is a temp table where only the required info is written to from the master (and data can be added to the records), and the third is for archiving. I am using an apache database with SQL. The fields from the tables are populated into an MSFlexgrid.
I basically have nine fields per record (so one row of a table). Each record can be updated during the course of it's life. The way I am updating a record (if for example I want to add the word "Completed" to a field within the record), is to delete it from the temp table, then re-insert it using info from the program:

Code:
checklist.Execute ("DELETE FROM checklisttemp_table WHERE (job_time = '" & opschecklist.Text2.Text & "' AND sys_name = '" & opschecklist.Text4.Text & "' AND job_name = '" & opschecklist.Text5.Text & "')")

checklist.Execute ("INSERT INTO checklisttemp_table (job_time, sys_name, job_name, job_status, sign_user, jobname_day)values ('" & edit.Label2.Caption & "', '" & edit.Label3.Caption & "', '" & edit.Label4.Caption & "', '" & edit.Text1.Text & "', '" & opschecklist.Text7.Text & "', '" & edit.Text2.Text & "')")
My problem is, I need a more efficient way of deleting the record before adding it again, as if I have two records that are simular, they will both be deleted.
Unless anyone can think of a better way to do this.
AArrgghh...help.......
Thanks
Lee123

---

Integer To Binary Efficiently
Hi,
I need to do the following as efficiently as possible:

Convert an integer to Binary and put the 1s & 0s into an array.

This is what I have so far:

Code:
' Convert decimal to binary and fill the array
intQuotient = MyInteger

Do While intQuotient > 0
intReminder = intQuotient Mod 2
intQuotient = intQuotient 2

BinaryArray(ctr) = intReminder
ctr = ctr + 1
Loop


How can I do this any faster?
Is there a function to convert Integer to Binary or something?

---

Getting COM Port Response Efficiently In Vb
I want to know how to catch response from COM port without delay and how to check which port is open

---

Getting Data From EXCEL, Efficiently
Hello all!

I have experienced a huge time delay in looping through cells in a worksheet to compare, manipulate, get... data.

So I am wondering what is the most efficient way to retrieve data from EXCEL to manipulate, store, compare, etc... without having to loop through the text?

Would it be to turn the spreadsheet into a recordset to compare to another recordset.

Essentially, that is what I am doing. I am trying to compare data from EXCEL to a recordset I retrieve from ACCESS.

Thanks for helping VB City!

---

Normalising A Database Efficiently.
Ive written some code to normalise a database but it seems to be slow. I've look for some examples on the web and not found any. There is a wizard in access and that seems to be able to work i out pretty quickly. Where am i going wrong ?
Im sure Its just bad programming technique and my lack of knowledge.
I have one large import table of round 30000 records. plus other to import later

tbl_import_temp
Title, Artist, Brand, Format, TitleID,AristID, BrandID, FormatID

My Nomal tables are ...

tbl_titles
TitleID, Title

tbl_Artist
ArtistID, Artist

and so on.....

here is my code......

Code:Function CheckNormalForm(FieldName, TableName)
        Dim RS As New ADODB.Recordset
        Dim I As Integer
        Dim strValue As String
        Dim arrGetRows()
   
        strSQL = "SELECT DISTINCT " & FieldName & " FROM tbl_Import_Temp"
        Set RS = cn.Execute(strSQL)
        arrGetRows = RS.GetRows
        RS.Close
        For I = 0 To UBound(arrGetRows, 2) ' cycle through distinct rows
            strValue1 = arrGetRows(0, I)
            strValue = Replace(arrGetRows(0, I), "'", "''")
            strSQL = "SELECT " & FieldName & "," & FieldName & "ID FROM " & TableName & " WHERE " & FieldName & " Like '" & strValue & "'"
            Set RS = cn.Execute(strSQL)
            If RS.EOF Then ' item did not exist in normal table
                RS.Close
                RS.Open TableName, cn, adOpenDynamic, adLockOptimistic, adCmdTable
                RS.AddNew ' add the item
                RS(FieldName) = strValue1
                RS.Update
            End If
            intID = RS(FieldName & "ID") ' get its ID
            RS.Close ' put the ID in the temp import table to create relationships later
            strSQL = "Update tbl_import_temp SET " & FieldName & "ID = " & intID & " WHERE " & FieldName & " Like '" & strValue & "'"
            cn.Execute strSQL
        Next
        Set RS = Nothing
End Function

can anyone give advice on how else to do this ? We import quite a lot of flat files into this database but once a few have been done the process gets slower and slower. I want to get this faster without using SQLServer. (im on access)

Thanks

---

Scanning Through Excel More Efficiently
Hey guys,

     I'm looking to see if there is a better way to scan through an excel sheet. Right now what I've been doing is:

Code:
For i = 1 To Worksheets(1).UsedRange.Columns.count
For j = 1 To Worksheets(1).UsedRange.Rows.count
If Worksheets(1).UsedRange.Cells(j, i).Text = "mytext" Then
count = count + 1
End If
Next j
Next i

This has proved pretty efficient for me, but there seems to always be someone who has a better way. Any takers?

 

---

Synchronize Multiple Databases Efficiently.
4 companies: One database each. (That means 4 DB's)

They all have a table full of vendor information.

I need to check the consistency of the data in the DB's. That is, I need to make sure that every record is present in every DB. While I would be able to do this by looping through each database, reading the primary key and connecteing to each other database to compare the keys, I was wondering if anyone had any insight on a 'better' way of doing this.

---

Efficiently Limit A File To 100 Lines
I am developing a small app that will write a line to a file fairly frequently and I'd like to guard against the file getting longer than about 100 lines. Anyone have an efficient method to do that?

---

Stucked---how To Write This Loop Efficiently
I have a one dimension dynamic array say, my_array(), for example, this time, it's been populated at run time as
my_array(0)=6
my_array(1)=7
my_array(2)=8

obviously, the actual value and length of array can vary at run time
I want to write a select case such as


select case my_number
case 1 to 6
'do something here...
case 7 to 13
'do something here..
case 13 to 20
'do something here...
'hope you could see the pattern here...

how do I write this more effectively and generically

---

Moving Shapes Efficiently - For Games
Hi there,
I've been trying to develop small games in vb like pacman, tetris etc but I think I may be moving the shapes around the form in an inefficient manner. For instance to move a shape from side of the form to the other I would write the following in a timer procedure (interval set at say 50ms):

Private Sub timer1()
  shape1.left = shape1.left + 25
End Sub

Although this works, it doesn't really make the shape move very smoothly. Is there another way that I can move a shape around on a form??

---

Best Way To Populate Multiple Combo Boxes Efficiently??
Hi everyone,

I have a form which is going to act as a "bulk entry" sheet for the daily work statistics of the users of my application.

It contains a picture box containing a row of controls which repeats 50 times. One of the controls is the "action" box which will be a combobox populated from a database table. Whenever I have used comboboxes like this in the past I have always populated them in the dropdown event. However as this control is repeated and the drop down action is therefore potentially repeated 50 times, it surely doesnt make sense to hit the db and populate the combo for each instance of the control that is used does it??

Can anyone suggest a more efficient/better practice way of doing this please. Should I be considering something like a disconnected recordset (I'd then only hit the db once and the data to populate each instance of the action control would already be there) or is there an even better way to do this ???

---

Does Windows Allocate Memory For VB Programs Efficiently?
Well, first I apologize for asking a similar question in another thread - but I feel I did not express myself well enough in that one - and this is a really important issue for me and I would really appreciate any feedback.


This started because I fired up the task manager in XP and tried to watch the memory allocation/deallocation for my program. I was not happy with what I saw (looked like my program had a memory leak) so I went through my code and made sure all of my object references were set to Nothing, when I was done with them. Then I set all my form references to nothing and I even put code in the form terminate event as an extra check that my forms were destroyed.


None of this made any difference so I started doing some experiments with very simple programs. I still noticed the following:


1.) When forms are destroyed, the memory is not freed.


2.) Message boxes result in an increase in memory usage with no corresponding decrease.


3.) In my bigger program I noticed "greedy" behavior. In other words, if I created and destroyed a bunch of objects, memory would increase and not decrease, but then if I created the objects again, memory would NOT increase. It was almost like the program was holding on to memory in case it may need it again later. I feel for a true memory leak, this would not happen - memory allocation would just keep increasing.


Does any of this make any sense? Could setting objects to Nothing in VB code not be enough? Could a system call be needed to force the program to release memory?


Could the garbage collector be on strike?


Thanks in advance.

---

How To Download Images Efficiently With WebBrowser Control In VB.NET?
I want to download all the images inside an HTML page. I have created a WebBrowser control; navigate to a URL; and cast the webpage as mshtml.HTMLDocument:

Code:WebBrowser.Navigate(txtURL.Text)
Dim HTMLDoc As mshtml.HTMLDocument = WebBrowser.Document

After the call to the Navigate function, all the images in that page should be downloaded. My problem is that I don't know how to extract and save the images directly from HTMLDoc. Currently I am using another way to do the task: get the image src from HTMLDoc and use Internet Transfer Control to download the images. This seems stupid since the same picture is downloaded twice.

Some early post suggested to save the whole webpage in order to get the images. But I don't want to do that, coz in the source file the ".src" attributes within the IMG tags will be changed - I want to extract some information from the original ".src" attributes.

Anyone has a solution to this? Many thanx!

---

Many Checkboxes To Control What Data VBA Outputs- How To Code Efficiently?
I have the following code to create headings on a worksheet based on what the user has selected. What's worse is that there is identical code that actually outputs data on the sheet. Can anyone suggest a more efficient way to do something like this?

If I could cycle through an array of checkbox values perhaps this would speed things up but even then, each if statement would be a bit different. Maybe there is someway to use bitwise AND and ORs in the if statements??

Any ideas would be appreciated- I'm guessing there are many better ways to structure this.

Thanks,

Dave



Code:
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("U Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("U Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("U Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("U Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("D Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("D Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("D Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("D Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("R Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("R Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("R Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("R Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("L Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("L Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("L Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("L Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("Vertical Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Vertical Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift LSL")



Edit by Moderator:
Please post Excel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

---

Efficiently Loading Stock Price Data Into Excel
Hi everyone!

I refer to Peter's article at http://www.eggheadcafe.com/articles/20020108.asp
(quote: "The neat thing about this is that by simply changing the SQL Statement and supplying the name of ANY text file that resides in that folder, you can use the filesystem object (for example) to get a list of the files in the folder, and then read each file into a separate recordset object by simply looping through your array of stock price filenames and dynamically changing the SELECT statement to match on each iteration.")
and I'd like to ask the following:

I am looking for an efficient way to load stock price data into Excel from text files (or any other database stored locally , i.e. I'm not talking bout web queries) for testing purposes.
To be able to measure portfolio risk correctly, the data has to be loaded on a daily basis(*), one by one.

Does anyone have a sample file (e.g. for Excel VBA) which allows to learn the basics?
Would SQL, ADO or an Access database be the most appropriate solution for this?

(*) As I want to avoid recalculation of the same values (indicators, stops, risk, etc.) over and over again, I consider keeping all the calculated data in a database and calculate only one row (the newly added) in Excel.

I hope I explained everything clear enough.

I also post an excellent article on "VBA: Efficiency and Performance". - Enjoy!

Thanks for help and time.
Any help is appreciated.

Regards,
Thomas <tompf@gmx.at>

---

MySQL Conn
I need to make a connection to a MySQL Table, take some data and put them into a msFlexgrid

---

Conn.execute
Good morning all,

How can I tell if a conn.execute state was successful, or better yet if it changed any records in the spreadsheet.

Thank You

---

Help With Conn.execute
I have the following code which works fine

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset
Dim query As String
Dim i As Long

Set conn = New ADODB.Connection


conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dlgXls.FileName & ";" & "Extended Properties=""Excel 8.0;HDR=YES;"""
parsed.MoveFirst

Do While Not parsed.EOF
Select Case (cmbCompany.Text)
Case "Vong":
Select Case Trim(UCase(parsed("EarnCode")))
Case "RG1": conn.Execute "UPDATE [sheet1$] SET REGULAR = " & FormatNumber(parsed("Amount") / 100, 2, vbFalse, vbFalse, vbFalse) & " WHERE EmployeeNo = """ & parsed("EmpNum") & """ AND DepartmentNo = """ & parsed("Dept") & """"



How can i check if Parsed("EmpNum") exists in the excel spreadsheet column EmployeeNo.

Please advice

---

Conn Problem
Hi! I have a login form which i put on form load a call to a function in the module namen openConnection. Doing such i get an error "Not a valid file name" and the it highlights .open in my module.


VB Code:
Private Sub Form_Load()  Call openConnection  Set rsPwd = New ADODB.Recordset  'Call FillcboLogType  p = p + 1  'initialize timer2  s = 59End Sub



VB Code:
Public Sub openConnection()  Set oConn = New ADODB.Connection    With oConn    .Provider = "Microsoft.Jet.Oledb.4.0"    .CursorLocation = adUseClient    .Mode = adModeReadWrite    .ConnectionString = "Data Source=" & DBPath & "" & DBName & ";Persist Security Info = False;Jet OLEDB:Database Password=ed;"    [hl="#FFFF80"].Open[/hl]      End WithEnd Sub


If i remove the call on form load this i get this error "The connection cannot be used to perform this operation. It is either closed or invalid in this context.
VB Code:
Private Sub cmdLogin_Click()  If txtLoginName.Text = "" Then txtLoginName.SetFocus: Exit Sub  If txtPassword.Text = "" Then txtPassword.SetFocus: Exit Sub    Call ReplaceQuotation(txtLoginName)  Call ReplaceQuotation(txtPassword)      sSQL = "SELECT UserType,LoginName, Password, DateLastUsed,PwdLevel " & _          "FROM tblUser WHERE UserType='" & cboLogType.Text & "' " & _          "AND LoginName ='" & txtLoginName.Text & "' " & _          "AND Password='" & txtPassword.Text & "'"    If rsPwd.State = adStateOpen Then rsPwd.Close    [hl="#FFFF00"]rsPwd.Open sSQL, oConn, adOpenStatic, adLockOptimistic[/hl]        If rsPwd.RecordCount > 0 Then      If rsPwd("LoginName") = txtLoginName.Text And rsPwd("Password") = txtPassword.Text Then        Screen.MousePointer = vbHourglass        rsPwd.Fields("DateLastUsed") = "Date : " & Format(Now(), "Long Date") & " Time : " & Format(Now(), "hh:nn:ss AMPM")        rsPwd.Update                       WriteIniFile App.Path & "INILogs.ini", rsPwd.Fields("LoginName"), "Logged_In_Last=", " Date : " & Format(Now(), "Long Date") & " Time : " & Format(Now(), "hh:nn:ss AMPM")        WriteIniFile App.Path & "INILogs.ini", "LOGS", "Last_User", rsPwd.Fields("FullName")                      'to check for the flag        '0 = admin, 1 = authorized        userLevel = rsPwd("PwdLevel")                       Unload Me        MDIMain.Show        Screen.MousePointer = vbDefault      End If    End IfEnd Sub

What's wrong with my code?

---

HTTP Conn. Header
Hello,
while im making a MSNclient, i have to connect to a http host, with a header. How could i send a header to that server? do i have to use inet?

thnx in advance

---

Winsock On Internet Conn, Then.....
Hello, I'm making a chat prog and would like it to open a port only when an internet conn has been found (connected). And if the connection is lost (disconnected) then close the port. Finally, thereafter if the comp goes online again, then to have the port opened again. How could I do this with the Microsoft Winsock Control? I have some code to detect if the comp is connected to the net, but I don't know how to get the code to run continuously like in a timer to check if connected or disconnected. Thanks for your help,

-XtremeNTL

---

App Chrashing When Using Ado Conn To Msde
hi,
i have a serious problem, i have build an app, which is doing sql connections to an local msde and crashes after a few minutes when executing an sql command...its not always the same sql execute at where the app crashes, it seems like after a few executes made the app crashes!

i am doing async connections & executes to the sql server which look like this for example:


Code:
Private Sub Connect(MyConn As ADODB.Connection)

Dim tmpSQLVer As String

On Error GoTo ErrorHandler

Set MyConn = New ADODB.Connection

MyConn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=OxygenDB;" & _
"Pooling=True;" & _
"User ID=LocalOxygen;" & _
"Password=meinemutter"


MyConn.CursorLocation = adOpenDynamic

MyConn.Open , , , adAsyncExecute

While MyConn.State = 2
StdFunktionen.StatusLog "Connecting to Sql Server! State:" & MyConn.State
DoEvents
Wend
StdFunktionen.StatusLog "Connected to Sql Server! State:" & MyConn.State


tmpSQLVer = MyConn.version

If MyConn.State = adStateOpen Then
ConnectState = 1
Else
ConnectState = 0
End If

Exit Sub

ErrorHandler:

ConnectState = 0

StdFunktionen.StatusLog "ERROR in ScriptEngineDBCn.Connect"

End Sub


Private Sub Disconnect(MyConn As ADODB.Connection)
On Error GoTo ErrorHandler

If MyConn.State = adStateOpen Then
MyConn.Close 'close DATABASE

While Not MyConn.State = 0
StdFunktionen.StatusLog "Disconnecting from Sql Server! State:" & MyConn.State
DoEvents
Wend
StdFunktionen.StatusLog "Disconnected from Sql Server! State:" & MyConn.State

ConnectState = 0
End If

Set MyConn = Nothing

Exit Sub

ErrorHandler:

Set MyConn = Nothing

StdFunktionen.StatusLog "ERROR in ScriptEngineDBCn.Disconnect"

End Sub



Private Function GetValueofCat(Tabelle As String, SearchString As String, ReturnField As String, SearchField As String) As String
On Error GoTo ErrorHandler
Dim MyConn As ADODB.Connection
Dim myRecSet As New ADODB.Recordset

Dim myNothing

Call Connect(MyConn)

Set myRecSet = MyConn.Execute("SELECT " & ReturnField & " FROM " & Tabelle & " WHERE " & SearchField & " LIKE '" & SearchString & "'", myNothing, adAsyncExecute)

While myRecSet.State = 4
StdFunktionen.StatusLog "Waiting for SQL Server to answer...looping! State: " & MyConn.State & " ScriptEngineCBcn.GetValueOfCat"
DoEvents
Wend

If IsNull(myRecSet.Fields.item(ReturnField).value) Then
GetValueofCat = vbNullString
Else
GetValueofCat = myRecSet.Fields.item(ReturnField).value
End If

myRecSet.Close

Set myRecSet = Nothing

Call Disconnect(MyConn)

Exit Function

ErrorHandler:

StdFunktionen.StatusLog "ERROR in: ScriptEngineDBCn.GetValueofCat"

myRecSet.Close

Set myRecSet = Nothing

Call Disconnect(MyConn)
GetValueofCat = vbNullString

End Function


i am getting crazy...i don't know what else to do! please help


thx & greets

Matthias

---

Database Conn For Beginner???
i have 2 quikies for u gurus:

1 - for a beginning programmer, which is the best method of accessing a passworded database within a little amount of time???
reason: i would like to develop a perosnal app for storing secure values (cdkeys/passwords)

2 - do u no of the url or have the file *offhand* (i dont want 2 b an inconvience) which is a basic beginning tutorial to that database method

thk u

---

Conn.Execute.... Still Executing?
How can u tell when the Conn.Execute has stopped executing... Im opening a recordset immediately after i run my execute query (which updates the database), and it seems to be opening the recordset before it has a change to update the database.

---

Conn.execute Question
I have the following peace of code.

Conn.Execute ("UPDATE [sheet1$] SET REGULAR = " & parsed("Amount") & .....

This code currently overrides the value of REGULAR in the spreadsheet with the value of parsed("Amount"). How can I code it so that it doesn't override the value of REGULAR, but takes the value of parsed("Amount") and adds it the Current Value of REGULAR.

Thanks

---

Conn.Update Question
Hello everyone,

I'm having a problem with the following peace of code.

conn.Execute ("UPDATE [sheet1$] SET REIM TIP I = "

the problem is with the field REIM TIP I having spaces. If I change the field in the spreadshee to REIMTIPI and change it in the code it works. But if I leave it with spaces it gives me a syntax error.

Any idea on how to fix this, without renaming the field to REIMTIPI.

Thanks

---

Multiple Conn? ( Resolved)
can i open another connection within an open close conn?

---

Conn.beginTrans Query
hi,
as we know that a transaction begins with "conn.beginTrans"......
now somewhere in the code, before performing a "conn.rollbackTrans", i need to know whether the transaction is Active or Not.....
plz tell me how can i know that........

---

Data Conn To Oracle9i From Vb6
Hi:

I am writing a VB 6 Program (development server is Win 2k Server, SP4) which
needs to retrieve data from Oracle 9i DB (sitting in Linux Redhat Enterprise
Server ES2.0).

Appreciate you help to provide code for data connection and odbc setup



Thank You
sg

---

2 Conn. To 2 Diff. Databases
I have a VB app with 2 ODBC connection. 1 to visual foxPro 7 (ADODBcnnDbase) database and 1 to Access 2k database (ADODBcnnAccess). I want to read the data from ADODBcnnDbase and create a new table to ADOCBcnnAccess with it. No problem to did it in access using DAO but no idea to do it in VB using ADO. Somebody can help me ?

thank you

---

Visual Interdev ADO Conn
This is my code....


function _initRecordset1()
{
var DBConn = Server.CreateObject('ADODB.Connection');
DBConn.ConnectionTimeout = Application('Conn1_ConnectionTimeout');
DBConn.CommandTimeout = Application('Conn1_CommandTimeout');
*line352*DBConn.CursorLocation = Application('Conn1_CursorLocation');
DBConn.Open(Application('Conn1_ConnectionString'), Application('Conn1_RuntimeUserName'), Application('Conn1_RuntimePassword'));
var cmdTmp = Server.CreateObject('ADODB.Command');
var rsTmp = Server.CreateObject('ADODB.Recordset');





This is my error....

--------
ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/Project_dev/ASP_1.asp, line 352
----------

Line 352 has an asterisk at the beginning...

I have been beating my head against the wall for hours!! Can someone shed some light on me?! PLEASE!! :-)


Thnx

---

Conn.Execute Problem
I need to insert records into the 2 tables which are stored in SQL Server
The code :

....
....
sql1 = "Insert into TBL1 values" _
& "('" & idc & "','" & eng & "','" & chin & "','" _
& birth & "','" & add1 & "','" & add2 & "','" _
& add3 & "','" & idk & "','" & sex & "','" _
& phone & "','" & nat & "','" & photoPath & "','" _
& entry & "','" & leave & "','" & st & "','" _
& remark & "')"
sql2 = "Insert into TBL2 values" _
& "('" & id & "','" & gengn & "','" & gchinn & "','" _
& relation & "','" & gaddr & "','" & gphone & "','" _
& gphonew & "','" & mobile & "')"

'Enter the first record
conn.Execute sql1
conn.Execute sql2



The error message : "Method 'Execute ' of object 'Connection' failed"
Why?
I have checked the sql statements by inserting one variable by one. It works well, however,when I insert the variables up to, say 8, the program can't run and generated the above error statement

I don't know what happen. I think my sql statments are o.k.
I also tried to only executing sql2, i.e. conn.execute sql2 works well. It can insert values by sql2 to a referred table. But sql1 can't insert values to a referred table

---

Using The Conn.Execute To Retrieve A Recordset
Hi to all,

I have read many threads here especially in the database section that it is not a good idea to use connection objects execute statement when retrieving records?



Code:
set conn = new ADODB.Connection

with conn
.CursorLocation = adUseServer
.Open strConnectionString
end with

set rs = conn.Execute("SELECT * FROM TABLENAME;")
This is the fastest type of cursor right? This is a good way to use if you are just retrieving only for a value and do no want to navigate around the selected records. I use this when I need to display some records to the user.

I am just curious why many user says that we need to stay away from using this kind of statement.

Also, if the connection object finishes executing the SQL SELECT statement and returns it back to the recordset object, what will happen to the cursor in the server? Will it still stay there and consume memory or will be release entirely?

Thanks and god bless all!

---

Same Sql Command With Error On Difference Conn
I have 2 connectionstring for same access database and same sql command, one exclusive connection string and another for standar security connection string.

my probs is when I use exclusive connection string it return with :
"run time error 2147212900...."
"syntax error in INSER INTO statement"
line "cnn.Execute (msql)"

The standard security connection string works fine.


regards,

VB Code:
Private Sub Form_Load()Dim Koneksi As String 'Driver ODBC Connection StringKoneksi = "Provider=Microsoft.Jet.OLEDB.4.0;" & _        "Data Source=C:datadbInventori.mdb;" & _        "Persist Security Info=False;" & _        "Jet OLEDB:Database Password=123;" '[b]exclusive con[/b]  ' "Driver={Microsoft Access Driver (*.mdb)};" & _          "Dbq=dbInventori.mdb;" & _          "DefaultDir=C:data;" & _          "Uid=Admin;Pwd=;"  '[b]standard security con[/b]   cnn.Open Koneksi cmdtambah.Enabled = Falsecmdsimpan.Enabled = Falsecmdhapus.Enabled = Falsecmdbatal.Enabled = False End Sub Private Sub cmdSimpan_Click()Dim msql As String If txtid.Text <> "" Then                cnn.BeginTrans                If txtid.Enabled = True Then                msql = "Insert into tbUser(ID,Password,m1,m2,m3,m4)" & _                " Values('" & txtid.Text & "','" & txtpassword.Text & "', " & _                " '" & chkmaster.Value & "','" & chktransaksi.Value & "'," & _                " '" & chkstok.Value & "','" & chkfasilitas.Value & "')"                           cnn.Execute (msql)  '[b]error here!!!!![/b]                                 End If                txtid.Enabled = False        txtpassword.Enabled = False        txtkonfirmasi.Enabled = False        chkmaster.Enabled = False        chktransaksi.Enabled = False        chkstok.Enabled = False        chkfasilitas.Enabled = False                cmdsimpan.Enabled = False        cmdbatal.Enabled = False                cmdtambah.Enabled = True        cmdhapus.Enabled = True                        cnn.CommitTrans           End If End Sub

---

Odbc Conn In Ctrl Panel !
@ runtime

---

To Pass The Conn Object Or Not? That Is The Question.
With this code, how do I pass the conn object to another class module?
The name of the function that needs to receive the conn object is

Code:
Public Function Get_Search_RS(conn As ADODB.Connection, rsRecordset As ADODB.Recordset, strTitle As String, strCat As String, strFileX As String, strFileName As String) As String




Code:
Public Sub GetConn2()
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Sms001SharedwebInternetGraphic_SearchdbGraphics.mdb;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open

End Sub

---

Question About Conn To Access Through Apache
I have a virtual server running on Solaris 2.6 UNIX O/S and Apache 1.3 webserver. I can upload and download via FTP and also configure my domains via a telnet program.
Now that I have detailed my server, maybe someone could help me out.
I am trying to connect my Flash web page to an access database located in the same folder. I have read tutorials and books about connecting Flash sites to a database, but they all explain in for Windows NT with the ISS webserver, so it won't work for me.
I know this is a big subject, so could anyone point me in the right direction to figure out how to do this.
Thanks, I will appreciate this. -Gareth

---

Detect If Dial-up Conn Is Idle
does ne1 know how can i do that ?

---

ADO Control's Conn. String Builder Is Broken
Hi,

WHen i use the connection string builder of the ado control nothing happens.
I open the Property pages of the ADO Control , then i click on '(Custom)...' then i get a window with how i connect to the DB. i select 'Use COnnection String' and then i click on build. Normally another window would popup , building the conn. string. this time nothing happens , when i click the 'Build' button nothing happens :/

Maybe a wrong/missing DLL?

Grtz DePhille

---

Memory Usage On SQL-server After Closing Conn
Hi

I use ADO to connect to an SQL-server database, and after I'm done I close the recordset and connection, and I set both to Nothing.

On the server I see that the process sqlservr.exe is still using the same amount of memory that built up during the query. (200 MB)

Is this right? Does sql-server keep the data in the memory for faster access on future queries? (because runnning the same query later is faster...)

noccy

---

MySQL, ADODB.conn And Crystal Reports
I have to select some records of a mySQL database, print a specific report for each of them and then write a "1" in a field indicating that the report has been printed.
I have two doubts:
1) What table is the one I have to use in Crystal Reports? How can I reference it?
2) Is my sentence correct to put a "1" only in those records I selected and printed?

See my code attached.

---

Same SQL Server Conn String Wont Work
The same connection string will not work on 3 of the 5 computers in the office. All of which are connected through windows to the server using the same logon.

Here is the connection string anyway.


Code:
cnnCalls.Open "Provider=sqloledb;" & _
"Data Source=kesho;" & _
"Initial Catalog=calls;" & _
"User Id=MIKEY;" & _
"Password=jeff"

Where there is a user setup in SQL server for SQL authentification with the user name MIKEY (caps) and password jeff (lowercase).

Also I am attempting to connect this way because I cannot get a connection using windows authentification so if you know a good tutorial how to do so could you please give me a link.

As ever thanks for all the help.

---

Detect If A Record Exists Using Conn.execute
Hi everybody,
Assuming one uses only ADODB.Connection (No Rs or loops to drive into the columns), is it possible to send a SQL query and discover if one/more records satisfying the query exists in a DB. Sufficient if we can find the no. of records satisfying the SQL.
Any other effective method to accomplish this task would also be appreciated.
Thanks in advance.

---