Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

front end/back end

Discussion in 'Information Technology' started by Kate, Jul 28, 2006.

  1. Kate

    Kate
    Expand Collapse
    Guest

    Hello,
    I am in the process of attempting to split my database.
    I used the wizard, and came up with the error "subscript out of range.
    Invalid procedure call or argument". However, it did seem to have
    created a backend, so i continued.

    The backend had the tables. Nice.

    Was it supposed to have created a front end as well, or is the front
    end just the bit I started with? The bit I started with still had the
    tables in it, so I deleted the tables, and used "get external data,
    link tables" to link my 'front end' with the back end, then reset the
    relationships.

    Now in the bit which i think is hte front end, you can still see the
    tables. (They have arrow thingys next to them to show they are linked).
    Is this what is supposed to have happened?

    Now do I just have to send a front end to each of my users? Then, in
    my copy of the front end, I can make design changes at my leisure, and
    when I'm done resend these copies to the users? (I have yet to figure
    out the 'how to' in automating this bit)

    Thanks
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    It sounds like you have the right result: a back end containing only the
    tables, and the front end with the linked tables (indicated by the arrow on
    the table icon in the Database window.)

    The relationships exist in the back end. You don't have to (and actually
    cannot) create those relationships in the front end, though you may need to
    organize the layout of the relationships window again.

    Before you send the front end to other users, make sure you put the back end
    in a folder that they all have write-access to. If the path to this folder
    is different on other computers, they will need to reconnect to the
    different path the first time they open your database. They can do this
    through the Linked Tables Manager (Database Utilities on the Tools menu), or
    you can add some code to check this and pop up the Windows FileOpen dialog
    if the links are not valid. Sample code in this article:
    Relink Access tables from code
    at:
    http://www.mvps.org/access/tables/tbl0009.htm

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Kate" <ktm19@student.canterbury.ac.nz> wrote in message
    news:1150687023.048767.307680@g10g2000cwb.googlegroups.com...
    > Hello,
    > I am in the process of attempting to split my database.
    > I used the wizard, and came up with the error "subscript out of range.
    > Invalid procedure call or argument". However, it did seem to have
    > created a backend, so i continued.
    >
    > The backend had the tables. Nice.
    >
    > Was it supposed to have created a front end as well, or is the front
    > end just the bit I started with? The bit I started with still had the
    > tables in it, so I deleted the tables, and used "get external data,
    > link tables" to link my 'front end' with the back end, then reset the
    > relationships.
    >
    > Now in the bit which i think is hte front end, you can still see the
    > tables. (They have arrow thingys next to them to show they are linked).
    > Is this what is supposed to have happened?
    >
    > Now do I just have to send a front end to each of my users? Then, in
    > my copy of the front end, I can make design changes at my leisure, and
    > when I'm done resend these copies to the users? (I have yet to figure
    > out the 'how to' in automating this bit)
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 18 Jun 2006 20:17:03 -0700, "Kate" <ktm19@student.canterbury.ac.nz>
    wrote:

    >Hello,
    >I am in the process of attempting to split my database.
    >I used the wizard, and came up with the error "subscript out of range.
    >Invalid procedure call or argument". However, it did seem to have
    >created a backend, so i continued.


    That's an error I've seen before. Not sure just what causes it.

    >The backend had the tables. Nice.
    >
    >Was it supposed to have created a front end as well, or is the front
    >end just the bit I started with? The bit I started with still had the
    >tables in it, so I deleted the tables, and used "get external data,
    >link tables" to link my 'front end' with the back end, then reset the
    >relationships.


    Pain in the neck to do so, but you did the right thing. Note that the
    relationships must be defined and enforced *in the backend* - not the
    frontend; the frontend inherits them.

    >Now in the bit which i think is hte front end, you can still see the
    >tables. (They have arrow thingys next to them to show they are linked).
    >Is this what is supposed to have happened?


    Yes, exactly.

    >Now do I just have to send a front end to each of my users? Then, in
    >my copy of the front end, I can make design changes at my leisure, and
    >when I'm done resend these copies to the users? (I have yet to figure
    >out the 'how to' in automating this bit)


    See http://www.granite.ab.ca/access/splitapp.htm for some suggestions.
    There's a topnotch frontend-distribution tool on that website as well.

    John W. Vinson[MVP]
     
  5. Kate

    Kate
    Expand Collapse
    Guest

    Oh, and one more thing,

    The back end gets saved on the server. Does every user have to have
    their own copy of hte front end? (ie does it defeat the whole purpose
    to have all users using the same front end located on the server?)

    If one person makes a change, it seems that hte others can only see it
    if they close the form and open it again. Should this happen
    automatically, or should you have to close it to see others' changes?
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Kate,

    I'm not sure why you received the subscript out of range error, but it
    sounds like it split correctly. You can also split a database manually
    without using the wizard: Create a new blank database, import the tables from
    the source DB ---> this becomes your new back-end (BE) database. Then open
    the source DB, delete the tables, use File > Get External Data > Linked
    Tables... and establish table links from your new BE database. As always,
    it's best to have a good back-up of your DB before you get started.


    > Was it supposed to have created a front end as well, or is the front
    > end just the bit I started with?


    The FE is the bit you started with.


    > Now in the bit which i think is the front end, you can still see the
    > tables. (They have arrow thingys next to them to show they are linked).
    > Is this what is supposed to have happened?


    Yes. The arrow thingys represent shortcuts to the actual tables. You might
    have noticed by now that you cannot open a table in design view that displays
    the sideways black arrow thingy. If you need to make design changes to the BE
    tables, you must open the BE database in exclusive mode (ie. other people
    cannot have it open at the same time).


    > Now do I just have to send a front end to each of my users?


    Well, yes, but I recommend checking some things first. The most important
    things to do are 1.) Disable Name Autocorrupt, 2.) Set all SubDatasheets to
    [None] and 3.) Establish a permanent connection to the BE database. Here is
    a web page that covers these details, and many more:

    Implementing a Successful Multiuser Access/JET Application
    http://www.access.qbuilt.com/html/multiuser_applications.html


    > Then, in my copy of the front end, I can make design changes at my leisure,
    > and when I'm done resend these copies to the users?


    Yes. That's exactly how you do it.


    > (I have yet to figure out the 'how to' in automating this bit)


    Update Front End
    http://www.access.qbuilt.com/html/update_front_end.html


    Good Luck!

    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Kate" wrote:

    > Hello,
    > I am in the process of attempting to split my database.
    > I used the wizard, and came up with the error "subscript out of range.
    > Invalid procedure call or argument". However, it did seem to have
    > created a backend, so i continued.
    >
    > The backend had the tables. Nice.
    >
    > Was it supposed to have created a front end as well, or is the front
    > end just the bit I started with? The bit I started with still had the
    > tables in it, so I deleted the tables, and used "get external data,
    > link tables" to link my 'front end' with the back end, then reset the
    > relationships.
    >
    > Now in the bit which i think is hte front end, you can still see the
    > tables. (They have arrow thingys next to them to show they are linked).
    > Is this what is supposed to have happened?
    >
    > Now do I just have to send a front end to each of my users? Then, in
    > my copy of the front end, I can make design changes at my leisure, and
    > when I'm done resend these copies to the users? (I have yet to figure
    > out the 'how to' in automating this bit)
    >
    > Thanks
     
  7. Kate

    Kate
    Expand Collapse
    Guest

    With the relationships thing, one of my relationships is between a
    query and a table, so clearly I can not set that in the backend.
    Should I be trying to reset that relationship so that it is between
    tables, or is it ok to just leave as is and define in the front end?
     
  8. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    > Does every user have to have their own copy of the front end?

    They don't have to, but this is certainly the best practice. During a
    presentation that I attended at Microsoft approx. (3) years ago, an Access
    Program Manager identified sharing an entire database as the number one cause
    of DB corruption. Many people do it without a problem, but I do not recommend
    doing so. Some of the reasons are listed here:

    http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB

    along with the (5) reasons I listed in this article:

    http://www.access.qbuilt.com/html/update_front_end.html

    MVP Albert Kallal has written a paper on this topic as well:
    http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm


    > If one person makes a change, it seems that hte others can only see it
    > if they close the form and open it again. Should this happen
    > automatically, or should you have to close it to see others' changes?


    You can set a refresh interval on the Advanced tab of Tools > Options, but I
    would not set this value too low, because it could result in excessive
    network "chattiness". Here is information from the Access 2003 Help file on
    this topic (I removed information that applies to Access Projects (.adp
    files):

    Refresh or requery data
    If you are using a Microsoft Access database that's shared on a network,
    other users could be changing data while you are viewing the same data in a
    datasheet or form. Microsoft Access updates the data you see at regular
    intervals. However, you can immediately display the most current data by
    refreshing the records.

    In an Access database (.mdb), refreshing records only updates the data that
    already exists in your datasheet or form. It doesn't reorder records, display
    new records, or remove deleted records and records that no longer meet
    specified criteria. To perform those tasks, requery the records.

    To requery or refresh:

    Open a table, query, or form in Datasheet view, PivotTable view, or
    PivotChart view, or open a form in Form view. Do one of the following:

    To refresh the records in Datasheet or Form view, click Refresh on the
    Records menu.

    To refresh the records in PivotTable or PivotChart view, click Refresh on
    the PivotTable or PivotChart toolbar.

    To requery the records, press SHIFT+F9.
    _________________________________

    You can also add a command button to forms to perform refresh or requery
    operations.

    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Kate" wrote:

    > Oh, and one more thing,
    >
    > The back end gets saved on the server. Does every user have to have
    > their own copy of hte front end? (ie does it defeat the whole purpose
    > to have all users using the same front end located on the server?)
    >
    > If one person makes a change, it seems that hte others can only see it
    > if they close the form and open it again. Should this happen
    > automatically, or should you have to close it to see others' changes?
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Relationships created between tables and queries are really only useful for
    when you create a new query, and you include the affected table and query:
    you'll get a relationship of the same type automatically inherited in the
    query. However, you cannot enforce referential integrity in a relationship
    between a table and a query. I cannot say that I've ever had the need to
    create such a relationship at the Tools > Relationships level.

    > Should I be trying to reset that relationship so that it is between
    > tables,


    Yes. Do this in the back-end database.


    > or is it ok to just leave as is and define in the front end?


    Relationships are only enforced in the BE database.


    Note: VERY IMPORTANT
    This detail is buried in my multiuser paper, but you might miss it. If you
    make any design changes to the back-end database, especially adding or
    removing indexes, I highly recommend that you delete the linked table in the
    FE database, compact the FE database, and then re-establish the link using
    File > Get External Data > Linked Tables... The reason is that Access
    caches information in the table links that can become out-of-date if you make
    design changes to the BE database. This means that if you make these types of
    changes to the BE database, you should plan on distributing a new copy of the
    FE database to all of your users.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Kate" wrote:

    > With the relationships thing, one of my relationships is between a
    > query and a table, so clearly I can not set that in the backend.
    > Should I be trying to reset that relationship so that it is between
    > tables, or is it ok to just leave as is and define in the front end?
     
  10. Kate

    Kate
    Expand Collapse
    Guest

    >3.) Establish a permanent connection to the BE database. Here is
    >a web page that covers these details, and many more:


    I did not find this part on your link. What do you mean by "a
    permanent connection"

    Thanks
     
  11. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Kate,

    That's in the section with the title that reads "Keep a persistent
    connection open", about 1/3 of the way down this page:
    http://www.access.qbuilt.com/html/multiuser_applications.html



    Keep a persistent connection open

    See Access MVP Tony Toews's tip, "LDB locking which a persistent recordset
    connection fixes":
    http://www.granite.ab.ca/access/performanceldblocking.htm

    See the section, "Minimize the number of connections that are made from each
    client," in this Microsoft Knowledge Base article:
    http://support.microsoft.com/kb/303528/EN-US/#15

    See Luke Chung's tip, "Tip #36: Increase Performance of Linked Databases":
    http://www.fmsinc.com/free/newtips/Access/accesstip36.asp

    Also see the fourth section, "Improve performance of linked tables," in this
    Microsoft Office Online tip:
    http://office.microsoft.com/en-us/assistance/HP051874531033.aspx

    An easy method of doing this is to create a table in the back end database
    that has just one record. For example, this record might indicate the latest
    version number of the front end database. Create a form that is bound to
    this table. Use VBA code or an Autoexec macro to open this form in hidden
    mode when the front end database is opened. That's all there is to it!




    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Kate" wrote:

    > >3.) Establish a permanent connection to the BE database. Here is
    > >a web page that covers these details, and many more:

    >
    > I did not find this part on your link. What do you mean by "a
    > permanent connection"
    >
    > Thanks
     
  12. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "Kate" <ktm19@student.canterbury.ac.nz> wrote:

    >The back end gets saved on the server. Does every user have to have
    >their own copy of hte front end? (ie does it defeat the whole purpose
    >to have all users using the same front end located on the server?)


    Yes, sharing a FE leads to a greatly increased risk of corruption.
    Plus it becomes much more difficult to distribute updates.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
  13. Kate

    Kate
    Expand Collapse
    Guest

    This method seems to be the simplest:

    "The simple situation would be to create a simple form based on a
    table. Any table but one with as few records as possible. Or create a
    dummy table and put one record in it. In your startup form add the
    following line of code in the startup forms OnOpen event.

    DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden"

    When you say 'my startup form', I don't really have one. I have a
    couple of forms, and which one you open depends on what you want to do.
    It is conceivable also that you don't open a form, but go straight to
    a report. So I am wondering where I should put the OnOpen event. Can
    I put it in several places? Or is there someway to do a OnOpenDatabase
    event?

    THanks
     
  14. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Create a form, and then create an AutoExec macro that opens that form in
    Hidden mode.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Kate" <ktm19@student.canterbury.ac.nz> wrote in message
    news:1150838044.732152.5210@r2g2000cwb.googlegroups.com...
    > This method seems to be the simplest:
    >
    > "The simple situation would be to create a simple form based on a
    > table. Any table but one with as few records as possible. Or create a
    > dummy table and put one record in it. In your startup form add the
    > following line of code in the startup forms OnOpen event.
    >
    > DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden"
    >
    > When you say 'my startup form', I don't really have one. I have a
    > couple of forms, and which one you open depends on what you want to do.
    > It is conceivable also that you don't open a form, but go straight to
    > a report. So I am wondering where I should put the OnOpen event. Can
    > I put it in several places? Or is there someway to do a OnOpenDatabase
    > event?
    >
    > THanks
    >
     
  15. Kate

    Kate
    Expand Collapse
    Guest

    Think I got it figured after all. I didn't realise that if you had a
    macro and named it AutoExec that it would run from startup. So I
    created a table in the BE, went to the front end and linked to that
    table, and created a form in the FE called FrmKeepOpen. THen I wrote a
    (AutoExec) macro and opened the (empty) form in hidden mode from there.
    Is this ok, and all I need to do to keep a permanent connection?
     
  16. Kate

    Kate
    Expand Collapse
    Guest

    When I do the autoupdate FE part, I want to do it manually. So if I
    use hte code from
    http://www.access.qbuilt.com/html/update_front_end.html, my question is
    about these two lines:

    strSourceFile = "\\server\share\YourFEDatabase.mde"
    strDestFile = CurrentProject.FullName

    So hte first line is the path to my FE where I made the changes to the
    design.
    Is hte second line going to be different for each persons copy of the
    FE? ie. Is it their path to where their FE is stored? What do
    CurrentProject and FullName refer to?

    To see if I understand correctly...
    Now I write the Create UpdateFEVersion() code in the FE before I
    distribute it to the users for hte first time. This FE will also
    contain a table where the version number can go. (Do I create this
    table in teh FE, if I do that, each one should be able to have its own
    entry in there with no linking, right?)
    The BE also has a table with the version number, which I guess has to
    be linked to the FE.
    Next, I have to write a function that compares the local version number
    to that in the BE (when I make a design change, I will update hte
    version number in the BE). If hte local version # < BE version #, then
    Create UpdateFEVersion() is called. Once finished the local version
    table has to be updated as well.
    For my comparing function, is it a function or a sub, and where do I
    called it from? Should it be on a AutoExec macro?

    Thanks
     

Share This Page