Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Dec 2007
    Posts
    26

    Unanswered: Database Connection from a call function

    How can I have the information below as a call function from a module i.e.

    Public (or Global )Function inc_connection ()
    Dim db As Database
    Dim ws As Workspace
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase("C:\folder1\customers.mdb", False, False)

    End function

    Then in the form a

    Private Sub

    Dim sql as string

    Call inc_connetion
    the first 2 lines are ok but when it gets to the set information, it then comes up with error object required

    Dim sql as string
    Sql = select * from customers
    Etc
    End sub

    Hope that all makes sense

    Casey

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you want to use public constants instead?
    Code:
    Public Const ws As Workspace = DBEngine.Workspaces(0)
    Try explain what you're trying to achieve a wee bit better and you might find a better method than the one you're trying
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    26
    Thanks for yor reply Georgev

    I will try to explain this way & keep in mind i am self taught and dont know the correct naming of many things so I shall just try by the following example.

    a example call function that I might use ie

    Function xyz ()
    me.fname = ""
    me. lname = ""
    me.contact date = ""
    me.information = ""
    End Function

    so i would just have one line ie Call xyz()
    instead of
    me.fname = ""
    me. lname = ""
    me.contact date = ""
    me.information = ""

    now how can I use the same way to call a connection to the back end database without typing

    Dim db As Database
    Dim ws As Workspace
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase("C:\folder1\customers.mdb", false, False)



    I would only need one line for each of the two call functions ie
    Private Sub
    call openconnection() 'connection open
    call xyz () ' fields cleared

    then my sql statement and other relevant information

    call closeconnection() ' close the connection
    End Sub

    hope this makes sense

    Casey

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh I see!
    You want to make public functions for opening and closing a connection.

    Why don't you write out the procedure in full once and post it back up here so I can take a look-e
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Posts
    26
    Georgev thanks for taking an interest. this is the actual code in the database the open and close connection lines are colored dark red

    Private Sub Form_Activate()
    Dim rs As Recordset
    Dim SQLA As String
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase("C:\custbe\custtbl.mdb", False, False)

    Me.Customerlist.RowSource = ""

    SQL = "SELECT customers.custid,customers.cust_LName,customers.cu st_FName,customers.Cust_Address1 FROM customers ORDER BY customers.CustID ASC" 'WHERE customers.pro_CustID = GetCustomerID();"

    Set rs = db.OpenRecordset(SQL)

    rs.MoveFirst
    Do Until rs.EOF
    Me!Customerlist.AddItem (rs.Fields("custID") & ";" & rs.Fields("cust_Lname") & ";" & rs.Fields("cust_Fname") & ";" & rs.Fields("cust_address1")) '= rs(1) ' & " " & rs(2) & " " & rs(3) 'Adds lastnames to dropdown list"
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    End Sub

    casey

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Public Function fillMyListBox(formName As String, listboxName As String, sqlStatement As String, numberOfColumns As Integer)
    
    On Error GoTo ErrorHandler
    
    Dim rs As Object
    Dim i As Integer
    Dim itemToAdd As String
    Dim strConn As String
    
        If Forms(formName).Controls(listboxName).ColumnCount <> numberOfColumns Then
            MsgBox "Wrong number of columns specified"
            Exit Function
        End If
    
        strConn = <enter conection string>
            
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open sqlStatement, strConn, adOpenStatic, adLockReadOnly, adCmdUnspecified
    
        Do While Not rs.EOF
            itemToAdd = ""
        
            For i = 0 To numberOfColumns - 1
                itemToAdd = itemToAdd & rs.Fields(i).Value & ";"
            Next i
            
            itemToAdd = Left(itemToAdd, Len(itemToAdd) - 1)
            
            Forms(formName).Controls(listboxName).AddItem itemToAdd
        Loop
    
        rs.Close
        Set rs = Nothing
    
        Exit Function
    
    ErrorHandler:
        If Not rs Is Nothing Then
            If rs.State = adStateOpen Then rs.Close
        End If
        Set rs = Nothing
        
        If Err <> 0 Then
            MsgBox Err.Number & Chr(9) & Err.Description
        End If
    
    End Function
    George
    Home | Blog

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Casey, I do that all the time. I have a public constant for the connection string and a public variable for the connection object. I have public functions for establishing and closing the connection, so the relevant bits of a recordset operation would look like:

    Code:
      EstablishConnection
    
      Set rst = New ADODB.Recordset
      rst.ActiveConnection = objConn
    
      rst.Open strSQL
    
    ExitHandler:
      Set rst = Nothing
      ReleaseConnection
    Paul

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Paul: that's ADO mumbo-jumbo.
    casey.t is in DAO

    so sorry it is time for food/wine/TV here: will look again in the morning and try to help if nobody else fixed it.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you look at my last post... All you have to do is pass the procedure a few values and it'll fill your listbox for you. Heck you could even pass in the connection string!

    As with Pauls suggestion - global constants will be your friend to!
    George
    Home | Blog

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Izy, in post 3 he asked about that specific thing, and in another thread he posted ADO code and asked the same question, so I addressed it.
    Paul

  11. #11
    Join Date
    Dec 2007
    Posts
    26
    George
    It is the “pass the procedure a few values”
    strConn = <enter conection string>
    that im trying to work out. Where do you keep the code for the connection string.

    Paul
    The other thread that I posted into was not the DAO or ADO but to work out how to pass the connection information to the current procedure.

    1: Where do you keep the information for establishConnection
    2: What would the code look like, and how do I pass it to the current procedure


    Izy
    I hope you enjoyed your food/wine/TV
    I do not mind learning both ADO & DAO If you post both examples then I will be all the wiser and can decide which is the best way to go for this database.

    I just want to know how to create the necessary information for the connection string, and to pass that connection string to the current procedure

    In my 3rd post the code for my connection is highlighted in red

    Hope some of this makes sense

    Thank guys for your help

    Casey

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a brand new, empty, shiny module and write the following
    Code:
    Public Const connServer   As String = "<server name>"
    Public Const connDatabase As String = "<database name>"
    
    'Optional parameters
    Public Const connUsername As String = "<username>"
    Public Const connPassword As String = "<password>"
    
    'Connection String with password/username
    Public Const connectionStringWithCredentials As String = "Driver={SQL Server};Server=" & connServer & ";Database=" & connDatabase & ";Uid=" & connUsername & ";Pwd=" & connPassword & ";"
    
    'Connection string without credentials
    Public Const connectionStringWithoutCredentials As String = "Driver={SQL Server};Server=" & connServer
    Note how these are declared as public constants... This means that they are available to use in any procedure throughout your project and are read-only!

    More information on connection strings can be found at www.connectionstrings.com
    George
    Home | Blog

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I do it a little differently than George. On top of my code above, which would be where you're using the connection, you'd have this in a standard module.

    Code:
    Public objConn As ADODB.Connection
    Public Const dbConnectionString As String = "Provider=SQLOLEDB;Data Source=10.1.1.7;" & _
                                                " Database=db;UID=user;PWD=pw"
    
    Public Function EstablishConnection()
      On Error GoTo Err_Handler
    
      Set objConn = New ADODB.Connection
      objConn.ConnectionString = dbConnectionString
      objConn.Open
    
    Done:
      Exit Function
    
    Err_Handler:
      Select Case Err
        Case -2147467259    'not connected
          MsgBox "Not connected to network"
          DoCmd.Quit
        Case Else
          MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
          Resume Done
      End Select
    
    End Function
    
    Public Function ReleaseConnection()
    On Error GoTo Err_Handler
    
    objConn.Close
    Set objConn = Nothing
    
    Done:
    Exit Function
    
    Err_Handler:
    MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
    Resume Done
    
    End Function
    Last edited by pbaldy; 01-15-08 at 13:04.
    Paul

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe, I never said I implement it the way shown above... Personally I don't want to disconnect my opening and closing of objects/recordsets; so if I were to generalise things, I'd do it in one call.

    Oh, and I chose early binding because I'm too lazy to add any references
    George
    Home | Blog

  15. #15
    Join Date
    Dec 2007
    Posts
    26
    Paul

    This sample is based on your post with exception that it's a ms access backend. what am I doing wrong ?

    place it into c:\folder1

    I want to be able to call the establishconnection function

    casey
    Attached Files Attached Files
    Last edited by casey.t; 01-21-08 at 05:16.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •