front end/back end

#1 (**permalink**) 28-Jul-2006, 08:22 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11645-front-end-back-end.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
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 (**permalink**) 28-Jul-2006, 08:22 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
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" wrote in message
news:1150687023.048767.307680@g10g2000cwb.googlegr oups.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
> 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)

#3 (**permalink**) 28-Jul-2006, 08:22 AM

On 18 Jun 2006 20:17:03 -0700, "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.

That's an error I've seen before. Not sure just what causes it.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
>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]

#4 (**permalink**) 28-Jul-2006, 08:22 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
if they close the form and open it again. Should this happen
automatically, or should you have to close it to see others' changes?

#5 (**permalink**) 28-Jul-2006, 08:22 AM

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/mu...lications.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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645

> (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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
> 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

#6 (**permalink**) 28-Jul-2006, 08:22 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
tables, or is it ok to just leave as is and define in the front end?

#7 (**permalink**) 28-Jul-2006, 08:22 AM

> 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/ge...1.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/AlbertKal...plit/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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
> 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?

#8 (**permalink**) 28-Jul-2006, 08:22 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645
FE database to all of your users.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.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?

#9 (**permalink**) 28-Jul-2006, 08:22 AM

>3.) Establish a permanent connection to the BE database. Here is
>a web page that covers these details, and many more:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11645

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

Thanks