InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:03:05 GMT20http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]WendellB (9/3/2012)[/b][hr]There's a common misperception that Access is an insecure system that is also unreliable. As long as you use the .mdb format, you have Access User Security. WendellEvergreen, CO[url]http://www.glmms.com[/url][/quote]ah, i have a file..no installation require that I use to get mdb passwords...sorry but its security is crap. I have a pretty good security procedure working for me right now with them though...anyways...this topic is not abt security...to the original poster...i noticed your first post is that the user sits and does nothing then suddenly its "=deleted"i think your connection timed out.Wed, 31 Oct 2012 16:17:06 GMTsdhanpaulhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote]When you say "=deleted" do you mean "#deleted"?[/quote]Certainly "#deleted". Thank youMon, 03 Sep 2012 10:52:25 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote] With a compiled and properly secured front-end your data is as secure as with any other system.[/quote]Compiled - is a key word. Our current wersion is not compiled - simple mdb. That's why all logics is hidden in SQL stored procedures that are called from the front end==================Thank you. We have a lot of rain on the East Coast of the country. So ... I enjoy fixing holes in my appsMon, 03 Sep 2012 10:50:08 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxI should say! Extended condolences are definitely in order. :crying:There's a common misperception that Access is an insecure system that is also unreliable. As long as you use the .mdb format, you have Access User Security. That feature can be used to manage what various users are permitted to see and do. If you are using an Access back-end for data storage, then there are issues with reliability and with the possibility of theft or loss of the data. But once you put that data into SQL Server on a physically secured server, then an Access front-end provides the fastest development path to a feature rich user interface. With a compiled and properly secured front-end your data is as secure as with any other system.My prescription for a frazzled and frustrated developer would be a nice long holiday weekend like we are having here, but on a beach or along a quiet mountain brook. (Bad planning on our part to be cutting over a new client this weekend. :crazy:) Hopefully your situation is beginning to settle down and behave as it should, and things will get better.WendellEvergreen, CO[url]http://www.glmms.com[/url]Mon, 03 Sep 2012 07:09:16 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]Afterthought - just out of curiosity can you give us a brief idea of what your application is for?Wendell[/quote]This is an application with sensitive patient and doctor information. They created an Access database 4 years ago and were constantly updating since then. It has a lot of forms, subforms, reports and subreports. At some moment it occured to the management that the data is not safe in Access. So they told me to transfer the data to SQL Server ... but keep the Access front end intact (I tried to suggest that VB, Java or Web front end would be much better - no way, management was very conservative). SQL statements on the front end would use real table names (as well as linked tables app) with a posibility to read or affect a back end data by a "unauthorized user". So I moved all logics (select, update, delete, etc) to the back end in stored procedures and triggers. This is a sad story in shortcondolences are accepted around the clock 24/7 :-(ThanksSun, 02 Sep 2012 06:50:01 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxHiWhen you say "=deleted" do you mean "#deleted"?If you do then I think you have an issue with the primary key on one or more of your tables. One of the common causes of this is the use of the bigint data type for your primary key.CheersSat, 01 Sep 2012 23:54:18 GMTProofOfLifehttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxThat is an easier case then as you know you are getting new data, so you can hide the subform control (set the visible property to No), get the data, and then unhide it by setting the Visible property to Yes.Afterthought - just out of curiosity can you give us a brief idea of what your application is for?WendellSat, 01 Sep 2012 14:50:05 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxI'll try. Users now use different files. The only case "=deleted " shows up - when user switches to another case and the subform is reloaded.ThanksSat, 01 Sep 2012 14:26:10 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxThe challenge in doing that is to know that the subform controls are displaying "=deleted" - it's not something I've ever attempted. If you can do that then you could hide the subform control until it has been populated. But that text popping up doesn't trigger an event as far as I know, so there's the problem. You could certainly try putting a bit a code behind the "Before Del Confirm", "On Delete", and "After Del Confirm" to display a simple text box message, but I don't think it will work. I presume you have solved the situation where multiple users were sharing the same database and clobbering the temporary table.Chris makes an excellent point - with SQL Server tables you want to make sure that the have a default value defined for bit fields, or you will have all sorts of trouble. Another thing you should also do is to give each linked table a TimeStamp field. Access is much happier when you do that - if you don't you tend to get ODBC error messages that another user has edited the record, and your changes cannot be saved.WendellSat, 01 Sep 2012 14:19:08 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxMay be just make the form invisible before it can be requeried?Fri, 31 Aug 2012 10:51:55 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxThere 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 NULLFri, 31 Aug 2012 08:36:54 GMTChris Quinn-821458http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]valeryk2000 (8/31/2012)[/b][hr]And one more: what is the best way dealing with "=deleted' on subform?[/quote]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.Fri, 31 Aug 2012 08:32:33 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]valeryk2000 (8/31/2012)[/b][hr]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)?[/quote]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.Fri, 31 Aug 2012 08:27:42 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]valeryk2000 (8/31/2012)[/b][hr][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?[/quote]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.Fri, 31 Aug 2012 08:12:07 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxAnd one more: what is the best way dealing with "=deleted' on subform?Fri, 31 Aug 2012 07:52:02 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxAnother 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)?Fri, 31 Aug 2012 07:49:50 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote]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.[/quote]==> actually I've done all that (1,2 and 3) [quote]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.[/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?Fri, 31 Aug 2012 07:37:26 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]rf44 (8/31/2012)[/b][hr] ...[u]Note[/u]: ADP is not supported in the recent versions of MS Access (2007 and beyond).[/quote]Actually, ADP is supported in Access 2007 and 2010 (see for example[url]http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/795d5b36-6d8e-43e2-9d17-1a168c197adb[/url]), but rumor has it that it will not be in the next version - see [url]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[/url]. 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.WendellEvergreen, COFri, 31 Aug 2012 06:58:01 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxI 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.[u]Note[/u]: ADP is not supported in the recent versions of MS Access (2007 and beyond).Fri, 31 Aug 2012 01:16:09 GMTrf44http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxWith 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.Thu, 30 Aug 2012 20:50:53 GMTProofOfLifehttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxOK. Let me try itThanks a lotThu, 30 Aug 2012 09:49:20 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspx[quote][b]valeryk2000 (8/30/2012)[/b][hr]Thanks. ==> If you are seeing the tables as read-only, chances are your userID in SQL Server doesn't have permissions to edit or append data.But there is no problem for me to get to SQL Server through current Acces front end. May be there is something that I need to change on the Access side? permissions on each table, or else? Please give me more practical details===========[/quote]In order to connect to SQL Server tables you must specify a specific login name - either SQL Server based, or Integrated Security (which I much prefer). Any user who wants to be able to edit data in a table must either be given explicit permission on that table, or they must belong to a group that has that ability. One solution we use is to make them a member of the dbOwner group, but that does have some downsides, as they can do pretty much anything they want to the entire database.On the Access side, unless you activate Access User Security, there is no permissions to set. (By default you connect as "Admin" and that user can do anything they want to the database.) However, the SQL Server table must have a primary key set if you link to it and want to edit it. Otherwise, Access treats it as read-only. The same is true of local tables (including temporary ones) unless you explicity create a primary key when you create them.Hope that helps.WendellEvergreen, COThu, 30 Aug 2012 09:43:02 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxThanks. ==> If you are seeing the tables as read-only, chances are your userID in SQL Server doesn't have permissions to edit or append data.But there is no problem for me to get to SQL Server through current Acces front end. May be there is something that I need to change on the Access side? permissions on each table, or else? Please give me more practical details=============> And regarding the shared front-end, that is generally considered dangerous. We always deploy the front-end to the user PC - that way if the front-end is somehow corrupted, we can simply replace the one on the PC.Yes. That's what we found as well. Or create folders for each user (we have only 4, but all of them are VIPs, e.g. hysterical :) on the share drive so they could enjoy there own file - in this case we do not need to go to each computer: just substitue old files with a new oneThanks againThu, 30 Aug 2012 09:01:06 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxRegarding your question about linked tables, and whether they can be written to, absolutely yes. We have a number of systems deployed that used linked tables that are updateable. In fact, we also link to views which are updateable. ODBC linked tables are the preferred method for using an Access front-end to SQL Server tables per Microsoft. If you are seeing the tables as read-only, chances are your userID in SQL Server doesn't have permissions to edit or append data.And regarding the shared front-end, that is generally considered dangerous. We always deploy the front-end to the user PC - that way if the front-end is somehow corrupted, we can simply replace the one on the PC. Otherwise, you have to get everyone out of the system so you can repair whatever has gone wrong. We learned that the hard way with some 80 PCs trying to share a front-end. It was a disaster, and we quickly changed to copying the front-end to the PC. That was in 1994. I think you will find that concept in nearly every best practice - if you want a bit more detail take a look at [url]http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18[/url]WendellEvergreen, COThu, 30 Aug 2012 07:14:57 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxRowan:==> Firstly, what exactly do you mean by a proxy table? Are these just links to SQL Server created using ODBC or some form of (pass-through) query?=====There is no LINKED tables in this Access front end app. There is no pass-through queries. SQL stored procedure called from Access (by ADO connection/command) would open a recordset that populates local ("proxy") Access table that is linked to continuous subform.==> Secondly, do you users access a common fron end or do they all have seperate copies? I guess what I'm getting at here is if they are all using the same front-end, then one user may be upseting the data source of another. If you are using ODBC links this will be unlikely.====Users are using ONE Access file from the share drive to open an instance of it on their computers.Question regarding ODBC linked tables: When I try this I can only READ the data, not WRITE. Is it possible to do both on linked tables?Thu, 30 Aug 2012 06:44:48 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxHi ValTo understand your problem I need clarification on a couple of things. I have experienced linked tables causing forms to display #Deleted, but not the data disappearing from the form. The #Deleted is well documented on the net and generally relates to keys/indexes.Firstly, what exactly do you mean by a proxy table? Are these just links to SQL Server created using ODBC or some form of (pass-through) query?Secondly, do you users access a common fron end or do they all have seperate copies? I guess what I'm getting at here is if they are all using the same front-end, then one user may be upseting the data source of another. If you are using ODBC links this will be unlikely.CheersRowanWed, 29 Aug 2012 18:01:51 GMTProofOfLifehttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxWell, I'm afraid I'm not much help at this stage. I've not worked with "proxy tables" at all either in Access or SQL Server - from web research they appear to used primarily with SyBase rather than SQL Server. But the fact that you are able to get to work on some PCs but not on others suggests to me that either the network connection to one or more of the PCs is flakey, or that you have software differences in what used to be called the MDAC package. Are all workstations running the same OS, and do they all have SP3 for Office 2007?Wed, 29 Aug 2012 17:01:02 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxok.1. This is mdb2. Subform are just imbedded into main form3. Only subform is linked to the proxy table (because the table feeds continuous subfom) Other controls (dropdowns, textboxes) do not need a proxy table and are populated directly from SQL Server 4. It is a new application. During testing we did not see that problem.================================I just tried this:a. I open an instance of the mdb on ONE computer. Data on subform is ok - not deleted.b. I opened 2 instances of the same mdb file on 2 different computers. It takes 15 - 40 sec for the data on subform of the first computer to loose the data, the second computer subform stable.c. I copied the mdb to two different locations, and opened each of them on two different computers. No problem at all, subforms behave!Hence: two instances of the same mdb somehow (I do not know!!) communicate with each other and kicks the data out of each others proxy tables.(F. Chopin, Funeral march ...)Wed, 29 Aug 2012 16:13:13 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxThat's quite an unusual design approach to creating a front-end to SQL Server. In general the recommendation is to either use an ADP front-end, although support in future versions is questionable, or to link the SQL Server tables using ODBC. Several questions:What format is your Access front-end - accdb or mdb?How are your main and subforms linked?Also are both the main form and the subforms linked to the proxy table? If so it sounds like the proxy table is being cleared by something.Is this an existing application that has been running for some time or is it a new one?WendellEvergreen, COWed, 29 Aug 2012 15:28:23 GMTWendellBhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxMy app includes 1) SS2K8 database on the back end2) Access 2007 front end NOT LINKED, data flows through ADO and stored procedures on the back end.=================Front End consists of forms/subforms and proxy tables "connected' to subforms. Data flows that way:Sql server table <===> Access proxy table <===> subform ==============Now a fun part begins:user opens the form (with continuous subforms populated with items) and just sits back doing nothing. Then suddenly records disappear from the subform with scary "=deleted" on the items a second ago having meaningful data. Can you imagine how happy the user can be?I checked the underlying table - empty==================After reloading the Access form all data comes back (SQL Server table was not changed)What? Why? No ideaValWed, 29 Aug 2012 15:07:44 GMTvaleryk2000