splitting a database

[mtrimpin24]

so i'm trying to split a database and its providing me with some errors.
first, however, i have a question.
the only table within my database is a link to an external table in a
protected database. The object was to create a front end for helpdesk to
use, where they couldn't make any changes, but could get the records as they
were updated.
So here's my question.
1. do i even have to split the database? there's no data in the front end i
designed.

2. How often does Access do updates. If someone edits the record in the
table that holds the data, when will access show it in the front end?

3. does a query or something have to be run to show the new/updated data, or
does Access provide this as-is, with no fiddling?

4. Can i make copies of my "front end" and let multiple people log into it
all at once, or can they all open the same front end without
slowdown/corruption issues?


I must say, i've had a terrible time finding any of this information
anywhere, on any page, helpfile or otherwise, on the net or elsewhere.

 

[Douglas J. Steele]

so i'm trying to split a database and its providing me with some errors.
first, however, i have a question.
the only table within my database is a link to an external table in a
protected database. The object was to create a front end for helpdesk to
use, where they couldn't make any changes, but could get the records as
they
were updated.
So here's my question.
1. do i even have to split the database? there's no data in the front end
i
designed.

If you don't have any tables in your front-end, then it's already split.
(Linked tables don't count)

2. How often does Access do updates. If someone edits the record in the
table that holds the data, when will access show it in the front end?

Access does updates whenever it's told to do an update.

On the other hand, I don't think you're actually asking about Access doing
updates. (see below)

3. does a query or something have to be run to show the new/updated data,
or
does Access provide this as-is, with no fiddling?

All a linked table is is a pointer to the actual table in the other data
source. It's a "live" pointer: any time you open the linked table or use a
reference to that linked table, you get the current data from the real data
source.

4. Can i make copies of my "front end" and let multiple people log into
it
all at once, or can they all open the same front end without
slowdown/corruption issues?

Each user should have his/her own copy of the front-end, ideally on his/her
hard drive.

I must say, i've had a terrible time finding any of this information
anywhere, on any page, helpfile or otherwise, on the net or elsewhere.

http://www.granite.ab.ca/access/splitapp/index.htm
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[mtrimpin24]

Allright so this thing says that in order to have the proper performance, i
should open the linked table and keep it open in the front end, but
hidden/invisible, if i read correctly? that still doesn't answer my question.
Do i need to have a requery constantly running attached to a beforeupdate
button press on some command button in order to see the new data. when doing
this i get several 10's of seconds lag. sometimes over a minute while it
runs through 50,000 records or more. Also, since this is a linked table, i'm
not entirely sure what it means when it says that i should open the table and
keep it open. The whole point of splitting the database was to remove the
data from the front end. how do i open and keep open the table?

 

[Douglas J. Steele]

No, you do not need a constant requery running. As I said, you've got a live
pointer to the data.

When Albert says "create a small form in the FE that is attached to a table
in the BE", you can either use the linked table as the RecordSource for that
form, or you can create a query and use that query. All it's doing is
ensuring that there's a connection between the front-end and the back-end so
that you don't have to create a new connection when you go to read the data.

 

[mtrimpin24]

I see. so i did read it correctly. Then help me correct something:

i have a front end open on my machine. the help desk will be looking at
this, and i wanted to have them only able to search the records. no changes.
that function works fine (the searching). What doesn't work however, is that
if someone upstairs opens the table that the FE is linked to, adds a record,
saves and closes the thing, the new record never shows up on the FE until i
close and reopen it. which is silly. What must i do in order to make the
new record show up on the FE w/o reopening it? currently the FE simply has a
link to the table. no queries or anything are used other than the search
code, which is in VBA. Am i going to have to close/reopen the form after
each use?

 

[Douglas J. Steele]

Okay, now we're talking something completely different.

Even if your table was in the same database, you wouldn't see the record
until you closed and reopened the form, or you issued a Requery on your
form.

How are you searching in your form? You could always simply put a

Me.Requery

immediately before you do the search.

 

[mtrimpin24]

I'm using this to perform my searches. Should i just tack "Me.requery" on
the top of it, or the bottom? or does it matter?


Private Sub Record_Locator_Click()
On Error GoTo Err_Record_Locator_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Record_Locator_Click:
Exit Sub

Err_Record_Locator_Click:
MsgBox Err.Description
Resume Exit_Record_Locator_Click



End Sub

 

[Douglas J. Steele]

"immediately before you do the search" would imply before the
DoCmd.DoMenuItem.

 

[mtrimpin24]

All right. So i added "me.requery" on the top of the search button click
event.
saved it. had someone enter the table, make a new record, save and close it.

upon searching multiple times, and trying to scroll to the end past the
current "last record" it shows, it does not ever show the new record unless
we close the database and reopen it.
the table is in a different database, and linked to the helpdesk front end.
does that matter?

 

[mtrimpin24]

I still need help with the issue below.

All right. So i added "me.requery" on the top of the search button click
event.
saved it. had someone enter the table, make a new record, save and close it.

upon searching multiple times, and trying to scroll to the end past the
current "last record" it shows, it does not ever show the new record unless
we close the database and reopen it.
the table is in a different database, and linked to the helpdesk front end.
does that matter?