asked on

Need Help with Simple VB Questions

I am relativly new to VB. I have taken a beginner and intermediate class, however they were both a couple years ago. I am now writing a VB application to take test on the PC. This will be used for multiple users. I have a few basic questions that I forgot if are possible.

I have 3 forms with multiple subroutines on all of them. Almost all of these go out to an access database to retrieve data from the files. I use this code to open my connection in all of the subroutines:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Training Exams\Training.mdb;Mode=ReadWrite;Persist Security Info=False"
.CursorLocation = adUseClient
.Open
End With

' get data from files here

cn.Close
Set cn = Nothing

Question 1) Can I just open the connection once in the General declarations and have it active for all 3 forms and will this speed up the performance of the application?

Question 2) In the connection string cuurently I have the MODE=READWRITE, but I had locking troubles so I had switched the mode to READ but this made it very slow. Why is the READWRITE option faster?

Thanks in Advance - Chad

Arthur_Wood

1) yes, make the connection object GLOBAL (Public in a Module)

2) as to the specific question , I have no idea, but when you say "but I had locking troubles", what "troubles" did you encounter?

AW

CMULHERON

ASKER

I kept getting a run time error -2147467254 and it had multiple descriptions like "Could not lock file" and "The database has been placed in a state by user 'ADMIN' on PC 'ABC' that prevents it from being opened or locked."

I figured that by opening the connection as READ only that it wouldn't lock the database.

If I declare the connection as Public and only open it once, do the files get closed if I were to do a EXIT SUB and not set the recordset = Nothing. Or do I need to do the RS_TQ1.CLOSE and set to NOTHING myself before I EXIT SUB. Example below:

++++++++++++++++++++++++++++++++++++++++
Dim cn As ADODB.Connection
Dim rs_TQ1 As ADODB.Recordset
Dim CmdSql_TQ As New ADODB.Command

Set cn = New ADODB.Connection
Set rs_TQ1 = New ADODB.Recordset

With cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Training Exams\Training.mdb;Mode=ReadWrite;Persist Security Info=False"
.CursorLocation = adUseClient
.Open
End With

'Get the last question# of the version of the test
With rs_TQ1
.Filter = "TQTSTNUM = " & Tstnum_W & "
.Open "TSTQST", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
If ERROR OCCURS
EXIT SUB
END IF

End With
rs_TQ1.Close
Set rs_TQ1 = Nothing

cn.Close
Set cn = Nothing

end sub

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CMULHERON

ASKER

That was Psuedo code in my last post. Sorry. I got my connection to be global and I added so error trapping as you suggested. I have not gotten the locking error again and the speed of the application seems to be faster by only having 1 connection. Thanks for All your help and I look forward to any future answers you may be able to help me with.

Arthur_Wood

glad to be of assistance.

AW