Enhancing database how to

[Guest]

So, I am giving a db I developed to someone else. I still have enhancements
to make i.e., I will be adding forms and reports. How do I port my
enhancements to the version they are using? They will be updating tables and
mine will be old data. If I copy my whole db over their updates will be lost.

Thanks.

 

[Guest]

Hi Phil,

As a general rule it's a good idea to split a database into front and back
ends. The back end has all the tables, and the front end includes everything
else. Each user gets their own copy of the front end on their workstation,
and the backend goes on some shared space on a server that each user has
rights to read/write (as the user's needs would dictate).

One of the benefits of splitting your database, and there are a number of
important ones, is that you can continue to develop the front end while your
users use the current front end. When you add enough to warrant it, you just
distribute copies of the new front end to your users. There are even
applications out there that generous developers have made available to the
rest of us that will automatically update user front ends by checking the
version they have on their workstations and comparing that to the most recent
version you've made available for updating.

Bottom line: you should split your database ANYWAY, AND it addresses this
particular issue as a by-product.

Hope that helps,
CW

 

[Guest]

Makes sense. How do I split it?

Hi Phil,

As a general rule it's a good idea to split a database into front and back
ends. The back end has all the tables, and the front end includes everything
else. Each user gets their own copy of the front end on their workstation,
and the backend goes on some shared space on a server that each user has
rights to read/write (as the user's needs would dictate).

One of the benefits of splitting your database, and there are a number of
important ones, is that you can continue to develop the front end while your
users use the current front end. When you add enough to warrant it, you just
distribute copies of the new front end to your users. There are even
applications out there that generous developers have made available to the
rest of us that will automatically update user front ends by checking the
version they have on their workstations and comparing that to the most recent
version you've made available for updating.

Bottom line: you should split your database ANYWAY, AND it addresses this
particular issue as a by-product.

Hope that helps,
CW

 

[Guest]

Hi phil,

Depending on your version of access, you might have a database splitter
wizard that you could use if you want. If you have user level security in
your database, I think the splitter wizard will strip it out (inadvertently?)
during the process.....at least for your backend, but I'm not positive.

I've always done it manually. Here's a link to microsoft's article on doing
it.

http://support.microsoft.com/default.aspx/kb/304932

Hope that helps,
CW

 

[Peter Hibbs]

Phil,

When you have successfully split the database and it is working
correctly you may want to look at these two Web sites which will help
to maintain and update your user's database.
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE_ReLink.mdb'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'

Peter Hibbs.

 

[Albert D. Kallal]

Makes sense. How do I split it?

:

I have an article that explains the whole approach to the split concept here

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Guest]

I created a database containing the following code:

Dim db As Database
Dim rec As RecordSet

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblFLSA")
rec.Index = "PrimaryKey"
rec.Seek "=", FLSA
If rec.NoMatch Then
WhatFLSA = ""
Else
WhatFLSA = rec("ExemptStatus")
End If
rec.Close

After splitting the database I encountered:

Run -time error '3251':
Operation is not supported for this type of object.

rec.Index = “PrimaryKeyâ€

Do anyone have an idea while this line fails after splitting the database?

 

[Parax .]

Split the database into two, one half contains tables and data, the other half contains forms reports & queries and Links to the data. You can then replace the frontend half without affecting the data in the backend half.

There is a wizard to do this for you, or you can do it quickly manually by creating a new file and importing all tables, then delete from the frontend and link the tables. you will need to become familier with the linked table manager.

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Albert D. Kallal]

There are few problems that exist when you split a database about the only
thing I can think of that don't work is the actual seek command.

You can do one of two things to solve your problem:

a) use the coding work around as explained here:

How to perform seek on link tables:
http://www.mvps.org/access/tables/tbl0006.htm

b) simply use SQL code such as

whatFLAS = dlookup("ExemptStatus","tblFLSA","id = 'FLSA')

Note how the above is only one line of code and is that is less complex
then your example by quite a bit.

I would only use seek if I'm running over and over in a loop. There is
little if any advantage to speedier performance then the above dlookup for
one value.

and of course in other ways to use SQL

dim rec as recordset

set rec = currentdb.OpenRecordSet("select ExemptStatus from tblFLSA where
id = 'FLSA')
if rec.ReocrdCount > 0 then
WhatFLSA = rec("ExemptStatus")
else
WhatFLSA = ""
end if
rec.Close

So even if you write out the full SQL and execute a full query it's still
really less code then the seek. as mentioned I would only use the seek if
you're running this thing over and over in an actual type of loop where you
need to read the primary key over and over in a very rapid fashion. for
pulling just one value out seek not gain you any performance at all.