Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97

    Question Unanswered: One front end with multiple backend databases?

    Hi!...

    Is there any way to do this with Access 97?...

    Problem: I'm working on an application which I want to use for same structure databases for more than 1 fiscal year. like I've 2 or more .mdb files (exactly same structure) and data for say year 2003-04 and 2004-05 in them. I want to make one front end .mde and want to call these .mdbs from the front end .mde depending on user's choice which year he/she wants to work with. I've created the application with 2 mdbs (backend) and 1 .mde with front end interface. In the front end mde I'm dynamically opening the database, creating its links and then removing links when the user finishes work or if he/she selects the different year. Its seems to work but what is happening is that during the session with one year (for which year the tables are linked) it some times doesn't bring data from these tables and/or mixes up the data from other years database whos links have been removed already.

    What I'm doing wrong or what may be causing this? any one can help?

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    Perhaps the problem is, When you change the link from one DB to another, the record sets are not cleared. So try to open the record sets as fresh record sets so that they don't hold the data of the previous selection.
    Regards,

    Rushi

  3. #3
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Smile Refresh Links

    Hi again Saqib.

    Try to add a button to your frontend application and use the following code:


    Dim strFilter As String
    Dim strInputFileName As String

    strFilter = ahtAddFilterItem(strFilter, "Databases (*.MDB)", "*.MDB")
    strInputFileName = ahtCommonFileOpenSave( _
    Filter:=strFilter, OpenFile:=True, _
    DialogTitle:="Please select database...", _
    Flags:=ahtOFN_HIDEREADONLY)
    fRefreshLinks (strInputFileName)

  4. #4
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97

    Re: Refresh Links

    Originally posted by vestigo
    Hi again Saqib.

    Try to add a button to your frontend application and use the following code:


    Dim strFilter As String
    Dim strInputFileName As String

    strFilter = ahtAddFilterItem(strFilter, "Databases (*.MDB)", "*.MDB")
    strInputFileName = ahtCommonFileOpenSave( _
    Filter:=strFilter, OpenFile:=True, _
    DialogTitle:="Please select database...", _
    Flags:=ahtOFN_HIDEREADONLY)
    fRefreshLinks (strInputFileName)
    I've similar routine already coded for my appliation but refreshing links over the network with multiple users already logged in and working it is sometimes terribly slow and time cosuming. But if that is the cause and it is nesessary to do I'll try and check it, but my point is when at the end before closing the front end application I've removed all the links and I re-create them again for the year selected, this shouldn't be necessary or is it? specially when you've to switch back and forth many time from one year to other?
    Last edited by Saqib; 01-29-04 at 08:07.

  5. #5
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    This may be a silly question but where is the FE located ??

  6. #6
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Angry

    This may even be a sillier question, what do you mean by "FE" ?

  7. #7
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Sorry FE - Front End

  8. #8
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Originally posted by Funkster
    Sorry FE - Front End
    the Front End Database is on the Server's Share Folder and Back End Dababases are also on different Shared Folder the other Server.

  9. #9
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    I think this may be your problem. If possible and practical you will need to move the FE to the local pc as the multiple users are causing a data conflict.

  10. #10
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Originally posted by Funkster
    I think this may be your problem. If possible and practical you will need to move the FE to the local pc as the multiple users are causing a data conflict.
    May be If I restate my question as follows:

    is there a way in access to dynamically link/unlink backend databases while running an application. and how?
    OR
    is there a way in access to dynamically open/close backend databases while running an application. and how?

    - regards,
    Saqib

  11. #11
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Actually what you are doing is fine its just that multiple users are accessing your Front End at the same time and this is causing a conflict.

    For example if user 1 selects table 2002 the links are created and the user can access the data. User 2 comes along and also requires table 2002. This may work correctly but if user 2 then decides to select table 2003 are the links for table 2002 deleted whilst user 1 is still working with the table.

    This is where I feel the conflict occurs. How does the database know which table which user is working on.

    I hope I havn't completely missed the point.

  12. #12
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    I agree with Funkster, install the Front End at the local PC. Then you'r also able to work with temporary tables etc.
    And better, your application will start faster.

  13. #13
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    If you cannot put the front end on the user's local machine, I recommend that you put the front end and all the back ends in the same location on the server, the same directory and subdirectory. Depending on your server, but this will probably help performance.

    Using multiple back ends should not be a problem. As far as dynamically linking to a different back end, I don't know.

    Regards,
    Mark

Posting Permissions

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