Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 25th, 2005, 04:36 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Denmark.
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




Reply With Quote
  #2 (permalink)  
Old February 25th, 2005, 05:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
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
Reply With Quote
  #3 (permalink)  
Old February 26th, 2005, 03:14 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Denmark.
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.