"minimal" database front end

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sirocco - 23 Sep 2004 23:20 GMT

What utilities, i.e. modules, do I absolutely need to run the database in a
multi-user environment? I plan on upsizing my Access 2003 database to SQL,
but keeping my front end as an mdb file, and since DAO can be a b*t*h to
convert to ADO, will just strip it out and put it back in line by line. But
there's probably a basic set of utilities or code that's needed for even the
most basic operation. What utilities/code, i.e. modules do I need? Is
there a reference or book that answers this question directly? I have
books but they're so full of theory, and code that doesn't work. Many
thanks in advance.

Reply to this Message

John Nurick - 24 Sep 2004 06:43 GMT

Hi Sirocco,

This depends entirely on what you want the database to do. If you're
talking about "modules" as used in VBA, there are many - admittedly
simple - databases that contain none. Have you read the upsizing white
paper(s) in the Microsoft knowledge base, and checked out some of the
websites with upsizing information?

>What utilities, i.e. modules, do I absolutely need to run the database in a
>multi-user environment? I plan on upsizing my Access 2003 database to SQL,
[quoted text clipped - 5 lines]
>books but they're so full of theory, and code that doesn't work. Many
>thanks in advance.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Sirocco - 26 Sep 2004 03:22 GMT

You state "there are many - admittedly simple - databases that contain
none". Even in a multi-user environment with a SQL back end? Come on,
could it be that simple? My current MDB front end includes a module with
a bunch of code (written by someone else) to relink the tables that are in a
mdb file (each time the front end is opened). Is this "manual" linking,
defined explicitely by VB code, only necessary with a mdb back end and
therefore redundant after I place my tables on SQL? Or is this module I
described simply a ghost of some kind, just there to scare me? What about
the business with DSN or "DSN-less" connections? My impression is that the
source of the data needs to be defined somehow, even if the data is on SQL.
How is this done? Again, many thanks in advance.

> Hi Sirocco,
>
[quoted text clipped - 18 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 26 Sep 2004 07:57 GMT

Linked tables as such don't require any code. The code you mention is
only there so the location of the back end can be changed without anyone
having to manually re-link them (e.g. with the Linked Table Manager).

>You state "there are many - admittedly simple - databases that contain
>none". Even in a multi-user environment with a SQL back end? Come on,
[quoted text clipped - 34 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Albert D. Kallal - 24 Sep 2004 07:26 GMT

> What utilities, i.e. modules, do I absolutely need to run the database in
> a
> multi-user environment?

Just the standard access 2003 install should be fine.

> I plan on upsizing my Access 2003 database to SQL,
> but keeping my front end as an mdb file, and since DAO can be a b*t*h to
> convert to ADO, will just strip it out and put it back in line by line.

Hum, ADO is easy, but I see no reason to convert code from DAO to ADO that
works fine as is.

in other words, sure ADO has better support for sol server, but really,
there is little, or no advantage gained by converting DAO code to ADO code.
Why are you converting code from DAO to ado? This conversion will solve
nothing, and in generally will not change performance either.

> But
> there's probably a basic set of utilities or code that's needed for even
> the
> most basic operation. What utilities/code, i.e. modules do I need?

Hum, not sure of the above question. If you are using ms-access to link to
oracle or ms-sql server, or a JET file share, the libraries and code used is
the same.

The only additional thing you need installed on the client pc is going to be
the odbc driver for your database server. (in your case, the odbc drivers
for sql server).

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Reply to this Message

Sirocco - 26 Sep 2004 03:25 GMT

You say "Just the standard access 2003 install should be fine". But...what
is that? I've never hooked up a front end to SQL before.

Thank you again.

> > What utilities, i.e. modules, do I absolutely need to run the database in
> > a
[quoted text clipped - 32 lines]
> pleaseNOOSpamKallal@msn.com
> http://www.attcanada.net/~kallal.msn

Reply to this Message

Albert D. Kallal - 26 Sep 2004 03:55 GMT

> You say "Just the standard access 2003 install should be fine".
> But...what
> is that? I've never hooked up a front end to SQL before.
>
> Thank you again.

Ah, ok..I see the problem here!!

When you asked what is needed to use sql server with ms-access, the answer
is just ms-access! You don't need to install anything more!

So, when you asked what is needed to use sql server, the answer is just
ms-access. If you are asking how to convert a existing application to work
with sql server, then you are asking a VERY VERYdiffernt question here!

You see, you can create what is called a access data project (ADP). This
kind of ms-access system works directly with sql-server. If you build a ADP
project, then ZERO needs to be done, since from day one it worked with sql
server. So, if your old application is a ADP, then it already works with
sql-server.

However, migrating, or "up-sizing" a existing application to sql server is a
different matter, and this will also requite some changes and new knowledge
on your part. This is simply a given. What things you need to change
generally can only be realized when you learn more about sql server (and,
also ms-access!).

You can do some reading on upsizing here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Reply to this Message

Sirocco - 26 Sep 2004 03:34 GMT

You state "Hum, ADO is easy, but I see no reason to convert code from DAO to
ADO that
works fine as is.". My impression was that, at least in regards to
reading/writing to recordsets, that VB must be ADO compliant. Have you
worked with an mdb front end that uses a SQL source that only has DAO
compliant code? My impression was NOT that ADO was simply more efficient,
but that it was, in fact, absolutely necessary. Or maybe that was the
case in 2003 but not now?

Many many thanks.

> > What utilities, i.e. modules, do I absolutely need to run the database in
> > a
[quoted text clipped - 32 lines]
> pleaseNOOSpamKallal@msn.com
> http://www.attcanada.net/~kallal.msn

Reply to this Message

Albert D. Kallal - 26 Sep 2004 06:00 GMT

> You state "Hum, ADO is easy, but I see no reason to convert code from DAO
> to
[quoted text clipped - 3 lines]
> worked with an mdb front end that uses a SQL source that only has DAO
> compliant code?

Yes, I used dao and linked tables to sql server many times now. It works
fine.

> My impression was NOT that ADO was simply more efficient,
> but that it was, in fact, absolutely necessary. Or maybe that was the
> case in 2003 but not now?

No, that is a wrong impression. You can use access 97 which don't even have
ado, and it work just fine to sql server. And, for newer versions, once
again, you do not have to use ado at all. Both approaches work fine, and
even in a2003 you can still use DAO with sql server.

So, dao is a good choice for EXISTING applications with a whole bunch of
code. In many cases, little, or no modification is needed to the code to
work with linked tables to sql server.

I mean, sure, if you are writing a whole bunch of new code to work with sql
server, or a new application, then you might consider using ado. However,
DAO works fine, and in most cases little if any performance difference will
be noticed.

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Reply to this Message