Access Front End & SQL Server Back End Connection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a networked application using Access as a distributed front
end and SQL Server as the back end database. I can create both the SQL
Server database and the Access forms etc. no problem.

I need a clear explanation of which is the best method to make a connection
between the 2 to allow data to be entered and read in the Access forms - so
far I have only been able to get read-only access to the tables.

I have spent the last 2 days looking at web sites with code examples and
upsizing instructions but nothing clear and straight forward. Please can
someone point me in the right direction.

Thanks for your help.

Andrew
 
There isn't a simle answer to the "best" architecture: linked-table .mdb or
true client/server .adp. Either method will yield a highly stable, scalable,
flexible, and powerful application. A .mdb uses Jet over SQL Server
(performing querying on the user's computer, and allowing local tables that
are user-specific and providing the best heterogenous datasource processing
I've ever seen), while a .adp doesn't even load Jet, and essentially you're
using MS Access as a developer toolset on SQL Server (the best, might I add;
if a little memory-hungry).

Either method will not restrict you to read-only access. If you are using a
..mdb, and you linked in the tables, did you provide MS Access with the Unique
Index on the table? When you link in a SQL Server table, it asks you for the
unique index. If you press "Cancel," it won't create this "pseudo-index,"
and you're table will be read-only.

If this isn't the problem, I'll need more specific information on how you're
using the database.

David Atkins, MCP
 
Thanks Dave

I am an Access developer who is trying to get to grips with SQL Server. I
am familiar with creating split front-end/back-end .mdb applications.

I have several fairly small databases to create. They will be for about 5/6
simultaneous users with a low volume of traffic. I want a SQL Server back
end with a distributed front end. It seems sensible to use Access for the
front end as I know it and it is available.

I am not trying to migrate existing databases so I simply want to get to
grips with the options and settle on a good approach to making the connection
between the two. There are lots of sites with code samples, but few of them
explain what are the advantages and disadvantages of each method (e.g. DSN
vs. DSN-less) or when is the best time to make the connection (when opening
the application; when opening a form etc.) or open a recordset. You mention
linked-table .mdb or true client/server .adp - I am in a position where I
could choose either, though I have assumed that .adp is better.

I created a connection using the Access connection wizard (though I have a
feeling it would be preferable to use code) - this is what gave me the
read-only connection. I have since re run the wizard and am now able to
write - I have no idea why it worked 2nd time but not 1st as I didn't do
anything different!

This is a long winded way of saying I need more information, where is the
best place to go?

Thanks

Andrew
 
I'll go out on a limb here by speculating you would see nominal differences
between the two methods (i.e., ODBC DSN-based linked tables .mdb vs. .adp).

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., easy user-specific
tabling, heterogenous data source linking, etc.), then stick with an .adp.

David
 
Thanks David

I have the 1st database working a treat. I have decided to buy a book on
the subject to inform myself of all the issues.

Andrew
 
Back
Top