Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002

    Unanswered: Share ODBC Connections

    I am DB2/Oracle person working on Access problem and need help understanding how ODBC connections are shared between modules and/or forms.

    I've got Access 2000 and an ODBC connection to an AIX/DB2 v7.2 database as so.

    ' Create ODBCDirect Workspace object.
    Set wrkODBC = CreateWorkspace("myWorkspace", "Admin", "", dbUseODBC)

    ' Open Connection object using supplied information in
    ' the connect string.

    Set conOIG = wrkODBC.OpenConnection("Connection1", dbDriverComplete, False, _

    This connection is successful, but I can not use the database variable dbsOIG in other modules. When I received the code ( I didn't write it), the DIM statement for dbsOIG was in this function and I concluded that the scope of the variable was stopping me from using dbsOIG outside of the current function.

    Well, I moved the DIM statement to the top of the module and changed it to a GLOBAL definition as so.

    Global dbsOIG As Database

    I thought I would be able to use the dbsOIG database definition in another module but can not. I receive the error.

    Runtime error 91
    Object variable or With Block variable not set.

    so it still doesn't recognize my GLOBAL dbsOIG database defintion.

    What am I doing wrong here?


  2. #2
    Join Date
    Aug 2002
    Charlotte NC
    You may need to create a Global_Moduel, which is nothing more than a moduel that has the varialbes set in them. I also beleve that you will need to use the key word public instead of Global IE
    Public odbc_connection as database. This may be why its not working if you are coding in a moduel now so try that first and if that dosent work then but the variable in its own moduel

  3. #3
    Join Date
    Nov 2002
    San Francisco
    I declare variable A out of any procedure..... then I have Public Procedure and stick somethign into A and then Private Procedure which runs public procedure (can be stored anywhere in MDB file) and puts my variable into A. The difference between Public and Private Procedure is only in the score from where you can call it. Public Sub/Function can be called from anywhere, Private sub/Function only from it's Module/Form/Report - it saves some memory.... and sometimes lot of pain! The same is for variables.... if you put it INTO function/sub then it works only in that sub. If you put it in the OUT of sub as DIM or CONST then it works for all funvtions in the module, if you use word PUBLIC, then it works for all modules...

    two small recommendations:
    use OPTION EXPLICIT and declare all variables...
    keep all PULBIC varibles and constants in one module (it's easier to find it)

    Public Const consB = "This is test too."
    Public strA As String

    Public Sub PutItIntoA()
    strA = " This is a test."
    End Sub

    Private Sub ReadItFromA()
    'this SUB can be in different module
    MsgBox strA & " " & consB
    End Sub

Posting Permissions

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