March 16, 2010 at 3:22 pm
Dear all,
I need to write a small application and I would like to use SQL Server (2008) at the back because this is powerful and that's my expertise.
I wanted to use MS Access at the front because it's easy/flexible to create forms and reports.
There will be 20 users max in total and certainly not at the same time.
For security, I use windows authentication and sql views to control who can see/update what.
I stumbled for a while on a weird problem where when I try to update a record through the view, MS Access complained (wrongly) about some optimistic locking issue.
Using Profiler, I discovered that MS Access was using a WHERE clause with the Primary Key (declared when linking the view) as well as all columns exposed in the query. So far, why not...
Unfortunately, MS Access was using a value 0 for a column of type BIT NULL that was actually NULL!
Of course, there was no match, hence the bogus concurrency issue.
The quick fix I have found is to declare that BIT column NOT NULL...
Here is my question: Is it a good idea to use MS Access as a front-end to SQL Server or am I likely to stumble after other bizarre things like the one I just encountered?
Many Thanks
Eric
😀
March 18, 2010 at 11:06 am
You will run into several issues if you are going to use linked table connections, if you make any change (ie new column or change a datatype) the linked table will have to be dropped and recreated in Access. This shouldn’t be an issue (please tell me this won’t be an issue) but there is a column limitation of 255 columns in a linked table in Access and more than that won’t show up. There are some other things, but they don't come to mind.
I have inherited some Access front end apps to SQL server back end data and some day’s it gets interesting. I would suggest a different way if you have one, but if it’s all you have then so be it.
Thanks,
Neal
March 18, 2010 at 12:48 pm
I think I'll give MS Access a shot because my initial requirements are extremely simple.
It's an initial job with a new customer and I might change the front-end later to C# for instance if it's all worth it.
Thanks for the feedback.
March 21, 2010 at 8:32 am
Just for the record, I have discovered about MS Access Projects as opposed to database and I really like what I see!
March 22, 2010 at 12:36 pm
Which version of Access are you using?
If you are using SQL 2008, I am not aware of an Access version out yet that uses ADP.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
March 23, 2010 at 8:17 am
I use 2003
March 23, 2010 at 10:10 am
Sorry.
What I was trying to say is:
To my knowledge there is no version of Access that can connect to SQL 2008 using ADP.
Access 2007 can connect to SQL 2005 using ADP.
Access 2003 would have to use ODBC connection and all views and stored proceedures would have to be created and maintained in SQL.
Are you using Access 2003 ADP and are you able to create and modify views and SPs using the Access Frontend?
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
March 23, 2010 at 12:07 pm
That's funny because I just connected to SQL Server 2008 from access ADP 2003 on my machine.
I did not notice any problem.
I flip back and forth between Sql Server 2005 and 2008 because my customer uses sql 2005 but I prefer 2008... and I have got deployment tools so that's not problem keeping the two in sync.
😛
March 23, 2010 at 4:29 pm
When you are using your Access 2003 ADP Frontend connected to the SQL 2008 server:
1) Can you create a view from the Access Frontend?
2) Can you modify an existing view from the Access Frontend?
3) Can you change a table definition? (change field type, add a field, or removed a field)
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
March 23, 2010 at 5:22 pm
No I can't effectively do anything like that but I do all my DDL through script files anyway (hence neat deployment) so it did not bother me...
March 23, 2010 at 5:28 pm
Just as I thought.
Access is a great frontend development tool.
I have been using Access with SQL 2000 for over 7 years.
I have developed several applications using Access mdb's and mde's for over 18 years.
Good luck with you future development.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
March 24, 2010 at 3:57 am
Since you seem to be an expert... may I ask you a couple of questions?
I used Access in anger back at version 1.1 and 2... while ago!
I consider myself an expert in SQL Server and I can write decent .Net apps...
For this particular project, I need an "easy" front-end and I will have 20 users max which is why I thought of Access.
What would you recommend in terms of menu system?
Should I just go for a "base form" with one button per report and form, or is it possible (and how difficult?) to make something a bit more dynamic that could be configured via tables in the database?
Many Thanks
March 24, 2010 at 10:28 am
I generally use a Tabbed form with single buttons for opening the forms and reports.
Each tab is used to group the reports and/or forms by classification.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
March 24, 2010 at 10:37 am
Thks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply