ADO connection string the best way?

[PinoyDev]

Good day!

I just want to ask on what is the best ADO connection string to connect to MS Access fast? and what is the fastest way to create it? And also, where in the project to best put it so that all forms in the project will use it without closing the connection. I also need to create a recordset from it..

Thank you!

[BitBlt]

Not sure exactly what you're asking for here. "Best" is sort of context-dependent.

If you just want a common connection string, make a module-level or global variable to hold the value you want. Usually, it is of the form:

    Global Const ConnectionString = "provider = microsoft.jet.oledb.4.0;data source = c:\working\db1.mdb"

where you change the filename to match your file. If that's all you need, stop reading now...the rest of the reply gets a little more industrial-strength.

Okay, you're still reading. Don't say I didn't warn you.

If you want to use the same connection string in different data-bound controls in different parts of your app, use the global variable to supply the string; then when you open a form, populate the connection property of the control in the form_load event.

If you want to use your connection string to open a recordset object in a separate module, share that recordset object globally and never close it, you open yourself up for all kinds of potential data consistency errors in your app. And, if for some reason your connection is severed (either through the app's fault, a BSOD or a power outage) it can lead to data corruption. Personally I recommend you not do it this way.

Also, if you plan this to be pointing to a shared database (as in a network), you leave yourself all kinds of possibilities to lock the database so that you can't do maintenance or upgrades. This is a real pain in the neck in a business environment.

As you can see, there are lots of ins-and-outs to this. Frankly, you're better off keeping your data requirements on a form-by-form basis, and connect/disconnect for each form when you need to.

After all that, I'm not sure if I've fulfilled your request. At least I hope I've given you some ideas. Good luck!

[Bile]

-->>No additions may be if blocker needs more explanation...

[PinoyDev]

Im just developing a single pc local app. Im planning to use single recordset variable only. I just want to open & close it everytime it is use.

I have this sample recordset. Which part should I put the connection string? and the recordset type?

    'In Module(Mod_Con)
    'Please correct my code if im wrong!

    Global Const ConnectionString = "provider = microsoft.jet.oledb.4.0;data source = c:\working\db1.mdb"
    Global rs as new adodb.recordset
    if rs.state=adstateopen then rs.close 'Is this necessary?

    'where to put the connection string in this recordset and what type of recordset it should be?
    set rs="select * from tbl_employee e" _
            "inner join tbl_dtr dtr" _
            "on dtr.empID=e.empID order by e.empID asc"

    while not rs.eof
        msgbox rs.fields("EmployeName").value
        rs.movenext
    wend

    rs.close
    set rs=nothing

Thank you!

[Bile]

-->>I think we should make things a lil bit clear here...
-->>1.On your project open a Module name it whatever you want.
-->>2.Take these codes and paste them to your module

Option Explicit

'*************************************************************************************'
' THESE ARE THE VARIABLES THAT MAY BE CALLED AND USED ANY WHEREWITHIN AN APPLICATION *'
'*************************************************************************************'

Global Conn As New ADODB.Connection
Global RS As New ADODB.Recordset

'****************************************************************'
'*      BELLOW IS THE CODE TO OPEN THE DATABASE CONNECTION.     *'
'****************************************************************'
Public Sub dbConnect()
  Set Conn = New ADODB.Connection
  Conn.ConnectionString = strConn
  Conn.Open
End Sub

'*****************************************************************'
'*      BELLOW IS THE CODE TO GET DATABASE CONNECTION PATH.      *'
'*****************************************************************'
Public Function strConn() As String
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\Database\YOUR DATABASE.mdb" & ";Persist Security Info=False"
End Function

-->>3.Now whenever you want to connect to your database you just call the procedure dbConnect
-->>4.To Openn any Record set may be I have a Form that connects to my Recordset tblEmployee to Retrieve data on My Button cmdView(Control Name)=View(Caption) I will code this or paste this

Private Sub cmdView_Click()
Dim SQL As String
Dim Count As Interger

Call dbConnect
SQL = "SELECT * FROM tblEmployee WHERE ((tblEmployee.LB_No) LIKE '" & Me.txtLB_No.Text & "');"
RS.Open SQL, Conn, adOpenDynamic

While Not RS.EOF
        With Your_ListView.ListItems.Add(, , RS.Fields("LB_No"))
            With .ListSubItems
                .Add , , RS.Fields("1st_DBFiels")
                .Add , , RS.Fields("2nd_DBFiels")
                .Add , , RS.Fields("3rd_DBFiels")

                Count = Count + 1

            End With
        End With
        RS.MoveNext
    Wend

    Count = 0
   RS.Close
   Conn.Close
   Set Conn = Nothing

End Sub

-->>5.Try it to See if its fine with you...

[PinoyDev]

Thank you Bile!

Can I make it something this way:

In module "Mod_Con"
Global Const UsedConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\Database\YOUR DATABASE.mdb" & ";Persist Security Info=False"
Global Conn As New ADODB.Connection
Global rs as new adodb.recordset
Global SQL as String

Public Sub dbConnect()
  Set Conn = New ADODB.Connection
  Conn.ConnectionString = UsedConnectionString
  Conn.Open

  if rs.state=adstateopen then rs.close
End Sub

'in Form
Private sub Form_Load()
call dbConnect 'Do I need to call this on every form form_load event? Is there a way to load this only in the module?
end sub

'Form buttonview
Private sub buttonview_click()
SQL = "SELECT * FROM tblEmployee WHERE ((tblEmployee.LB_No) LIKE '" & Me.txtLB_No.Text & "');"
RS.Open SQL, Conn, adOpenDynamic
While Not rs.EOF
print rs.fields("EmpName").value
rs.movenext
wend

rs.close
set rs=nothing

Conn.close
Set Conn=nothing
end sub

And also, can I use Public instead of Global? what is the difference?

Thank you!

[Bile]

-->>On line 17 of your code,No need at all to call the Procedure during form load as You can see I only call the procedure or Functions within the Area I need it to execute example on my Post I called it in a cmdView line 5..,in module you just define your Sub routeens and functions I'm not so sure if you can Load it in there unless its another Function or Procedure to be called later.
-->>Well..Global and Public? Means the same thing to me as SOMETHING which is GLOBAL its NOT of PRIVATE,and as well SOMETHING which is PUBLIC its NOT of PERSONAL USE(PRIVATE)
-->>Mathematical Proof Let G=Global,P=Public,!=NOT and PR=Private then
-->>G(!PR)=P(!PR)
-->>I hope your good at Math
-->>But in case Im wrong I do wait for other posts too...
-->>Tanx

[AndreRet]

Global was used in vb2/3, Public was used from vb4 onwards. It is the same as global, rather use Public throughout. Public replaced Global as Private replaced Dim...

[Bile]

-->>Tanx Andre,its clear for me as well...

[PinoyDev]

thank you AndreRet, Bile!

[Bile]

-->>Yeah a lot to Andre...

[AndreRet]

Thank you Bile. Only a pleasure Blocker. Please mark the thread as solved, thanx.

[AndreRet]

Sorry, my bad, see it was marked solved, thanx