Access FE to SQL advice

[roosn]

Hi

I am creating and using an access front end with a SQL server back end (which I manage thru access project), and I am wondering if I am losing my way.

I have some heavy tables which hold upwards of 60,000+ records. Before when I held it all on access, the database would swell up to 200 mb+ and used to grind to a near halt, now with the tables on SQL, it is much quicker, and no corruption problems so far. Also using SQL, I am forced to a better table structure.

I have linked the SQL tables thru to the access front end no problem and have queries running of the them, I find I can do more with access queries as they seem easier to construct and fault diagnose. This is especially true when using the ‘choose’ function. However, I am not sure if this is correct or intended way.

Therefore, in a good access front end, SQL server back end set up, should I

1) Where possible, put all updates, append, and delete queries as project stored procedures and pass them thru to the access FE

2) rather than link the tables from SQL to the access FE, and then make access FE queries to feed the forms, as I currently do, make the query views in project, and link them to access FE, and then make queries off these to the forms.

3) i note comments about using a WHERE function, should this be in SQL if possible

Ultimately, my thinking is that if I can make the project / SQL do as much as possible, it will be easier (for somebody else with better knowledge) to migrate the front end to another platform such as ‘asp’, if required.

Pardon my lay mans language, I need a course or a good book

 

[Pat Hartman]

You don't say whether you are using an .adp or .mdb. They are very different. I never use .adp's because I don't like their restrictions.

Regardless - EVERY form should be based on a query that includes a where clause to reduce the number of rows retrieved from the server.

 

[WayneRyan]

Roos,

As to the database bloat, if the original environment entailed import
of external data through ADO/DAO recordsets, then of course the Access
database will bloat. An .MDB, or in your case, your .ADP should bloat
also. In either case if you use recordsets to import/process large
amounts of data, your front-end will tend to increase in size.

In any case, your DATA now lives in SQL server. "Bloat" is no longer
an issue. Your data is now its own entity outside of your .MDB or
.ADP application.

The SQL server environment allows you to more tightly enforce the
definition of your data. I seriously hope that you have the Enterprise
Manager so that you can establish/monitor:

The Relationship diagrams (not much different than Access) to define
table joins and R.I. topics such as cascading updates/deletes.

The Rules which allow you to specify the datatypes/ranges for the
simpler types of data control.

The Triggers that let you invoke the T_SQL code and/or Stored Procedures
to perform data manipulations that the above don't encompass.

Ideally, you can define the above so that even if you had users who's
only interface to the data was limited to contact ONLY with the tables,
the data would be fine regardless of any mods.

If you could live with the SQL Server setup above for the data, then
as to deciding on a front-end.

For import/export of data to a third-party (Even Microsoft), Microsoft
Access (not .ADP) would be my choice. Compact/Repair on exit if "bloat"
is a problem, but it provides a great interface.

For providing a look into data, I'd still choose Access with a "bound"
form using a linked table. No reason to use some weird thing that uses
"unbound" forms.

Regardless of what you use as a front-end, your SQL server triggers/rules/
Stored Procedures should take care of your data. The front-end should
just move data, not try to manipulate it.

My first post in a while, tending to ramble on.

Basically, with your data in SQL Server.

If you are NOT close to the ideal "tightly bound to SQL Server", then use
Access (or .ADPs) and the logic in their forms as your interface.

If you ARE closely bound to the ideal "tightly bound to SQL Server", you
don't care who/what accesses your data. Choose the interface to fit your
internet/network/workstation needs.

Wayne

 

[roosn]

Hi Pat / Wayne

Thanks for taking the time to reply.

i have put some serious time in the last few days into learning the differences in query code between access and sql server.

therefore i have a front end of access mdb with a backend of sql server managed thru an adp

i am now creating queries in sql server thru the adp, and linking them to the front end mdb

from there i further query the linked queries using form values as the parameters, for example, i have a form with some combo fields, and a continuos subform.

the recordsource of the subform has parameters set by the values of the combo and this is where the WHERE clauses tend to take effect

i am really happy with the speed of data retrieval now, however, it is nice to know if i am doing all this in the correct principle of database design

once again, many thanks for your input

 

[thart21]

Clarification needed

I am moving to a SQL server backend on our network. I have been reading the posts on .adp versus .mdb and the book MS Developer's Guide to SQL Server but couldn't find specifically how to set this up (Or, more likely, I'm just not getting it). I have successfully connected the .adp and SQL and created the tables.

I want to maintain an .mdb as the front end. So, do I connect to my SQL BE through the .adp to manage my tables and then connect my .mdb to the .adp or do I connect the .mdb directly to the SQL db? Just trying to figure out how to get all 3 connected together.

Also, should I always use unbound forms when using SQL db's? My current forms have many combo boxes and a subform.

 

[Pat Hartman]

You don't need an .adp at all unless you don't have Enterprise Manager to manage the SQL Server database. Current versions of the Access .adp cannot manage the new SQL Server 2005 that was recently released (Access can link to the new version and work with the tables, it just cannot create or modify new tables/objects in the new db format) and word is that when Office 12 is released next year, the .adp will still not be able to manage an SQL Server 2005 database. It will only be able to link to it to add/change/delete data.

The .mdb connects directly to the SQL Server database. Access does not allow one database to link to the linked tables in another database. The link always goes directly to the physical data store.

 

[thart21]

Thanks Pat - I do have Enterprise Manager and I think I'm working off of SQL Server 2003 on our network so I'm going to ditch the .adp, and link my .mdb tables to the SQL db.

One more question. My main reason for going to the SQL db backend is that my Access tables were continuing to corrupt and randomly assign new records an existing autonumber id. I got tired of fixing it and am hoping that the SQL tables will fix this issue (??) Or, if I understand this correctly, by going this route the Jet engine is still involved and, if that is what was causing the problem, it still will be a problem in the SQL db?

Appreciate your thoughts on this.

Thanks for the help.

 

[Pat Hartman]

Jet is involved but only with passing through queries to the back end database.

 

[Banana]

Can anyone clarify if this will work:

Say I want to use some JET function but need to work with SQL backend.

Can I just create a simple query with the criteria to pull a reasonable set of data, then use the query as a basis for local query for JET to work with, complete with JET-specific functions without any snag?

 

[Banana]

Bump-

Also, while I prefer to use DAO whenever Access will be using JET. Will it continue to work fine for dealing with local queries pulled from the server?

I'm also wondering if it's acceptable to use ADO for communicating with SQL server while using DAO for data manipulation or modification, or does it have to be ADO all the way?

 

[Banana]

Surely someone here has used Access as FE with any SQL server as BE and can answer the question above?