Wrox Programmer Forums
|
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 25th, 2005, 04:36 AM
Authorized User
 
Join Date: Feb 2004
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Default Connection String

Hi
I am using my ADO connection on several forms like this

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    ' connect to database
    Set cn = New ADODB.Connection

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & DatabaseNavnPath & ";"
    ' open a recordset

    Set rs = New ADODB.Recordset
    Mysql = "SELECT Bla,Bla FROM BLABLA"
    Set rs = cn.Execute(Mysql)

What i would like to achieve is. Write as much as possible in a funtion. So the call would look somthing like.
    call Opencon
    Mysql = "SELECT Bla,Bla FROM BLABLA"
    Set rs = cn.Execute(Mysql)

Any help would be appreciated.

Cheers
Karsten




 
Old February 25th, 2005, 05:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I use a general function like this which returns a connection object, so you just need to do:
    Set cn = OpenConnection
    Mysql = "SELECT Bla,Bla FROM BLABLA"
    Set rs = cn.Execute(Mysql)

anyway, here's the function
Code:
Public Function OpenConnection() As ADODB.Connection
On Error GoTo OpenConnection_Fail

    Dim nRetryCount As Integer

    Set OpenConnection = CreateObject("ADODB.Connection")
    Debug.Assert Not OpenConnection Is Nothing

    With OpenConnection
        ' ### change the next line depending on where your connection string is stored ###
        .ConnectionString = GetConnectionString

        Do While .State <> adStateOpen And nRetryCount < 3
            .Open
            nRetryCount = nRetryCount + 1
        Loop

        If .State <> adStateOpen Then
            Err.Raise vbObjectError+1024, "OpenConnection", "Unable to connect to database using " & .ConnectionString
        End If
    End With

    Exit Function
OpenConnection_Fail:
    Err.Raise Err.Number, Err.Source, Err.Description
End Function
I also use this to close the conn and all associated objects - the following example also closes an associated command and stream object, but you can substitute recordset or whatever else you like:
Code:
Public Sub DropConnection(ByRef cn As ADODB.Connection, Optional ByRef cmd As ADODB.Command = Nothing, Optional ByRef strm As ADODB.Stream = Nothing)
'===========================================================
'   Description     : generic function to drop a connection and associated objects
'===========================================================
On Error GoTo DropConnection_Fail

    If Not strm Is Nothing Then
        If strm.State = adStateOpen Then
            strm.Close
        End If
        Set strm = Nothing
    End If

    If Not cmd Is Nothing Then
        Set cmd.ActiveConnection = Nothing
        Set cmd = Nothing
    End If

    If cn.State = adStateOpen Then
        cn.Close
    End If
    Set cn = Nothing

    Exit Sub
DropConnection_Fail:
    Exit Sub
End Sub
hth
Phil
 
Old February 26th, 2005, 03:14 AM
Authorized User
 
Join Date: Feb 2004
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Phil

Thanks.
It makes my code so much more nice to look at, and easier to maintain.

Cheers
Karsten






Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection string MunishBhatia ASP.NET 2.0 Professional 4 May 16th, 2007 05:50 AM
Connection String jmss66 Oracle 4 August 8th, 2006 03:19 AM
Connection String fs22 Classic ASP Databases 3 August 30th, 2004 01:25 AM
Connection string tlamazares SQL Server ASP 1 March 29th, 2004 05:16 PM
Connection String Help. diamond2165 BOOK: ASP.NET Website Programming Problem-Design-Solution 1 November 7th, 2003 06:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.