Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Access 2007 front end to SS2K8 problem Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 8:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 23, 2014 9:42 PM
Points: 48, Visits: 9,454
With all the previous post it sounds like you are well underway with a solution - many people using one MDB would have been the cause of your data display issues as each new user would have been changing the recordsets of the previous.

But just in case, I thought I would expand a little on the method of connecting of Access to SQL Server.

1) Set up an AD group and assign to it the users who are to access this database. This enables the use of Windows Authentication as a security method rather than SQL Server;

2) Create an AD login for this group in SQL Server, mapping the login to your user database and maybe tempdb (for the benefit of your stored procedures).;

3) Grant appropriate permissions to the objects in the database eg tables will need select, insert, update and delete; stored procedures will require exec. You can do this manually if you only have a few objects, or write some SQl to do it for you - maybe keep it as a stored proc.

You can either continue to use the existing ADO methods in Access or convert to using ODBC and linking the tables. If you use ODBC you will need to set up a connection (using the ODBC administrator) on all PCs designated for access to the app.

All the best.
Post #1352562
Posted Friday, August 31, 2012 1:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:04 AM
Points: 59, Visits: 929
I frequently use this technique and never encountered this kind of problem. The sole difference is that I use DAO insead of ADODB as data library.

Note: ADP is not supported in the recent versions of MS Access (2007 and beyond).
Post #1352624
Posted Friday, August 31, 2012 6:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
rf44 (8/31/2012)
...
Note: ADP is not supported in the recent versions of MS Access (2007 and beyond).

Actually, ADP is supported in Access 2007 and 2010 (see for examplehttp://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/795d5b36-6d8e-43e2-9d17-1a168c197adb), but rumor has it that it will not be in the next version - see http://answers.microsoft.com/en-us/office/forum/office_home-access/access-2013-no-support-for-access-project-adp/762fcedf-fdca-4b18-a918-850646897dda?tab=AllReplies&page=1. Microsoft put out the word that it would not be enhanced some time ago, so the handwriting has been on the wall, but some people apparently weren't listening.

Wendell
Evergreen, CO


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1352779
Posted Friday, August 31, 2012 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:51 AM
Points: 141, Visits: 275

1) Set up an AD group and assign to it the users who are to access this database. This enables the use of Windows Authentication as a security method rather than SQL Server;
2) Create an AD login for this group in SQL Server, mapping the login to your user database and maybe tempdb (for the benefit of your stored procedures).;

3) Grant appropriate permissions to the objects in the database eg tables will need select, insert, update and delete; stored procedures will require exec. You can do this manually if you only have a few objects, or write some SQl to do it for you - maybe keep it as a stored proc.

==> actually I've done all that (1,2 and 3)


You can either continue to use the existing ADO methods in Access or convert to using ODBC and linking the tables. If you use ODBC you will need to set up a connection (using the ODBC administrator) on all PCs designated for access to the app.


My serious (but may be naive) concern about linked tables: in a "simple" Access app form controls are linked to tables (or through queries) by DYNASET, that would change table field value every time you change it in the control. Our current design (SQL Server / ADO) allows to update the record ONLY by firing event on the Access front end form (e.g. clicking button "Save Changes"). Am I wrong?
Post #1352807
Posted Friday, August 31, 2012 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:51 AM
Points: 141, Visits: 275
Another question (not related to the topic, or ... related?). When we load nvarchar (max) - a.k.a. memo into Access form text box or report field it is truncated. To overcome this problem we cast navarchar(max) as Text (just to remind, SQL Server Text datatype, not Access). Does anybody have better solution (keeping in mind that TEXT datatype is obsolete in SQL version after 2008)?
Post #1352815
Posted Friday, August 31, 2012 7:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:51 AM
Points: 141, Visits: 275
And one more: what is the best way dealing with "=deleted' on subform?
Post #1352818
Posted Friday, August 31, 2012 8:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
valeryk2000 (8/31/2012)
[quote]
....
My serious (but may be naive) concern about linked tables: in a "simple" Access app form controls are linked to tables (or through queries) by DYNASET, that would change table field value every time you change it in the control. Our current design (SQL Server / ADO) allows to update the record ONLY by firing event on the Access front end form (e.g. clicking button "Save Changes"). Am I wrong?

Actually, the change to the data in the underlying table in a bound (linked) form only changes when the Update event fires at the form level. Prior to that the data in the control changes, but the changes are not committed to the tables until the form is either closed or you move to a new record. Any changes to the current record can be cancelled by the user hitting the ESC key twice. If you choose to use an unbound form then you need to have ADO or DAO code that populates the controls on the form, and that saves any changes when the user clicks a "SAVE" button or some other suitable event occurs. The unbound form approach is appropriate in certain situations, such as a data entry form that requires storing data in numerous related tables, but the coding cost to create such forms, and the maintenance cost when changes are required, results in us using bound forms most of the time.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1352832
Posted Friday, August 31, 2012 8:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
valeryk2000 (8/31/2012)
Another question (not related to the topic, or ... related?). When we load nvarchar (max) - a.k.a. memo into Access form text box or report field it is truncated. To overcome this problem we cast navarchar(max) as Text (just to remind, SQL Server Text datatype, not Access). Does anybody have better solution (keeping in mind that TEXT datatype is obsolete in SQL version after 2008)?

The truncation of "memo" type data in Access has been a pain since day one. Most often, the reason is that the recordset is being sorted in some way in Access, and it is most often an issue in continuous forms. Is there an explicit reason for using nvarchar rather than varchar? UniCode is great if you need to store information in languages other than the "Latin" language set, but it requires twice as much space. If you are storing long documents, then the (max) parameter would be necessary, but otherwise the maximum of 8000 characters will suit most needs. If we get something larger than that, the ususal approach is to save the data as an object rather than as text data - larger documents typically contain formatting and other information that can't be stored in a raw text field. Bottom line - if you can avoid sorting the recordset, I think you will find the Access form behaves OK. If you can't avoid sorting the data, then you may want to resort to a pop-up form to let the user view and edit the data.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1352851
Posted Friday, August 31, 2012 8:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
valeryk2000 (8/31/2012)
And one more: what is the best way dealing with "=deleted' on subform?

Unfortunately, I don't know of anything that can be done when that happens. But that only happens on bound forms, which is to say that if you used an unbound form, that wouldn't happen. But as I noted in a previos response, subforms are almost impossible to do in a continuous unbound form. What that means is that the underlying data on the form has changed, and you shouldn't be seeing that if each user has their own copy of the database that they run.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1352857
Posted Friday, August 31, 2012 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:37 AM
Points: 185, Visits: 765
There is a known problem with Access linked to a SQL back end that can exhibit this behaviour.

If you have a bit column in the table that does not have a default value, Access will struggle.

Check the definitions for your tables - set the default value of any bit fields to 0, then update the table(s) to set the value of the bit fields to 0 where the current value IS NULL
Post #1352863
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse