Split database

[Guest]

I have just recently split my database. I have both the front end and back
end on a shard drive and have copied the front end to the applicable
machines. The data entry form on the front end writes to two temporary
tables. The data is appended to two permanent tables upon the
btnUpdate_click action.

Is it reasonable to have temporary tables set up for each front end. That
way user A can close the application without updating and the information is
there when he reopens is. It also would prevent data entry competition
within the temporary table.

What would be the best way to change this? I could copy the table structure
but I am unclear about the linking.

Any suggestion/help would be appreciated

 

[Rick Brandt]

I have just recently split my database. I have both the front end
and back end on a shard drive and have copied the front end to the
applicable machines. The data entry form on the front end writes to
two temporary tables. The data is appended to two permanent tables
upon the btnUpdate_click action.

Is it reasonable to have temporary tables set up for each front end.
That way user A can close the application without updating and the
information is there when he reopens is. It also would prevent data
entry competition within the temporary table.

What would be the best way to change this? I could copy the table
structure but I am unclear about the linking.

Any suggestion/help would be appreciated

Delete the links in the front end to the temp tables. Then import those tables
from the back end. Afterwards they can be deleted from the back end file.

Be aware that using temp tables in the front end can add to file bloat, but you
can monitor that to see if it is significant. If you will be updating the fron
end often enough then it shouldn't be a problem.

Note that in this context "temp table" refers to permanent tables that you fill
and purge of temporary data. If you were actually creating and destroying the
tables themselves then building them in an external (local) mdb is better. That
way the entire external mdb can be created and destroyed and your file doesn't
suffer any bloat.

 

[Guest]

Using table in the front end is not a bad idea if the data is only of a
temporary nature that would be used only by that user during that session. I
would be wary of saving data in local user tables between sessions.

As to the linking issues, you don't have to worry about it. Your other
objects don't know or care if the tables or local or linked. If you want to
move tables to your front end, just delete the links and import the tables
(structure only) to the front end.

An alternative is to use ADODB cursors instead of local tables.

 

[Guest]

Thanks Rick and Dave for the quick response. The "temp table" would actually
be used for the current entry. However, if entry is interrupted it would
allow the user to return to the entry without losing data.

Its seems from your responses that this is a reasonable and a fairly easy
change to make.

It begets another problem, however. My form writes to two "temporary"
tables. One has flight information. The second has crew position/hours.
The flight "temp" table's PK is an autonumber which is then written to the
crew "temp" table as a FK.
Having the tables reside on the front ends will cause duplicaton in the PK.
If I place the autonumber in the permanent flight table in the back end, how
can I ensure the correct FK is placed in the crew permanent table.

That sounds very confusing, but hopefully you can find the problelm in the
statement.

Thanks,
Mary

Currently,

 

[Rick Brandt]

Thanks Rick and Dave for the quick response. The "temp table" would
actually be used for the current entry. However, if entry is
interrupted it would allow the user to return to the entry without
losing data.

Its seems from your responses that this is a reasonable and a fairly
easy change to make.

It begets another problem, however. My form writes to two "temporary"
tables. One has flight information. The second has crew
position/hours. The flight "temp" table's PK is an autonumber which
is then written to the crew "temp" table as a FK.
Having the tables reside on the front ends will cause duplicaton in
the PK. If I place the autonumber in the permanent flight table in
the back end, how can I ensure the correct FK is placed in the crew
permanent table.

That sounds very confusing, but hopefully you can find the problelm
in the statement.

AutoNumber usage makes the use of "temp" or "work" tables very problematic.

You would first have to append the flight data to the real table omitting the
AutoNumber field so that the back end applies its own value. Then you would
have to retrieve that value so you can use it when you append the crew data.
Depending on how you do that and how many users might be simultaneously
appending records this can be very tricky.

Do you really have a compelling reason to use temp tables and not just write
directly to the real ones? In all my years of database development I cannot
recall ever needing to do this.

 

[Jeff Boyce]

To add to Rick's and Dave's observations, I would ask if, since you've
described multiple copies of the front-end, if your "other" users would need
to be able to access the changes made by user#1. If so, I would also vote
for connecting the front-ends directly to the back-end tables, so that ALL
users can see the current state of affairs.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

The reason I a using "temp" tables is so that data will not be written
directly to the database as it is entered. I need the user to be asked if he
wants to update the data. If so, then the data is appended to the permanent
table and deleted from the temp table. If not, the data is deleted from the
temp table.

The only other way I could think to do this was to have unbound text boxes
which get written to the permanent table on update. Perhaps this would have
been a better way to go?

 

[Rick Brandt]

The reason I a using "temp" tables is so that data will not be written
directly to the database as it is entered. I need the user to be
asked if he wants to update the data. If so, then the data is
appended to the permanent table and deleted from the temp table. If
not, the data is deleted from the temp table.

But many developers only "think" that they need to do this, often because of
some other system that they formerly used or because they see web sites that
work this way. It is called the thin-client model and it really is not a good
way to use Access.

The question I always ask... WHY would a user bother to enter the data if they
did NOT want to add it to the permanent database? If your answer is "they might
change their mind" then my response is that they are just as likely to change
their mind after pressing your save button that copies the data to the real
tables as they are to change their mind before doing that. Just give them an
option to delete or "void" records without actually deleting them and be done
with it.

 

[Guest]

Thank you all for your input. I am learning a great deal. My plan is

1. To add an autonumber field to my permanent flight table.
2. Change the underlying tables in my data entry form to the "permanent"
tables.
3. Change the btnUpdate_click coding.
4. Add btnClearScreen_coding to remove the current entry from the permanent
tables, if necessary (database "owner" requirement)

This is my first attempt at a multi-user application and a split database,
so I appreciate your help.
--
Teach me to fish! Thanks for the help.
Pax, M

To add to Rick's and Dave's observations, I would ask if, since you've
described multiple copies of the front-end, if your "other" users would need
to be able to access the changes made by user#1. If so, I would also vote
for connecting the front-ends directly to the back-end tables, so that ALL
users can see the current state of affairs.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Keith Wilby]

The reason I a using "temp" tables is so that data will not be written
directly to the database as it is entered. I need the user to be asked if
he
wants to update the data. If so, then the data is appended to the
permanent
table and deleted from the temp table. If not, the data is deleted from
the
temp table.

The only other way I could think to do this was to have unbound text boxes
which get written to the permanent table on update. Perhaps this would
have
been a better way to go?

Use the "real" tables and put some code in the form's Before Update event to
ask the user if they want to commit the record. Cancel the event if they
answer 'no'.

Keith.
www.keithwilby.com

 

[Tony Toews [MVP]]

AutoNumber usage makes the use of "temp" or "work" tables very problematic.

You would first have to append the flight data to the real table omitting the
AutoNumber field so that the back end applies its own value. Then you would
have to retrieve that value so you can use it when you append the crew data.
Depending on how you do that and how many users might be simultaneously
appending records this can be very tricky.

I respectfully disagree. When using DAO the following works very
well to determine the last record just inserted.

RS.Move 0, RS.LastModified
lngTableListID = RS!stlid

If using SQL then it I vaguely recall something about some kind of @@
SQL command would work even in an Jet database but I don't recall any
details now.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[RoyVidar]

If using SQL then it I vaguely recall something about some kind of @@
SQL command would work even in an Jet database but I don't recall any
details now.

Yes, with ADO, I think it could look something like the following

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last
Identity (Autonumber) inserted on this connection.

 

[Tony Toews [MVP]]

Yes, with ADO, I think it could look something like the following

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last
Identity (Autonumber) inserted on this connection.

Yup, that looks familiar.

Thanks, 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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Set rs = cn.Execute("SELECT @@Identity", , adCmdText)

It sure surprised me when someone first posted that a few years ago
and stated that worked well against a Jet database.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

It sure surprised me when someone first posted that a few years
ago and stated that worked well against a Jet database.

That's a feature of ADO, not of Jet.

If you're converting DAO code that was running against Jet to now
run against a server database, you can no longer get the ID value
before the UPDATE of the recordset in which you appended the new
record. Instead, after the UPDATE of your recordset, move to the
LastModified record and then you can pull the ID from there.

 

[RoyVidar]

That's a feature of ADO, not of Jet.

If that was true, then the following shouldn't work

Sub DAO_Identity()

Dim rs As DAO.Recordset

With DBEngine(0)(0)

On Error Resume Next
.Execute "CREATE TABLE DAO_IdentityTest " & _
"(id Counter Primary Key, sometext Text(25))"
On Error GoTo 0
.Execute "INSERT INTO DAO_IdentityTest " & _
"(sometext) VALUES ('Just testing')"
Set rs = .OpenRecordset("SELECT @@Identity")
Debug.Print rs.Fields(0).Value

End With

End Sub

But it does (how reliable it is with concurrent users, I don't know).

Quoting from the book where I first encountered it (A2k Developers
Handbook, Sybex, 1999) p 197:

"The Jet 4 extensions also add support for querying the last-assigned
Auto-Number value using the same syntax as SQL Server

SELECT @@IDENTITY"

Jet 4 extensions, not ADO.

 

[David W. Fenton]

If that was true, then the following shouldn't work

Sub DAO_Identity()

Dim rs As DAO.Recordset

With DBEngine(0)(0)

On Error Resume Next
.Execute "CREATE TABLE DAO_IdentityTest " & _
"(id Counter Primary Key, sometext Text(25))"
On Error GoTo 0
.Execute "INSERT INTO DAO_IdentityTest " & _
"(sometext) VALUES ('Just testing')"
Set rs = .OpenRecordset("SELECT @@Identity")
Debug.Print rs.Fields(0).Value

End With

End Sub

But it does (how reliable it is with concurrent users, I don't
know).

Quoting from the book where I first encountered it (A2k Developers
Handbook, Sybex, 1999) p 197:

"The Jet 4 extensions also add support for querying the
last-assigned Auto-Number value using the same syntax as SQL
Server

SELECT @@IDENTITY"

Jet 4 extensions, not ADO.

I didn't know that -- thanks for pointing it out.

 

[Tony Toews [MVP]]

"The Jet 4 extensions also add support for querying the last-assigned
Auto-Number value using the same syntax as SQL Server

SELECT @@IDENTITY"

Jet 4 extensions, not ADO.

I'll be darned. Maybe I should go back and reread those books front
to back and see what else there is to learn.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/