front end/back end

[Kate]

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

 

[Allen Browne]

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

 

[John Vinson]

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]

 

[Kate]

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?

 

[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]

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?

 

[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
__________________________________________

 

[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]

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

 

[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
__________________________________________

 

[Tony Toews]

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

 

[Kate]

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

 

[Douglas J. Steele]

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

 

[Kate]

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?

 

[Kate]

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