Best design for Access 2003 FE/ sql BE. Need critiques

[rhdyes]

I am finally forcing myself into the Access/Sql server world. I am an experience Paradox programer starting with version 7 up to version 11.
Its a very robust network database environment. I have very mature applications running in it with sometimes up to 30 users in it with no problems. I have interfaced with DB2, Microsoft Sql, Mump and Postgresql with it. I am saying all this to give an idea of my background.

I am now looking at this environment to move my applications to over time.
These are my ideas for this programming environment.
Because of my background I have made use of many temp tables on local machines to speed up my databases. This was easy in Paradox because they are separate db tables within the file system. I like this and want to keep that flexibility and power. Because of this I have decided to make Access mdb file as my database. All my tables would be empty and when my users launch the application it will actually be a local copy on each machine. From there I thought I would have a sql back-end and my users would interface with sql through nothing but stored procedures. I would do CRUD this way and my security. I would populate the access tables on the users local machines as needed. In all my one to many relationships my master tables would all have a time stamp field on it so I can make the comparison between the record on the local machine and the record on sql server before any data changes. The only thing that would be on the local databases would be tables used for such things as departments and anything other values that would be in drop down lists on my forms. I would also have local tables that would be populated and emptied for temp tables for reports and the like.
So my question is this. Is this a good strategy and if not please shoot it down and give me better ideas. Remember I am limited to using just Access 2003 and MS SQL 2005. All analysis would be welcomed.

Thank you all in advance.

 

[gemma-the-husky]

probably better posters than me to answer this

but ... try it - if it works in paradox, i am sure it will work equally well in access.

i'm not sure you have to do it this way, but you probably come to this having a much better SQL skill set than most access developers - if your backend already does a lot of the work, then it makes sense to try and make use of it.

 

[dportas]

In SQL Server it is generally advantageous to use stored procedures for data access unless you have a good reason not to. As to the rest of your strategy, it sounds like you are struggling to make the most of an Access solution where you don't really need it. Is there some reason why you aren't considering .NET for this?

 

[rhdyes]

dportas I have not. You think .net would be better?

 

[dportas]

.NET is the most widely used development platform on Windows. It's also the best supported by other applications and tools and it has many features to help you build high performance database applications. If you don't need the desktop database features of Jet/ACCDB then I think .NET is a better choice for developing any client server application.

 

[Banana]

As someone who has worked in both worlds (Access and .NET), I'm inclined to say that Access actually makes much more sense as a front-end client in terms of Return On Investment and Time To Market. The list of things I can't do in Access but can do in .NET would be very short and mostly irrelevant to the world of data-centric application.

Furthermore, considering that I can write .NET library to extend Access, it'd actually still be cheaper to develop in Access and write .NET library to fill in the gap than to go full-tilt in .NET.

The biggest advantage of Access is that you get a free database engine, which makes it quite trivial to maintain local tables, perform reporting services and thus offload some load from server to the client, increasing the concurrency and scalability. Same could be accomplished in .NET but not without the ease of use and rapid application development.

Regarding the idea of having empty tables... I'm not sure why it should be empty. Normally, for the lookup tables that doesn't change too frequently, I save as local table within the Access while maintaining a master copy on the backend. Write in some mechanism to truncate & reload for updates which Access can check at startup or at shutdown or perhaps by a scheduled job. It's also possible to sync rather than truncate & reload but the logic is also more complicated... I only bother if it's actually warranted.

It should be clarified, though - the biggest advantage of Access comes in using its bound form. Using unbound forms would really defeat the whole point and at this point, I'd be saying go to .NET. However, for most parts, bound forms can be used to good effect and enjoy great performance provided the queries are well written and so forth.

If you really want to use stored procedures, you can use ADO to bind the form to resultset of a stored procedure, and if the conditions for updatability are satisfied, the users can edit in this form as well. However, in my experience, I usually use DAO 95% of time because most of time, the forms are one-to-one to the table (subforms are used for where there's a master-detail scenario) but I truly appreciate ADO when I do use it in the 5% of time when I need stored procedures to encapsulate some complex logic and still use a single form for users' interaction.

Last thing. Be aware that in SQL Server, TIMESTAMP does not mean timestamp. It's actually a binary data... a ROWVERSION is more accurate description. It is still useful as an indication of when a row was changed but the binary data will only tell you in what order a particular row was changed (e.g. every changes you make to a row within a table, the counter increment by one). If you really want time stamp, you'd have to use DATE/TIME with appropriate default value.

For more high-level information on how to use Access as a front-end client... there's a guide.

I hope this gives you a start.

 

[dfenton]

I would say the idea of using empty tables is crazy. Just use the SQL Server data directly, with ODBC linked tables.

All of my apps have two back ends, the shared back end tables, and a local tmp.mdb for the temp tables that is stored in the same folder as the user's front end. This seems to me to be the simplest way to implement what you ask about with none of the fussiness of copying down data and then having to worry about posting it back to the server, and having to resolve conflicts, etc. I just can't see one iota of benefit from doing it that way and it's a helluva lot more work.

 

[rhdyes]

You guys have given me a lot to think about. I think I am going to stay with the Access and work in some of the ideas. dfenton I like the idea of the tmp.mdb. I will experiment with that some.
Banana I am using the datetime field. I read about what you explained.
I have to get my arms around using some of these concepts. It is a different environment that's for sure.