Re: Upsizing questions

> 1. Should I keep using the mdb as frontend or switch to an adp (access
> project) instead? Any reasons?

I have not been developing in Access for about 2 years now. In 2002 I
converted an Access mdb to ADP using Office 2000 then Office XP
with SQL Server 7.

Back then and now I am pretty sure nothing major changed (I might be wrong)
as most of the experienced Access developers would not bother switching
from MDB to ADP. They did not see any advantages of doing so.
Now I would not argue with that point as back then I did a lot of research
on the same question but I decided to switch to ADP. The reasons I did
so is because since I was switching to SQL Server as the backend, I
preferred to have my Access database directly connected to a SQL
Server backend instead of relying on ODBC and Linked tables.
I had to learn ADO too to make the data communication as I was
using unbound forms everywhere (which basically some people would
say it would defeat the purpose of using Access altogether and would
recommend using VB instead).

I would suggest you stay with mdb for the time being and not waste
your time with ADP. If you find the time to redo your frontend then
I recommend you use VB .NET instead of Access.

I also suggest you post the same questions here on
comp.databases.ms-access and see what the latest story is on
ADP/MDB and VB.NET.
I haven't followed the Access community for a couple of years now
so I am not sure where Microsoft Access is heading towards in the
future.


> 3. Is it possible for access frontend to connect/link to two separate SQL
> Server dbs, main and archive at the same time?

Sure, if you use VB Code you can open as many connections as you want
to other SQL Server databases. You don't need to think you are limited
by the ODBC Linked Tables connection to one SQL Server database.
Nothing prevents you from creating a new connection using code, accessing
the data and then closing the connection.

Good Luck with your research and decision.



.

Relevant Pages

  • Re: ADP vs. MDB?
    ... One major problem we had was that in an adp all calls to the database seem ... to run in the same connection context (i.e. adp opens one connection to the ... revert to an .mdb, open and close my own connections as required, implement ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Access Front End & SQL Server Back End Connection
    ... I believe that if you're using SQL Server, unless you have a need for the ... additional capabilities afforded by use of a .mdb (e.g., ... tabling, heterogenous data source linking, etc.), then stick with an .adp. ... > grips with the options and settle on a good approach to making the connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... I don't know about performance with ADPs; but I have an MDB (with an MDB ... general, seems to have some problems with performance, whether ADP or MDB. ... I'm using a fairly complex database using and ADP ... the front end over a VPN connection. ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Access Front End & SQL Server Back End Connection
    ... I am an Access developer who is trying to get to grips with SQL Server. ... grips with the options and settle on a good approach to making the connection ... linked-table .mdb or true client/server .adp - I am in a position where I ...
    (microsoft.public.access.modulesdaovba)
  • Re: From .mdb to .adp database
    ... I decided to go on with adp project, and i have created a store ... But queries are different stories. ... In SQL Server, there are Views, Stored Procedures, UDFs. ... Also, when you decide to use MDB front-end, you can choose use MDB ...
    (microsoft.public.access.adp.sqlserver)