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
| 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
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

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.


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

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
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
            nRetryCount = nRetryCount + 1

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

    Exit Function
    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:
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
        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
    End If
    Set cn = Nothing

    Exit Sub
    Exit Sub
End Sub
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

Hi Phil

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


Reply With Quote

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 03:57 AM.

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