Wierd Prob: Cannot Update SQL Server Text-fields For Some Records, But Can For Others
Feb 22, 2005
Hello,
I have a very strange problem:
I have a table linked to an SQL Server table in my MS Access 2000 Application containing 5 text-fields (SQL Server Type, in Access seen as MEMO-fields) and several other fields (in total about 140 fields). Updating any field works fine except for some records. In those records I can only edit the non-text-fields in my application. When I try to edit one of the text-fields I get a error message telling me that another user has edited the record and i have to discard my changes.
The strange thing is that this problem only crops up for some records. (1 in a 100 or so) All other records work fine.
Does anyone know what happens here? And how this can be fixed?
I am using a update query to update a table from another table. This query however will works only if I set the allowzerolength to yes in the table design otherwise validation error message will appear. This means that I cannot use the "Is Null " anymore because this empty space " " can appear. Is there any a way i can filter out those empty fields in the query design then? Thank you for your help.
I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.
We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records. The other system stores the whole note in one record. We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record). I have a table with the notes from the first sytem which looks something like Note1 - Line1 - Text Note1 - Line2 - Text Note2 - Line1 - Text Note3 - Line1 - Text Note3 - Line2 - Text Note3 - Line3 - Text
What I want to do is, for each note, combine the separate lines into one record. I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?
I have 2 tables Master table (Jobs) containing the primary key ("Job Reference") and 2nd table (Candidates) with the foreign key ("Job Reference")
2 Forms
frmJobs Form to view job details frmCandidates form to view Candates information
I have placed add new candidate button on the frmJobs form which opens the candidate form in the add mode as a blank form. This is fine but I'm looking to add a record where it picks up the "Job Reference" text field value from the main frmJobs and update it in the "Job Refernce" text field on the frmCandidate when I click add new candidate.
How can I achieve this? I'm fairly new to access/vba
I am very new at Microsoft Access. I have two forms, A and B, that are filled out by two different users. Form A gets filled out first than form B gets filled out. Each has a name box as well as a birthdate box and a few other similar text fields. However, each form also has a few distinctive fields. I was wondering how I could input the common information in Form A to Form B so the user of Form B does not have to spend time retyping the name and birthdates again. In other words, I want to synchronize the similar records between the two forms. I would like the values to appear in a table as well. I would greatly appreciate it if someone could help me with the visual basic code. Thank You.
I have a form which uses a list box to select which record to display. The code is all generated by Access during form design. It is a method I have used numerous times inthe past in various database without problem (even in the current database I am developing).But for some reason on this one form I get the following error;-
"The expression After Update you entered as the property setting produced the ollowing error; A problem occurred while database was communication with OLE Server or Active X Control".
Recently I've been having a problem with Access (2003) that I can't seem to get around. If I click on "NEW" to create a new query, do some editing, then go to close, it prompts me as to whether or not I want to save, which would be ok, if it would let me choose NO!!
I only get OK or Cancel options. I've NEVER had this problem before. I have not done any updates added any addons. Is there some hidden trick here or some way to get around this cause its really annoying.
I have a database on a shared network drive. If you try to open the db (when another user already has the db open) from the route "my computer" then selecting the drive then selecting the db the message
"Microsoft Access can't find the database file 'M:lah blah blah' Make sure you have entered the correct file and path name"
However, if you open the db through the access application it opens fine and there are no problems.
Has anyone come accross this before??? I have contacted our IT department who say that they cannot see why this is happening.
I have a backwards date between query. I have a table with Date1 and Date2 fields. On the form I have a calender control. I want the user to click a date on the calender control and then look at the rows in the table to see if that date is between the Date1 and Date2 fields.
So the query will be (in english) get all rows that CalenderDate1 is between Date1 and Date2
When I open my mainform through an onClick event of a button, the form opens up with the subform on it is disabled/closed -- Just a blank white box shows in place of the subform on my mainform. Here: http://img426.imageshack.us/img426/4672/shot29sj.th.jpg (http://img426.imageshack.us/my.php?image=shot29sj.jpg)
However, when i go through the objects window and open the mainfrom directly. I am able to enter and update data and the subform shows up and works fine. Here: http://img420.imageshack.us/img420/5650/shot19ig.th.jpg (http://img420.imageshack.us/my.php?image=shot19ig.jpg)
This is the code attached to the onClick event on the mainMenu
Private Sub New_Project_Click() On Error GoTo Err_New_Project_Click
Dim stDocName As String Dim stLinkCriteria As String
stDocName = "Project Status - Full Details2"
stLinkCriteria = "[projectId]= 0" DoCmd.OpenForm stDocName, , , stLinkCriteria [Form_Project Status - Full Details2]![Proj ID].Enabled = False
This is the record source on the mainform: The projectInfomation table is the record source SELECT * FROM projectInformation;
This is the record source on the subform: The projectStatusCommentary table is the record source
SELECT projectStatusCommentary.projStatusId, projectStatusCommentary.projectId, projectStatusCommentary.statusCommentary, projectStatusCommentary.commentaryDate FROM projectStatusCommentary;
The link fields are: projectInformation.projectId projectStatusCommentary.projectId
The tables are in sybase and my application has an ODBC connection to them. I can update any of the tables directly without issues. I have ran the queries independently and updated tables through the query and it works fine also.
I have been pulling my hairs out on this for over 3weeks now. cos i can't seem to find what's wrong. I am not able to attache the db cos it's very huge. But i have copied the problematic form &subform, to a new db so you may see my design, and record sources.
If you have any suggestions, ideas, or you've come across this kind of issue before, anything...please make my new Year a happy one and help :) I would really appreciate it! Have a happy and prosperous new year.
I need to update data in a bunch of tables of a sql server database. The database has 300 tables which I have linked via odbc. I'm hoping there is a simple way, using vba, to loop through my linked sql server tables and determine which of them are views as opposed to tables.
I have a database here that is run by users who use either Access 2002 (XP) or Access 2003.
I have noticed something which to me seems odd and dunno if any of you guys could explain why or how to resolve it?
If one of the 2002 users opens the database it opens fine first time (I use 2002 myself for development) however if a 2003 user opens it it can take 30s+ to open, but then if they close it it will re-open in just a couple of seconds like for the 2002 users.
However if a 2002 user opens it once a 2003 user has just closed it they will then take 30s or so to open it, but then again if they close and re-open it's almost instant.
Is that normal?
Incidentally office 2007 doesn't seem to suffer from this, but it's dead slow at running the database anyway so I'm not going down that route for the forseeable future!
Thanks in advance for any input!
I did try the recommended method of searching this forum using google but it didn't seem to work for me:(
I'm trying to sort a combo box alphabetically except for one value which is always at the top of the list.
The row source of the combo box is a query. record to remain at the top of the list is static- same value and ID number always. I've fiddled around with a few things, but at the end of the day, i'm just not smart enough! (or its not possible -which i doubt).. any suggestions? cheers.
I need some help, I’m trying to update 1 database which is in SQL on one server with data from another database which is on another server There are around 8000 records. It is taking for ever updating the data via MS Access Append Query and Update query. Do you know if there is any other faster way in code, which can be updated? Maybe in a loop doing 100 records at a time or any other suggestions.
Reason I need a faster way is the data on the SQL database is needs to be updated is linked to a website with data in , while the data is being updated the website crashes.
Hi, i have created two tables, containing the same fields. One table if for '1 day tests' and the other is for '2 day tests'. I need to keep these two in seperate tables.
At the moment i have two seperate forms, one for each type of test, but as the fields are all the same, i was wondering if i had a combo box, so the user could choose whether they wanted a one day or two day test and filled in the rest of the fields, the record would then be stored in the appropriate table. Ie 2 day test table, if the chose 2 day test on the combo box
I have two date and time fields which format are like: 15/09/05 09:48:43,I have another field which calculates the time difference between these two fields. I am doing a calculation on the time difference field and want the time to be displayed as for example 03:00, but at the moment it just shows 3:00.
This is the control source which has some formatting in it:
I've tried every way i can, and i cant get the rowsource query right.
If someone could look at this and post a fix, so that when one uses the "training record" form it allows you only to select course dates that are already in the tbl_course_dates table.
I'm trying to create a little dbase for personal keeping track of a number of companies I deal with, so that i store their details and also the products they distribute. In more detail, I want to have 'fixed' product categories, which will be assigned to supplier, and each category will include the product models I have set, including their details (such as price, manufacturer, country of origin, etc).
My point is to be able to create several customised reports and listing: 1. All product models I have in a category including their details. 2. Report per supplier to view their details and the products they distribute 3. Certain product models in a list with their local distributor (companies I deal with).
I assume it involves a couple of many2many relationships, however I have created a file in MS Access but never managed to have it work properly.
I want to delete a relationship temporarily so that I can delete a record. After the record is deleted I will restore the relationship immediately. But I am concerned that this will ruin any queries I have already established or ruin my other records. Could you let me know if this is possible without making a mess of things that have already been established.
