?
Solved

Need Help with Simple VB Questions

Posted on 2003-03-19
5
Medium Priority
?
159 Views
Last Modified: 2010-05-01
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
0
Comment
Question by:CMULHERON
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8168067
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
0
 

Author Comment

by:CMULHERON
ID: 8168283
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
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 80 total points
ID: 8170422
rather than using the Mode on the Connection, you can control the Locking on the .Open call, as you have tried to do.

If ERROR OCCURS
     EXIT SUB
  END IF


This is not corrct code (I guess you were writing PSUEDO-CODE here)  What you should try is like this:

With rs_TQ1
   .Filter = "TQTSTNUM = " & Tstnum_W & "
On Error Resume Next
   .Open "TSTQST", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
  If Err.Number <> 0 then
     MsgBox "Error Number :" & Err.Number & vbcrlf & "Description: " & Err.Description, vbOkOnly
     EXIT SUB
  END IF

End With

That will then trap any error that might occur on the .Open method call, and then display the error number and description, if an error occurs.

AW
0
 

Author Comment

by:CMULHERON
ID: 8174888
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8175528
glad to be of assistance.

AW
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question