Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Multiple Databases

    I try to call one database forms and tables into another database without creating tables and forms.


    Database where (target) i need to open forms and tables:
    insurance.mdb (In the same database I click on TOOLS - REFERENCES and add accountreceivable.mdb as i need all forms and table from that database)


    Database from where (source) i need forms and tables
    accountreceivable.mdb


    I try to call forms and tables from accountreceivable.mdb into insurance.mdb. Do i use the below function in insurance database to open tables and forms from 2nd database. If i do where should i write this function i mean in the module of insurance database or in the module of accountreceivable database. How to i call that function

    Function OpenForm(byref vformname as string) as boolean
    docmd.open vformname,,,,,acdialog


    OpenForm = true

    end function


    Thank You.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The only things you can link to in another database (someone correct me if I am wrong) are tables and modules.

    The code to open the form will have to be in the database where the form resides. I have created a generic progress meter form that is a database with other utility programs (string functions, etc.).

    Another suggestion is when you are writing code in the 2nd database use either CodeProject or CodeDb instead of CurrentProject or CurrenDb. That will help you access the tables in the 2nd database if they are not linkked in the insurance db.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Manipulating data in another Database is relatively simple but opening a Form in another DB is a little more involved. For example, you can run a query to retrieve data from another DB by simply doing this:

    Code:
    Dim SQLstrg As String
    Dim RemoteDB_Path As String
     
    RemoteDB_Path = "C:\Microsoft Office\Office10\Samples\Northwind.mdb"
     
    If DoesFileExist(RemoteDB_Path) = False Then
       MsgBox"Can Not Find Remote Database File", vbCritical, "Remote Not Found"
    Else
       SQLstrg = "SELECT * FROM [Orders] IN '" & RemoteDB_Path & "';"
       CurrentDB.Execute SQLstrg  ' or where-ever you want to use the query.
    End If
     
    ' Place the following function into a Database code module.
    Public Function DoesFileExist(PathStrg As String) As Boolean
        Dim a$
        On Error Resume Next
        a$ = Dir(PathStrg, 14)  ' Locate regardless of File Attribute (H-S-RO-A).
        If a$ <> "" And Err = 0 Then DoesFileExist = True Else Err.Clear
    End Function
    On the same token, you can also insert data into a remote Database table by doing this:

    Code:
    Dim SQLstrg As String
    Dim RemoteDB_Path As String
     
    RemoteDB_Path = "C:\Microsoft Office\Office10\Samples\Northwind.mdb"
     
    If DoesFileExist(RemoteDB_Path) = False Then
       MsgBox"Can Not Find Remote Database File", vbCritical, "Remote Not Found"
    Else
       SQLstrg = "INSERT INTO [Orders] IN """ & RemoteDB_Path "" " & _
                     "SELECT * FROM [yourLocalDBTableName] WHERE " & _
                     "[yourDBTableFieldName]='" & WhateverValue & "';"
       CurrentDB.Execute SQLstrg
    End If
     
     
    ' Place the following function into a Database code module.
    ' (If you havn't already done so)
    Public Function DoesFileExist(PathStrg As String) As Boolean
        Dim a$
        On Error Resume Next
        a$ = Dir(PathStrg, 14) ' Locate regardless of File Attribute (H-S-RO-A).
        If a$ <> "" And Err = 0 Then DoesFileExist = True Else Err.Clear
    End Function
    In essence, you really don't need to even Link tables if you consistently use this method but keep in mind that linking is much easier (for most) and somewhat faster in performance especially when dealing with large amounts of data especialy with regards to Deleting rows or Inserting rows. Appending or Grouping (GROUP BY) shouldn't pose to much of a difference. Obviously, the more complex the query, the longer it takes to accomplish. For small quantities of records (under 100) you wouldn't notice any real speed degradation. Then again....that's my opinion.

    The above information may be enough to accomplish what you are trying to do but if you're still bent on displaying a Form within a remote Database then I suggest you try the following code from Dev Ashish. You can view it from this link. It was originally written for Access97 but should work for 2K+.


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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