Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have a procedure that calls a sub in another db. When it runs I don't see any instance of Access, nothing 'flashes'. I have never set a reference to an external db with the VBA References tool.

    This code calls the external procedure:
    Dim a As New Access.Application
    Set a = CreateObject("Access.Application")
    a.Visible = False


    a.OpenCurrentDatabase "\\dotatufs02\GIS\Airports.mdb", False
    a.Run "GetPaverData"
    Set a = Nothing
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think we may be talking about different things. It's one thing to open another database using vba and do something in that database or with a procedure in that database.

    I have a routines where I have a list of databases, and go out to each to get things like linked tables or tables/fields or modules/procs.

    Here's part of one (opens many other databases in a loop)
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : Multi_MDBs
    ' Author    : user
    ' Date      : 3/30/2008
    ' Purpose   : Used to identify linked tables in a list of databases.
    '---------------------------------------------------------------------------------------
    '
    Sub Multi_MDBs()
    Dim ThisDB As DAO.Database
    Dim otherDB As DAO.Database
    Dim strDbPath As String
    Dim RS As Recordset
    Dim rz As Recordset
    
    Dim tdf As TableDefs
    Dim i As Integer
    Dim MyBEPath As String
    Dim strMsg As String
    Dim sResponse As Variant
       On Error GoTo Multi_MDBs_Error
    
    On Error Resume Next
    Dim tbl As TableDef
       On Error Resume Next
     
    Set ThisDB = CurrentDb
    
    Set RS = ThisDB.OpenRecordset("Select fullMDBCoord from A2Kmdbs")
    Open "c:\jack\LinkedTbls.log" For Append As #1
    Do While Not RS.EOF
    blnTitlePrinted = False
    
        Debug.Print Now & " - Processing  " & RS!fullmdbcoord
       ' If rs!fullmdbcoord <> "D:\A2K\db1_Backup.mdb" Then
       ' GoTo MoveIt
       ' End If
        strDbPath = RS!fullmdbcoord
       Set otherDB = OpenDatabase(strDbPath)
       Set tdf = otherDB.TableDefs
       Debug.Print "Checking tables in "; otherDB.name & vbTab & Now()
    For i = 0 To tdf.Count - 1
    MyBEPath = Mid(tdf(i).Connect, 10)
    Debug.Print tdf(i).name
    
    'Revised code to output to a file:
    If MyBEPath <> "" Then
      If blnTitlePrinted = False Then
      
    Print #1, vbCrLf & vbCrLf & "Linked Tables in "; otherDB.name & vbTab & Now() & "   JD  " & vbCrLf & "=============================" & vbCrLf & vbTab & "        Table" & vbTab & vbTab & vbTab & "Linked to" & vbCrLf
    blnTitlePrinted = True
      End If
    Print #1, tdf(i).name & vbTab & " ------>>" & vbTab & MyBEPath
    End If
    Next i
    Get_out:
    
       otherDB.Close
        Set otherDB = Nothing
    MoveIt:
    RS.MoveNext
    Loop
    ....
    .....
    But I think the poster was asking about a library of shared procedures.
    Suppose you have a lot of functions that could be used in many contexts.
    In the old days with other languages this was a common subroutine library or something similar, or an api.

    Many of us have countless databases from over the course of various assignments.
    If you want to use the procedures/functions in these databases, you don't have to copy the procedures to the current database. and
    you don't have to programmatically open the database with the procedure to use it.
    You put your favorite, general use (even special circumstances - math, text manipulations...) functions into a library database
    and just reference that library-- and all functions and procedures are readily available.

  3. #18
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ahhh, yes, makes sense. Never faced that requirement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #19
    haggis999 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Nov 2012
    Location
    Southampton, UK
    Posts
    60
    Quote Originally Posted by June7 View Post
    I have a procedure that calls a sub in another db. When it runs I don't see any instance of Access, nothing 'flashes'. I have never set a reference to an external db with the VBA References tool.

    This code calls the external procedure:
    Dim a As New Access.Application
    Set a = CreateObject("Access.Application")
    a.Visible = False
    a.OpenCurrentDatabase "\\dotatufs02\GIS\Airports.mdb", False
    a.Run "GetPaverData"
    Set a = Nothing
    I have not tried it out, but I think your code sample contains a good reason why you see no instance of Access, i.e the line a.visible = False

    David

  5. #20
    haggis999 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Nov 2012
    Location
    Southampton, UK
    Posts
    60
    Of the two methods that have been discussed, I think that setting up a reference to an external library database is the easiest to use. You only have to set the reference once and then, as Orange says, the contents of that library can be instantly referenced with no surrounding additional code. It provides a very elegant solution.

    I guess that there may be other situations where June7's code would be more appropriate. I didn't know before how to open another database in that manner, so thanks for expanding my knowledge on that point.

    Thanks to both of you for helping me to get my brain around this issue.

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think I have downloaded dbs from posters that included a reference like that and of course I get a 'missing library' error. Most of the time I can still run the db enough to analyse issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating 'Common' VBA code
    By Nevsky78 in forum Programming
    Replies: 4
    Last Post: 06-18-2012, 02:07 AM
  2. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  3. common form fields
    By soulice in forum Forms
    Replies: 9
    Last Post: 04-05-2012, 02:58 PM
  4. Replies: 1
    Last Post: 06-09-2011, 10:12 PM
  5. Replies: 4
    Last Post: 05-16-2011, 04:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums