Access db on Microsoft Small Business Server. How to approach it

[emsik1001]

Hi

I have an access database to develop however the files are hosted on a Microsoft Small Business Server.

What should I be aware of when developing Access dabase on MS Small Business Server?

There are 5 user, usually 2 of them will use the db at the same time and its Access 2000.

Many thanks in advance for your expertise.
Emil

[missinglinq]

I don't work in a server environment, but for multiple-users, you'll need to split your database into front end/back end, which can be done by the going to Tools - Database Utilities - Database Splitter.

This will place the tables with data in the back end and basically everythig else, forms, queries, reports, etc. will be placed into the front end. One copy of the back end goes on your server and a copy of the front end needs to go on each users PC.

One of the advantages of this setup is that you can actually have different front ends for various users.

As I said, I seldom work with split databases, but there are others here that can help you with details as they arise.

Linq ;0)>

[emsik1001]

So I understand that creating a back end database is the best way (what are the negatives about using one file? I'm just curious)

I have used the database splitter (which is quiet easy to understand as it only creates a new database and links tables to the new database)

What are the available options for the front-end interface? I heard something about .mde files, asp, asp.net and I presume that's not everything.

What are the pros and cons for each one?

[missinglinq]

Sharing front ends is a primary cause of database corruption! You can simply use your mdb front end with a copy on each users PC, as I said, or you can convert it to an mde file, if you like. The advantage of using an mde file is that it "locks" the design of your databse, i.e. the forms, queries, reports, etc. so that they cannot be changed by your end users. Sometimes this is desirable/neccessary, and sometimes it's not. Some developers want to allow the end users to create their own queries, for instance, or their own reports, if the users are skilled in these things. Others want total control over these things. Some developers worry about end users diddling behind the scenes, possibly harming the function by making design changes.

But the bottom line is that Access is usally used for the front end as well as the back end. Asp/Asp.net and so forth would only be used for the front end if you were planning on making the database interactive over the internet, and that's another whole story.

One final warning! If you elect to convert your mdb to an mde file, make a back of your mdb file and store it in a safe place! You cannot make any modifications to an mde file, and sooner or later, you'll need to!

Linq ;0)>

[emsik1001]

So in my case the simplies way would be to use mdb or mde.

What kind of problems can I expect from using this method?
If several users needs to add new records at the same time does it have same negative effects? what about modyfing records?

[PianoMan64]

So in my case the simplies way would be to use mdb or mde.

What kind of problems can I expect from using this method?
If several users needs to add new records at the same time does it have same negative effects? what about modyfing records?

Now since I've worked with Microsoft SBS 2000 before, and a little with SBS 2003 Premier, You also have the option if the backend of MS Access can't handle the number of records you have the option of Upsizing the tables and queries to MS SQL, so that you have security options and a great deal more flexability in how to manage the records and who has access to what tables in the database.

There are many articals on this subject as well as I have personal experience with upsizing, and well a developing in MS SQL and Oracle.

Let me know if I can be of service to you,

Joe P.

[youmike]

I run an application for a client which has a number of back ends for various reasons. There are about 50 varieties of front end, each password protected and each aimed at different users, but using combinations of tables from the back ends. Each user has local copies of required front ends, but all are linked to the common back ends. I maintain by depositing altered front ends into the various users' folders on their machines and I do this from wherever I happen to be in the world.

The downside is traffic over the network when querying data, because Access works by bringing all the data that might be needed back to the local machine for manipulation. If we moved to SQL server, we'd reduce traffic, because this would allow all the manipulation to be done on the server and only the answer returned over the network. This would be at quite a lot of extra cost in terms of SQL server and redeveloping all the front ends. The processing times are tolerable, so we don't change.

The upside is that when a new front end is needed, development time is very quick. I'd defend our position against purists whosay that this is not what Access is designed to do. It's worked for us for many years and it's cost effective.

One final thought. If you do develop on this scale, I suggest you avoid macros like the plague. I use VBA and SQL exclusively and tend to embed SQL into VBA code using the CreateQueryDef object, rather than invoke queries created in the grid. The grid is very useful for setting up SQL which can then be copied into VBA modules.

[emsik1001]

Two questions?

1) What does upsizing mean and what does it do? (I didn't come accross this term before and couldn't find the answer it the dictionary ;p)

2) "CreateQuer yDef object, rather than invoke queries created in the grid" I do use VBA and SQL, however I didn't come accross this before, would you be able to give me some tips regarding this?

Now the database I create is rather small and I presume they will not add more then 1-2k records in the main table within a year. Once I finish it; it will be left to them to maintain it and develop and I presume they have some basic knowledge about adding a new form etc, so access front-end seems to be ideal for them.

Thanks for all the info, it is very usefull for me.

[PianoMan64]

Two questions?

1) What does upsizing mean and what does it do? (I didn't come accross this term before and couldn't find the answer it the dictionary ;p)

2) "CreateQuer yDef object, rather than invoke queries created in the grid" I do use VBA and SQL, however I didn't come accross this before, would you be able to give me some tips regarding this?

Now the database I create is rather small and I presume they will not add more then 1-2k records in the main table within a year. Once I finish it; it will be left to them to maintain it and develop and I presume they have some basic knowledge about adding a new form etc, so access front-end seems to be ideal for them.

Thanks for all the info, it is very usefull for me.

Well, I'll answer my part of the question, "Upsizing" is a wizard that is available in MS Access 2000 and newer. It allows you to transfer all your tables in MS Access database to MS SQL Database. This way you can use the power of MS SQL without having to know a great deal about how to create tables and maintane tables in MS Access. The other option you have since you have SBS and if you're running MS SQL is that you can create a MS Access Project file instead of a MDB file. This way all the backend data is kept on MS SQL and all the forms and Code, is kept in the Project file.

Hope that helps,

Joe P.