Results 1 to 3 of 3
  1. #1
    PATRICK is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    32

    Keep Connection String open to re-use in the same form.

    Im using a connection string to connect in my MySQL Server, I was wondering how I could open the connection when the form open the use thos connection in different command/button...

    lets say I have this in my OpenForm:

    Private Sub Form_Open(Cancel As Integer)



    Dim MyConn As ADODB.Connection
    Dim sqlstr As String
    Set MyConn = New ADODB.Connection
    Dim MyRecSet As ADODB.Recordset
    With MyConn
    .Open ConnectString() // this call my string connection with information of the server...
    End With

    If MyConn.State = adStateOpen Then

    msgbox "got connected."
    Else
    MsgBox "connection not established, Please try again", vbCritical, "ALIMS System"

    End If

    Exit Sub

    ==== now how could I use this connection in a click event? because the connection is open, the way I have here I placed the same call connection in all the events, I don't think it's right once they are in the same form.


    Thanks in advance...

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Why do you need to keep the connection open? Are you accessing the back end to populate a data entry form (an unbound form)? If so keeping that connection open isn't necessarily your best option. If you are using a data entry form and you are doing something like cycling through records on an unbound form it *may* be more efficient to keep the dataset open but (I have never tried it) you may have to use a public function to open the connection and a public function to close it (when for example you close the data entry form). I wouldn't attempt to keep it open unless you have a very good reason for doing so.

  3. #3
    PATRICK is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    32
    well maybe I wont need it, I was just thinking... I used to have linked table from MySQL Server, so when I first start the app it was refreshing the links and then I had all the tables just as local ones, could insert, select without calling again, then when I changed to connection string I thought maybe in a way in the form, open the connection alone and do what I need to do then when the form is closed, the connection will be closed. but I guess you're right, calling this way each moment I create a connection and after it's done the connection is closed and that's it.

    Thanks for reply.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Connect System DSN using connection string
    By jbailey4545 in forum Access
    Replies: 0
    Last Post: 11-08-2013, 01:29 PM
  2. Specifiying a connection string in ms access forms
    By thebionicredneck2003 in forum Access
    Replies: 4
    Last Post: 05-09-2013, 04:19 PM
  3. Replies: 1
    Last Post: 04-19-2012, 12:43 PM
  4. Create dynamic connection string to Access
    By janwane in forum Access
    Replies: 2
    Last Post: 10-07-2011, 06:56 AM
  5. Connection String
    By cradaa in forum Access
    Replies: 4
    Last Post: 05-24-2011, 07:28 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums