Access 2000 Frontend MS SQL 2000 Backend - Locking Problems
Aug 20, 2007
We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
I have a database with a sql backend and an access frontend. I put it in a folder on the network for users to access. First, they were getting errors like not being able to access the database if someone else had it open and not being able to perform certain functionality. It was suggested that i put a mde on each of the users pc. i gave them all access to their own front ends on the folder because they use different pcs. this solved the problem. however, i need a way to perform updates without manually creating mdes for every user. i used to work for a co that there was one mde was put into a folder on the network with a config file. The users access the database through CITRIX. Whenever they accessed the shortcut on the server, it created the users their own front end. Whenever we need to provide updates, we simply made a new mde from the mdb and whenever the user accessed the database again, they would have the latest mde and it would created an update frontend for the user. Does anyone know of such an utility or know what I am referring too? I encounted several suggestions like a batch file; but they refer to putting the file and the mde on each users pc. This is not how i'm set up. Thanks
Hello. I have an Access 97 frontend and a SQL 7 backend. When I try to open up the linked SQL tables in the frontend I recieve the error " This recordset is not updatable" from Access. I have the permissions in SQL set at this point to public having all permissions but it still does not change my problem. Does anyone know the answer?
Ok, little frustrated hence the excessive exclamation points.
I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!
I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!
And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?
Is there something simple i'm missing or is it my table structure????
Hi, this is my first post (hopefully of many) on this board. Just wanted to say a quick hello before I dive into my question. :)
As the title suggested, I have to develop a MS Access form app (yes, it has to be Access - I know it sucks) that will post and query data to and from a remote SQL Server db. While I have no problem linking the two through the default ODBC drivers, my question is security. Some (actually most all) of the data being passed back and forth is sensative information, and I would like to know the best way to keep it safe.
If anybody has any suggestions, instructions, or can redirect me to a good FAQ site on the subject, I would be most appreciative. I have already done a search on these forums for an answer, but have sadly come up short.
We are new Access users and trying to take our database live...here are some of the problems we're facing.
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend: 1.) When we run our queries, multiple copies of the same record are displayed (this is wrong). 2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also, if anyone knows any websites or resources that can help us understand what things like Expr1 mean (a reference) that would be helpful too!
We are not a very advanced group of access users and are trying to split our database for a research study...here is our question:
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend: 1.) When we run our queries, multiple copies of the same record are displayed (this is wrong). 2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also if anyone has a good source for trouble shooting this or simmilar difficulties we'd appriciate any spots or advice.
Hi! I am evaluating an architecture for one of our project... a SQL database containing the data (backend) and a second database containing the development code (frontend) linked to the backend with synonyms.
It enables to upgrade the code without touching the data. Or to change the backend / use a different set of data at will.
Everything was going fine, the behavior was expected to be EXACTLY the same with synonyms as with real tables. But I came accross a problem: Let's say we have a synonym (frontend) dbo.TABLE1 that points to a table (backend) with a IDENTITY column.
I have a sp (frontend) with the following code: INSERT INTO dbo.TABLE1... SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()
Well in that case, @SCOPE_IDENTITY is NULL!
Anyone has ever faced that problem? Should I use another function to return the last ID inserted? Or is it the backend/frontend architecture that is completely flawed? I also heard there's a way, by creating the tables and the code on different filegroups, to restore only the tables or the code...
Hi all, Please help me for the code of connectivity it was working fine,but,when i hav written code for validation for id n password,its showing error as Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'login_detail'. The statement has been terminated. Here is my code given below. Please help me Imports System Imports System.IO Imports System.Xml Imports System.Text Imports System.Security.Cryptography Imports System.Web.UI.WebControls Imports System.Web.UI.HtmlControls Imports System.Data Imports System.Data.SqlClientPublic Class WebForm1 Inherits System.Web.UI.Page#Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer.<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() End SubProtected WithEvents txtUser As System.Web.UI.WebControls.TextBox Protected WithEvents txtPwd As System.Web.UI.WebControls.TextBoxProtected WithEvents btnSubmit As System.Web.UI.WebControls.Button Protected WithEvents lblError As System.Web.UI.WebControls.Label 'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it.Private designerPlaceholderDeclaration As System.Object Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub #End RegionPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here Dim objConn As SqlClient.SqlConnection Dim ds As New DataSetDim m_strConnection As String = "server=172.16.152.17;Database=Trial;UID=abhi_Asset4;PWD=L!nux001;Connect Timeout=100"objConn = New SqlClient.SqlConnection objConn.ConnectionString = m_strConnection objConn.Open() Dim strSQL As String strSQL = "insert into login_detail(UserID,Password) values('" + txtUser.Text + "','" + txtPwd.Text + "')"Dim objCommand As SqlClient.SqlCommandobjCommand = New SqlClient.SqlCommand(strSQL, objConn) objCommand.CommandText = strSQL objCommand.ExecuteNonQuery() objConn.Close()
End SubPrivate Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click If (txtUser.Text.Trim() = "") Then lblError.Text = "UserID should not be blank" ElseIf (Convert.ToInt32(txtUser.Text.Length) < 5) Then lblError.Text = "UserID length must be more than 5" ElseIf (Convert.ToInt32(txtUser.Text.Length) > 20) Then lblError.Text = "UserID length must not be greater than 20" ElseIf (txtPwd.Text.Trim() = "") Then lblError.Text = "Password should not be blank" ElseIf (Convert.ToInt32(txtPwd.Text.Length) < 5) Then lblError.Text = "Password length must be more than 5" ElseIf (Convert.ToInt32(txtPwd.Text.Length) > 20) Then lblError.Text = "Password length must not be greater than 20" Else Server.Transfer("Success.aspx") End IfEnd Sub End Class
I recently had to reinstall a new instance of SQLServer 2000, but was unable to use the previous server name. As a result, my Access2000 front end is not happy with it's linked tables. I can't seem to find anyplace within Access to universally change the address of the SQLServer used as the back-end for all linked tables.
When I do try to access the linked tables through Access, I get an error, and the option to change the server location. When I try to type-in the new SQLServer location, there is an attempt to reconnect to SQLServer, but a whole lot of errors are generated, and none of the data is transferred into the Access table.
I really don't want to have to re-do my Access front end, so it seems it would be easiest to somehow reinstall SQLServer to have the same server location it used to. Is there a good way to completely erase all traces of SQLServer so that I can have better luck reinstalling it to the same location it used to be in? Just using the uninstall program from SQLServer doesn't seem to be cutting it.
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
Hi, I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
We have a SQL Server 7.0 system in NT 4.0 environment. We upgraded our users to Access 2000 and started to work with this. Now we installed a new server which is Windows 2000 based and the domain is different from the SQL servers domain. We then installed Access 2000 on Windows 2000 to use with terminal server. But I noticed that there was a problem with the program. I then looked at the program which was written on Access 2000 and saw that the tables and views can't be seen. The program runs but I can't see the views and tables. Another thing is access disconects from SQL Server when I want to see the tables. So what can be the problem.
In one part there is an Access 2000 on Windows 2000 server. On the other part SQL Server 7.0 on Windows NT 4.0. And Access can't see the tables in SQL server.
When I try to make a connection to an Access .mdb I get the following error: "Unable to open application. The workgroup information file is missing or opened exclusively by another user"
Yet, I am able to open the file through Access and have necessary permissions and I know no one else has it opened. The mdb is password protected and I have provided the correct login information in the DTS connection.
Hi,We have encountered deadlock on a table which is used to generatesequential numbers for different categories eg typical entriesCategory ValueTRADE_NO 1456JOB_NO 267.....The applications reference the relevant category applicable to themand updatethe Value accordingly. This is table is very small, occupying 1 page.However, it has no index as it was not seen to be appropriate for atable this size.However, can someone please advise whether1. An index is required for row level locking2. If an index on a table as small as above is likely to reduce thedeadlock rate.Also, please consider the following but which I am not sure isrelevant for above query.We noted that when we migrated the database concerned from SQL 6.5 toSQL 2000, using DTS, that the database was NOT strictly in SQL 2000format for non clustered indexes (NC) ie the clustered key was notpart of the NC index until the clustered index was rebuilt.Given this should I just rebuild this table with a fake index and dropit thereafter.We are aware of the different techniques used to avoid deadlocks (egtables accessed in same order etc) and have , as much as possible,implemented those practices.I thank you in advance for any help you may be able to offer.ThanksPuvendran
How can I configure a front end Access 2000 Database using Microsoft SQL 2000 as a back end environment? Could anybody help me by telling me what are the steps under SQL? Thanks everyone in advance.
I am converting a MS Access 2000 project to use SQL Server. This project has uncovered a number of problems, my latest seems to be very odd.
When I query using ADO against the SQL Server database, the results don't seemed to be returned immediately. It's like the access methods being used are waiting too long to write/read the data.
This could be a simple ADO configuration error, but I cannot find any settings that would make this behave so strange.
I'm using OLE/DB drivers with trusted security and attaching using client-side recordsets (I tried server-side as well, same results) to get data for the Access form. In many instances, the data from the form is not yet retrieved when I check the results using the debugger. If I execute the same section of code just seconds later, it works without fail.
I ran the SQL Server Profiler just for grins and found that records were not getting written to the database as I would have expected. Apparently ADO had generated a transaction and rolled it back. (why I don't know) I have looked in Microsoft's support database and on MSDN to no avail. Rather then rewriting my T-SQL as stored procedures for these functions, I'm hoping for some insights from anyone else who has been down this nasty road.
Here is the exact scenario:
Problem #1: A form that accepts data and has an update button does not update the recordset being used to populate the form. I even coded a rst.Update statement into the code. The record within the recordset isn't updated until the DoCmd.GotoRecord , , acNewRec call has been made. I suppose I could code the insert statement myself, but shouldn't Access be updating the database for me. (it does have r/w access)
Problem #2: A subform that performs a query and allows records to be added to a table does not correctly detect data returned from the query. A check is made in the code for a field (which has a value of 0) but the code detects a null value. If I step through this code in the debugger, enough time passes that the value is present and I never see the problem. If I set a breakpoint at the error, the null condition is indicated, even though the debugger shows a value of 0 for the field.
SQL Server version is 2000, with SP3a applied.
I updated the MDAC to 2.8, JET to 8.0 and still the same problem. I cannot find anything in my ADO or Access books for a setting that would cause this behavior. My connection is using the following info:
Set Conn = New ADODB.Connection Conn.CursorLocation = adUseClient Set rst = New ADODB.Recordset Connection string: Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;Trusted_Connection=Yes;Integrated Security=SSPI; Recordset Cursor type: adOpenDynamic Recordset LockType: adLockOptimistic
I open the recordset using: rst.Open mytable, Conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Problem # 1
After manipulating the data in the form, I press a "save" button on the form. This button calls a number of subroutines, eventually executing the code below:
Problem # 2 I set the recordsource using: (in the form_open) Me.RecordSource = "Select * from mytable where myfield = 'data'"
In the form_unload, the problems occur. It tests for a value stored in the form from the query. This value is null at the time of the code execution, it has a value when inspected within the debugger however.
Maybe I'm missing something here, this project was originally coded using DAO. My conversion to ADO is 95% complete. (all of these two components are converted to ADO) This problem is causing me to miss out on sleep and look like a rookie.
Has anyone seen or heard of something such as this? Is there any hope for this snarled mess of 25K lines of code?
Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin
Insert or update statements seems to be locking entire page or table rather than locking the corresponding row to be inserted or updated.
lets assume the table with 3 rows.
scenario 1) In transaction A, I'm updating the 3'rd row. and in another transaction B I'm reading row 1. Transaction B seems to be waiting for transaction A to finish before returning the select results.
Scenario 2) In transaction A, I'm inserting new row (4'th row) and in another transaction B I'm reading row 1. here as well trasaction B does not return the row 1 unless transaction A is complete. Select operation is blocked due to insert.
Ideally in both the scenarios , read operation should have returned the results without waiting for update/insert to finish. As the read is being done on different rows than that of being updated or inserted. I have tried both the insert/update as well as select queries with all the possible locking hints such as ROWLOCK, READCOMMITED, UPDLOCK etc... The only way select query returns the row without blocking is by using the NOLOCK locking hint. But then this is not the proper solution as it gives us the dirty read.
Please suggest me any solution or workaround for above issue.
Hi, I wonder if anyone can shed some light on this issue -
We have a client/server program for MSSQL7 and MSSQL2000. When running client/server - we have our own broker, etc, the program ends up locking itself on SQL2000; this is not a problem on SQL7. We noticed that it seems some system tables locking tempdb. We also noticed that SQL2000 no longer has insert-into/bulkcopy option like SQL7. But, here we don't have temporary tables at all. It's just that we found the lockings in tempdb caused by some sysobjects, sysindexes, etc. Also, this does not happen to all the systems we have. Some of our machine does not have the self-lock problem; most of the machines have this problem though.
If we don't run client/server, then the program works fine. Same logic, the difference is when we use client/server logic to connect to the remote SQLServer 2000 server, the locking might happen on most of our machines.
Anyone is familiar with tempdb and locking issues, please provide some hint??? Is it possible that there are some system parameters that we can set to avoid the problem. We haven't found out why some of our machines do not have problem, while most of the machines have this locking problem.
We are running a 3rd party ETL tool to populate a denormailized version of a production database for reports. Everything works fine 95% of the time. However there is a semi-rare occurence of the ETL tool hanging up. The norm is for the tool to take about 5 times longer than usual, but it still works. Over the weekend however it through an error saying:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
The reports are run through Crystal using stored procs and are all basically select statements
So my question(s) are the following: 1. What kind of lock would a report put on a table (select statement) 2. Would it make sense to change stored procs to use WITH NOLOCK? 3. Or is something else going on?
HiIs it possible to force row level locking in one or more tables insome database. We have some problems when SQL Server decides to choosepage- or table-level locking.We are using SQL Server 2000.Best regardsAarno
Hi, A quick Q for all the Sql Server 2000 beta testers out there. Does anyone know of any problems associated with using SS2000 Enterprise Manager with a SS7 backend running SP2? In particular, I'm keen to try out the Tsql debugger features, if they'll work with SS7.
Many thanks in advance, Jon Reade, Sql Server 7 DBA NEC Technologies (UK) Ltd Telford, Shropshire.
I hope that I am not asking a question which has been answered many times in the past, I did a search but could not find an answer. These may be beginner questions, so bear with me as I am something of a beginner.
This week our organization updated from SQL 2000 to SQL 2005. We are using a Microsoft Access front end for this data and the information is entered through forms into the correct tables. When our researchers are enter the information they also have to sometimes search existing information to ensure they do not duplicate what is already in there. For this we use several access queries. This has worked well on the SQL 2000 server without any issues. However, now that we are on 2005 we are starting to recieve ODBC Call fail errors and errors which specify that they are a result of a deadlock. My first question is, are the ODBC call fail errors possibly locks which caused the ODBC connection to time out? My second question is why am I recieving these errors in 2005 but not 2000?
we are having performance problems with a pure MS Access database across a WAN. As a test we want to setup a MSSQL server , move the data to the server and run the MS Access clients through a ODBC interface. The application uses much SQL methods to get the data ( docmd.runsql commands ). Only a few native queries are used.
Will this help the performance ? If not , what do i need to do to get a better performance ? It is a lot of work to build a new application so if it is possible whith the existing code it would be very nice .
Hi All, I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?
My company is currently using access to manage equipment in 4-5 different locations. I want to move this to a sql database and have a front-end to do the same thing access is doing now.
Should I use access as a front-end or should I develop a custom front end using vb?
Just a fyi 5-7 tables 7-9 queries 10 or so reports and the front end is currently a switchboard that links to many other forms.
I am creating a Access frontend to some SQL Server Tables. One of these tables has an int type field and several money type fields. I am trying to populate the table based on text boxes on an Access form. Because of other reasons instead of just using the SQL table as the record source of the form I am using "INSERT INTO" statements to update the data.
Here is my problem if I leave the textfields that are supposed to populate the int and money fields blank I get an syntax error message on the INSERT statement. If I populate the textfields with a 0 the INSERT works fine.
The int field and money fields are defined to accept nulls so why is the blank textfield generating an error message instead of inserting a null into the table?
Any help or guidance would be greatly appreciated.
I have a website which runs off a Access database which I am currentlyconverting to sql server database. I would like to still use my accessfront end for reporting and queries..I created an odbc connection on my computer to the remote sql serverand created a link in access to my sql server tables. All my report andQueries run fine I just have one problem it keeps on prompting me forther password of the remote sqlserver database. I check the odbcconnection and I see the password is not saved in there even though Iput it in. Is there a way I can have the password saved. I do not wantkeep on having to enter the password.
I have a ms acssess db acting as a frontend to a Sql Server 2005 db. I have need to have the db setup as a standalone to distribute with the application for times when the 2005 server is unreachable. I have a created CE db that replicates the 2005 version.
What I want to do now is put the CE db on the desktop with the MS access frontend. Based on a user's preferences the connection would direct to the CE version.
The CE engine is loaded on the desktop, I have modified the ADODb connection string to point to the CE db. When I run it I get an error that says: "The provider can't be found. It may not be properly installed."
Here's the connection string:cnxn: ConnectionString = "Provider=microsoft.sqlserver.mobile.oledb.3.1;Data Source=spcd.sdf;User ID=;Password=;"
Am I trying to do something that just isn't going to happen?
