Hello,



I have been developing Access DB solutions for a while now. While I
would not call my self an expert, I do know a good bit about coding and
table design.

Most of my applications has been one to user DB apps, were I have a
front end DB that links to another access backend DB.



Well, today, I have been tasked in developing a database application
with a SQL backend and Access front end.



I have been reading a lot about creating a data layer and some of the
performance pitfalls associated with doing such things as setting the
record source property of a form to a table.



I was wondering how some of you experts construct a data layer in access
for a SQL environment.



I was thinking about creating functions for various views of data.



Something like .



MODULE DataLayer



Function rsInvoiceDetail() As ADODB.Recordset



    Dim cmd As New ADODB.Command

    conn.ConnectionString = "Enter Connection String Here"

    conn.Open

    cmd.CommandText = "spGetInvoiceDetail"

    cmd.CommandTimeout = 0

    cmd.CommandType = adCmdStoredProc



    Set rsInvoiceDetail = cmd.Execute



End Function



Then on the OnOpen Event of a form put something like



            Dim rsSubFormInvoiceDetail as ADODB.Recordset

            Me.recordsource = rsSubFormInvoiceDetail





If you did something like this, then the form at design time would not
have a schema for which to bind control's control source properties to -
unless you did it all in code.

This approach seems to be code heavy and I am not sure I like it.



I am starting to fiddle with An access ADP were it puts the SQL tables
under tables, and views and functions under queries.



Another concern I have about doing this in SQL - is someone opening up
the back end to the access DB and fiddling with the tables and such,
Especially if we use an ADP.

We do plan to have security in the database, but it needs to be based
upon a persons NT Network login and specific permission located in a SQL
Table. We have that part worked out (ie using NT login to grey out a
button for a person who doesn't have access to use that function on a
form). However, we need to secure access to the backend in such way  -
that we can continue to use the users NT login ID match against security
settings in a SQL table - without theuser having access to the tables
themselves.



To insure this, I am thinking that we cant have table links at all nor
use and ADP database, which brings me back to the data layer example
above.



Anyway,

If you're willing to share your data layer approaches - it would be much
appreciated.



Thanks in advance for any insight you can give.

SQL Server vs the Competition
Date Only Data Type
Time Calculation - NEED HELP PLEASE!
Array?
10 seconds for MoveFirst on a rowset?