Question Archive

A Note From The Guru

Date:  April 27 2002

I am sorry but I must once again suspend the Ask The Guru section.  I am still not working but looking for new digs has turned into a full time job by it self, couple this with the fact that I am receiving between 20 and 30 questions a day, I just do not have the time.  I have been answering some questions via Email but I have not been posting them here in a while because Tripod has been timing out way too many times while I tried to post.

Below is a listing of some of the questions I have been answering in the past month or so.

Thread Blocking ?

From:  Priyadarshan
Date:  April 01 2002

Hi there,

I am a new VB user and finds VB a very Fantastic language to work upon. Currently I am working on creating an Application which puts data in the Oracle database. I am fine with all the technologies used for it but my querry is something related to the core VB language.

The situation is that on the click of one button named "START", the procedure starts which does all the work for putting the data in database reading them from the preformatted Excel sheets. But I want to add a button like "CANCEL" which when hit should follow the code written under it. The problem is that once the procedure is initiated under START , any hit to CANCEL is not responded. How can I bring this functionality into picture?



Waiting for your Response,

Priyadarshan.

The Guru's Response:

Hi Priyadarshan,

You have a classic case of thread blocking here, the thread that is putting the data in the database is preventing the code in the Cancel button from executing until after all of the code has executed.

Are you looping while you are reading in the data from the excel sheets??? If so, just place a DoEvents key word at the top of the loop and this will free the blocked thread to perform other tasks, including the code in your Cancel section.


BTW, it is usually a good idea to put a DoEvents key word in any loop that you have and it should also be the first instruction in a Form_Load event too.


I hope this helps

The VB Guru

Percentage ?

From:  Dave Briski
Date:  March 2002

Hi there,

I saw your cool site on VB tips and I had a question regarding Visual Basic and Access.

I'm trying to write a logon program that looks up usernames and passwords on an Access 2000 database to see if they match. I've read some stuff in my Visual Basic textbook that talks about doing this type of thing but I am still pretty unclear.

I know that in the form_load part you have to write the code that links the database to the program. then in the button that they click after typing in their username and password you have to write the code that actually checks it.

If you have time to write back and suggest anything that would be great.

I can send you the code for it too if you want to see what I'm doing.

Thanks alot!!!

Dave Briski VB student

 

The Guru's Response:

Hi Dave

If you have no Idea how to access a database, this can be a little confusing. First of all, read my tutorial on how to use UDL files, then use the following code:

Note: you will have to set a reference to the Microsoft ActiveX Data objects Library (try to pick the most current version, depending on the OS you have, you may have multiple listings from version 2.0 to 2.7)

Dim rs As ADODB.Recordset

Dim con As ADODB.Connection

Set rs = New ADODB.Recordset

Set con = New ADODB.Connection



con.Open "File Name=" & App.Path & "\MyUDL.udl"



With rs

.ActiveConnection = con

.CursorType = adOpenForwardOnly

.LockType = adLockReadOnly

.Open "select * from users where userid = " & txtUsername & " and Password = '" txtPassword "'"

End With

‘ If it was a SQL database, I would just check the rs.recordcount property, but because Access will not return this value, we have to manually check it.



If (Not rs.EOF) Or (Not rs.BOF) Then

‘ Success, the username and password is good

‘ Place code here for successful log in

Else

‘ Failure, the username and password are wrong

‘Place code here to notify the user wrong username or password

End If



rs.Close

con.Close

Set con = Nothing

Set rs = Nothing

I hope This Helps

PopUp Menu?

From:  Steve Broos

Date:  March 2002

Hay

Thank you very much for the quick reply !

I have something to add to your solution.
If you close the obj.DB object (adodb.connection) in the exec_SQL function you are not able to use the DB object (recordset) in the calling function. Th connection has to remain opened.


I have an other problem right now, and can't find an answer on the web :/

I use a custom made popup-menu.
If you rightclick in a readonly datagrid it opens a popupmenu with 2
options:
edit and delete.

I've got 2 problems with this:
- After my own routine ends, the standard popup-menu
(cut/copy/paste/...)
appears,
but I want it not to pop up.
- It works fine (except for the 2nd std popup) when you first click with the left mouse
button to select a row. If you only use right-click, the row you clicked on isn't selected.

If it's possible to select a whole row in the datagrid with the mousebuttonUP event, and then open the popup, this could be GREAT ! I think this can resolve the other problem, because when a whole row is selected, and you rightclick on it, the standard popup doesn't open. It opens only when ths cursor is in one single cell.

Do you have any idea how I can fix this ?


Best regards,
and thx again for the quick reply on my previous mail.

Steven

The Guru's Response:

Hi Steve,

No Problem Steve, what can I say, I love VB ;)

Before I answer your new question, I just wanted to mention the
following:

All though I did not show the code in the example, you do not necessarily have to keep a connection open for an RS to stay "Live", you can close the connection and still work on it, it is called a disconnected recordset. To use one, you do the following:

Dim Con As ADODB.Connection
ExecuteSQL as ADODB.Recordset

On Error GoTo ConnectionError

Set Con = CreateObject("ADODB.Connection")


With Con
.ConnectionString = "File Name=" & App.Path & "\" & DSN & ".UDL;" ' Note, I like to use UDL files for my Database connections, replace as necessary for how you normally connect to a database.
.CursorLocation = adUseClient
.ConnectionTimeout = 300
.Open
End With

Set ExecuteSQL = CreateObject("ADODB.Recordset")


With ExecuteSQL
.ActiveConnection = Con
.CursorLocation = adUseClient 'These next
three must not change for the disconnected RS to work properly
.CursorType = adOpenStatic ' Do Not
Change
.LockType = adLockBatchOptimistic ' Do Not
Change
.Open SQL
.ActiveConnection = Nothing
End With

Exit Function

By telling the connection and the RS that the cursorlocation is on the Client, not the server, the whole RS is sent down to the client from the database server, you then say that the LockType is BatchOptimistic, this allows you to make changes to the Recordset while in Memory and then do a Batch Update. Finally, buy setting the ActiveConnection to Nothing, the Recordset is truly Disconnected. If you want to update you database with the changes that you have made to the RecordSet in Memory, you would call the following code:

Dim Con As ADODB.Connection

On Error GoTo ConnectionError

Set Con = CreateObject("ADODB.Connection")


With Con
.ConnectionString = "File Name=" & App.Path & "\" & DSN & ".UDL;"
.CursorLocation = adUseClient
.ConnectionTimeout = 300
.Open
End With

With DisConnectedRS
.ActiveConnection = Con
.UpdateBatch
.ActiveConnection = Nothing
End With

Now, a Disconnected RS will not work with a database like Access because Access will always keep the Cursor on the server, no matter what you do.

On to you next question:

The standard way in VB to prevent a Windows Menu from appearing is to quickly disable the control, show your menu and then re-enable the control again. The controls Mouse_Down event will fire before the Windows Menu Event fires, so by disabling the control first, the Windows menu will never fire; in other words, your menu will supersede the Windows menu.

To test this, create a new project and place a text box on the form, next, create a test menu called mnuTest and place a few submenus below it. In the Text1_MouseDown() event, place the following code:

Private Sub Text1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

If Button = vbRightButton Then
Text1.Enabled = False
'If this were for your real project, place code here to highlight the row of data in your data grid HERE
PopupMenu mnuTest
Text1.Enabled = True
End If
End Sub
 

I hope this helps