Convert Access to SQL Server, but keep Access front end?

[cmay]

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

 

[Guest]

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.

 

[cmay]

Thanks Bill.

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.

 

[Guest]

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.

 

[Rick Brandt]

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.

 

[cmay]

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