|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have been given an old Access application where the Access is not
only the database, but the front end as well.
There are forms, reports, and several buttons simply display the raw
tables, in what appears to be Access's native data table display.
I was asked to move the data into SQL Server, but keep the front end in
Access for now.
I know if this were an app using ADO, I could go in and change the
connection strings or whatever, but is it possible to have Access be
the front end, pointing to a SQL Server database? Where would I go in
Access to set the "data source" if this is even possible?
Thanks
|
|
In most cases, all you need to do is create an ODBC file and link the tables
in your front end. Everything else can stay the same. Access treats the
linked tables like regular Access tables.
File>Get External Data>Link tables.
Then select ODBC (the last File type in the drop down list). Use the wizard
to build a new DSN file or use an existing one.
Each table or view should have a unique index or you will not be able to
edit the records.
There are a few gotchas, but nothing earth-shattering.
--
Bill Mosca, Microsoft Access MVP
"cmay" wrote:
[Quoted Text] > I have been given an old Access application where the Access is not
> only the database, but the front end as well.
> There are forms, reports, and several buttons simply display the raw
> tables, in what appears to be Access's native data table display.
>
> I was asked to move the data into SQL Server, but keep the front end in
> Access for now.
>
> I know if this were an app using ADO, I could go in and change the
> connection strings or whatever, but is it possible to have Access be
> the front end, pointing to a SQL Server database? Where would I go in
> Access to set the "data source" if this is even possible?
>
> Thanks
>
>
|
|
Thanks Bill.
Bill Mosca wrote:
[Quoted Text] > In most cases, all you need to do is create an ODBC file and link the tables
> in your front end. Everything else can stay the same. Access treats the
> linked tables like regular Access tables.
>
> File>Get External Data>Link tables.
>
> Then select ODBC (the last File type in the drop down list). Use the wizard
> to build a new DSN file or use an existing one.
>
> Each table or view should have a unique index or you will not be able to
> edit the records.
>
> There are a few gotchas, but nothing earth-shattering.
> --
> Bill Mosca, Microsoft Access MVP
>
>
> "cmay" wrote:
>
> > I have been given an old Access application where the Access is not
> > only the database, but the front end as well.
> > There are forms, reports, and several buttons simply display the raw
> > tables, in what appears to be Access's native data table display.
> >
> > I was asked to move the data into SQL Server, but keep the front end in
> > Access for now.
> >
> > I know if this were an app using ADO, I could go in and change the
> > connection strings or whatever, but is it possible to have Access be
> > the front end, pointing to a SQL Server database? Where would I go in
> > Access to set the "data source" if this is even possible?
> >
> > Thanks
> >
> >
|
|
I tried it many times and there were very little problems with ODBC. Some
fine-tuning here and there. But....
Any reason why you're doing this? Often, people think that doing this will
speed up the application, nothing could be further from the truth. When all
you do is upload the tables, the application actually becomes slower! Keep in
mind that the queries still execute at the workstation and not on the server.
So you're not really taking advantage of SQL Server's client-server
architecture.
"cmay" wrote:
[Quoted Text] > I have been given an old Access application where the Access is not
> only the database, but the front end as well.
> There are forms, reports, and several buttons simply display the raw
> tables, in what appears to be Access's native data table display.
>
> I was asked to move the data into SQL Server, but keep the front end in
> Access for now.
>
> I know if this were an app using ADO, I could go in and change the
> connection strings or whatever, but is it possible to have Access be
> the front end, pointing to a SQL Server database? Where would I go in
> Access to set the "data source" if this is even possible?
>
> Thanks
>
>
|
|
Ray wrote:
[Quoted Text] > I tried it many times and there were very little problems with ODBC.
> Some fine-tuning here and there. But....
>
> Any reason why you're doing this? Often, people think that doing this
> will speed up the application, nothing could be further from the
> truth. When all you do is upload the tables, the application actually
> becomes slower! Keep in mind that the queries still execute at the
> workstation and not on the server. So you're not really taking
> advantage of SQL Server's client-server architecture.
It is not correct that all query processing is done locally when using
Access queries against ODBC linked tables. The vast majority of the work is
still sent to the server for processing. Depending on how the query is
designed you can get anywhere from 100% of the processing done on the server
to 100% of the processing done locally after pulling entire tables over, but
the former is much more likely than the latter.
It is true that you have to change to Stored Procedures and/or Passthrough
queries to *guarantee* server side processing, but that is not the same as
saying that all standard queries will be processed locally.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
I am not exactly the one calling the shots on this project, but I
believe the reason we are moving to SQL is mostly for a way centralize
the data as right now several people have their own copy of the data,
and probably more importantly I think this is a first step before a
rewrite of the UI.
But as I said... I am not the one calling the shots :)
|
|
|