Jim Lawrence
accessd at shaw.ca
Thu Jan 31 20:22:45 CST 2013
Hi John: Since, I first started with MS Access, back in 1997 days, I have rarely used an Access BE DB...except for small local transactions. We argued about that subject loud and long as I recall. Your concern was that there was no protection from one user over-writing another. I kept saying the SQL server took care of it. Now I am sure you are aware of that. My method was always to use stored procedures and pass parameters, never download any more data that absolutely necessary and always let the SQL server do all the data manipulation. The Access FE was only for presentation and reporting. It is not ugly, but you will have to give up most concepts of tight data binding. ADO-OLE and MS SQL server handles it all, beautifully and very fast. I have heard of datasets and things like that but have never used them...they are not necessary. If you start using datasets it will be extremely difficult for you, when they eventually expect you to move everything to the web. ;-) Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W Colby Sent: Thursday, January 31, 2013 11:07 AM To: Access Developers discussion and problem solving Subject: [AccessD] Upgrade Access to SQL Server I am being asked to upgrade Access FEs which have quite complex SQL Server BE tables, plus (apparently) some data from those tables pulled down to the FE and stored there over time as the user processes the data in those local FE tables. They want to move those local tables to SQL Server. My question is, is there an accepted method for providing this kind of table out in SQL Server? IOW the structure is there, but the data in the table (as seen from the FE ) belongs to that instance of the FE. We place tables local to the FE exactly for this purpose, to make it local to that specific instance of the FE, on that specific user, on that specific machine. It seems that if I am going to do this in SQL Server then I will need to add a "machine ID" kind of FK in the tables as I upsize them to SQL Server, then in the Access Application somehow get filtered datasets. This sounds ugly. -- John W. Colby Reality is what refuses to go away when you do not believe in it -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com