LInking to database on separate server - advantage?

[lorirobn]

Hello,

I am creating an Access application for a client at a non-profit. The IT liason there is researching SQL servers for us to put the application on. He feels I will most probably have to make changes to my app. once he gets the server. As an alternative, he suggested having my tables reside on a separate database server, and linking from my application to these tables. With this, he says, all my code, forms, etc. remain unchanged.

I just want what's best for my client. I don't mind making changes to my code to fit the server, and I tend to go with the more 'standard' approach. This sounds like a 'workaround', but I'd love to hear from other folks. Any opinions/advice on linking to tables in order to avoid making changes to an application? Are there advantages? Disadvantages?

Thanks greatly in advance,
Lori

 

[jsteph]

This is not a workaround, it is the norm to separate Access Tables from the Front-end. But I'm wondering about what you said

The IT liason there is researching SQL servers for us to put the application on.

Does this mean it will be SQL-Server? In which case the tables obviously would be on a separate server.
--Jim

 

[lorirobn]

The project manager requested that the database be on a "full-fledged" SQL Server. The IT liason was researching SQL servers and MSDE, then he came up with the idea to separate the tables from the Access application, for ease of implementation. I think my terminology may be off. From your response, it sounds like having these linked Access tables is also SQL-Server, due to residence on a separate computer. I'm not sure what the 'full-fledged' SQL-Server means, then, and why he is now suggesting linked tables.
Thanks for the info that Access tables are normally separated from the Front End. Not sure what our options are, I guess.
Sorry for my confusion... I guess I need to get clearer. Any direction to point me in for research? SQL Server/MSDE/Linked Tables?
Thanks,
Lori

 

[jsteph]

Lori,
In the earlier versions of Access, up to about Access 97 I think, the Access 'tables' if you will, were native Access tables ('JET' is MS's term for the Access db engine) Basically the db engine was part of the Access application.

So what the 'typical' set up for an app was, would be to have one .mdb file with just tables, and one .mdb file with just forms, code, reports, etc. So here the tables were linked.

SQL Server, Oracle, etc. were db engines only--meaning that, for all intents and purposes, they only held tables (and stored procedures, but let's not cofuse this), and you needed some other front-end to enter data, run reports, etc. Access .mdb's are a common and easy to use front end for these db engines, you can use an odbc-link to these databases as an easy way of having Access treat them almost as native Access tables, as far as having them be Row Sources for Forms, Reports, etc.

But MS wanted to have the best of both worlds, so they came out with MSDE, which was like SQL-server, but somewhat watered down, though it was more-or-less integral with Access.

I have never used MSDE and I never will--I think it was too much 'in the middle'--if you were going to deal with the extra hassles of converting or developing with MSDE, which used much of the same syntax as SQL-server, then why go half-way and suffer the MS-imposed limitations of MSDE? Anyway, it was cheaper and small shops could take this 'half-step' up and get some more power without buying a sql-server license.

So after that long-winded backstory, it sounds like your boss wants to go ahead with 'full fledghed' sql-server instead of MSDE or JET. And that's a good choice.

I think very few professional developers who have a database that's more than 20 meg would ever have it all JET and in one .mdb, most split the front-end and back-end if they're using JET, and of course with sql-server, it has to be split.
--Jim

 

[lorirobn]

Thank you, Jim. I am getting the picture now (slowyly!). So let me get some things straight. The SQL-Server (what I am calling 'full-fledged') separates the front-end and back-end? The application (forms, reports) exist in one place, the tables exist on another?

The IT 'liason' is talking about something else: about keeping the tables on one existing computer at their site (aka: the server) and keeping the application (forms, reports, etc) on their network, and 'linking' from application to tables. (I may have gotten these specifics a little fuzzy, but I have the gist of it). Without getting a full-fledged SQL-Server. It sounds like SQL-Server is the way to go, but I'd like to read up on the reasons a bit more so that I can be more knowledgable. Any suggestions from anyone on books/articles/web sites to gather info?

Thanks once again!
Lori

 

[BNPMike]

Access works with all SQL databases. It works most naturally with Jet. Jet can be held on a separate server but it is not a server database. The clients always manage the data.

The next most natural database is Microsoft's SQL Server. Using this will tend to cause changes to your code.

To understand the ramifications of using MS SQL Server, search the Microsoft site. There is a whitepaper on the pros and cons.

After that you can use any database that supports ODBC or has something that can work with ADO etc.

I think you need to understand what "IT Liaison" are trying to achieve.

 

[jsteph]

Lori,

The application (forms, reports) exist in one place, the tables exist on another?

Yes. And this can also be the case with Access-only, and it's of course the only choice with sql-server and Access.

For the Access-only solution, you'd just have 2 Access .mdb files, in one you'd only create tables, in the other, just forms, etc. And in the one with forms (called the 'front end'), you link the 'back end' Access tables. Now, let's assume you've got an existing app with this set up. You could create the same tables in sql server (or just import them, data and all, to sql server with the DTS utility) and link to the sql-server tables instead. The benefits are much larger capacity, and potentially much greater performance.

Depending on what kind of code already exists, there may be very little coding changes necessary. Higher performance can be gained by making coding changes specifically for the sql tables, but in the simplest scenario, it's possible that no changes at all are needed.

MSDE, as I'd mentioned before, is a confusing middle-step, and I've never used it and I generally don't recommend it on principle, so I'd stick to keeping the options simple--Access back-end or sql-server back end, either with an Access front end.
--Jim