Multiple INSERTs At Speed Failing?
Aug 29, 2007
I am trying to issue multiple INSERT statements in a row - but it seems that only the first succeeds. If I put a 1-second delay between attempts, suddenly they all succeed (so I know the statements themselves are all valid, it's not a data issue).
These are all being issued in a loop from the same thread, so as far as I can see unless the data is being inserted asynchronously, I can't see any problem. If it is being done asynchronously - how are we supposed to know one INSERT succeeded and thus we can issue the next one?
Anyone have any idea why this might happen? I don't want to leave 'magic delays' in the code!
View Replies
ADVERTISEMENT
Jul 7, 2006
I have an application set up as follows:
Live front end in one network folder, accessible by all users.
Development front end in another folder accessible only by myself (changes imported into Live as and when necessary).
Back end in another folder accessed by both front ends.
One of the tables contains orders made by various people. The way the data entry form is set up is that, once a person's ID has been entered, all previous orders are displayed in a sub form at the bottom of the screen.
I'm noticing that, if two people are entering new records into it via two different PCs, it sometimes happens that one person can see an inserted record, only for it to later vanish from both the form and the underlying table. There is no pattern to these 'disappearances', but it's obviously concerning.
Possibly more worrying is that, from time to time, all records for the person being updated are being lost, not just the most recent entries being made.
I have the database set to Record Level Locking. Can anyone suggest anything else I should be looking out for or should have done?
The only other thing I can think of is that I sometimes enter records from the development version, while it is open in development mode, with just the data entry form being out of design mode. Would that make a difference? If so, why?
I'm stumped here.:confused:
View 2 Replies
View Related
Jun 14, 2005
This one is very annoying...
i have a button that is supposed to update a record based on the record id. when i click the button, it not only updates the record, but it also creates a new record with just the default values i have set for the table. no other values are entered into the table. here is the code that is relevant to the problem:
Code:Dim DAOdb As Database Dim DAOrs As dao.Recordset Dim sSQL As String Set DAOdb = CurrentDb() sSQL = "Select * from StopwatchRecord where StopwatchRecord.ID=" & Me.hidID & ";" Set DAOrs = DAOdb.OpenRecordset(sSQL) With DAOrs .Edit .Fields("StartTime") = Me.StartTime.Value .Fields("EndTime") = Me.EndTime.Value .Fields("ElapsedTime") = Me.ElapsedTime.Value .Fields("Comments") = Me.Comments.Value .Update End With MsgBox "Record Saved" DAOrs.Close DAOdb.Close
Please help! this is driving me nuts
thanks
*j
View 12 Replies
View Related
Nov 18, 2006
Hi All,
I have been searching and can't seem to find how to solve my problem. I have a sub form that allows adding new rows to a database. The detail contains a combobox and a few text fields. When I enter data everything works fine. However when I accidently tab into the next row (new blank row) and then try to get out of the form without adding a new row I get an error about "Index or Primary Key cannot be null" or something like that.
It is very frustrating because I can't exit the form until I add data to the new row and then leave without tabbing to much (thus creating another new row with no data).
How do I trap the new row and only allow the insert if it has data in it?
Thanks.
View 1 Replies
View Related
Jun 3, 2006
hello!
I'm wondering how would I, in Access, make a query that would let me do a UNION and then insert that data into a table?
Is there a way that I could achieve that in one(1) query?
I've tried with INTO, but I get an error.
(i'm using access 2000-2003)
thanks!
View 1 Replies
View Related
Apr 30, 2015
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code:
INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;
View 5 Replies
View Related
Feb 5, 2006
In my query I have the folowing expression:
Currency: Iif([Exch]="1";"Szt";"Eur") - if the exchange rate is 1 then the currency is Zlotty if different from 1 then it's Euro
When I run the query it returns "#Error" ?
Anyone have an idea to fix this?
View 2 Replies
View Related
Feb 17, 2007
well i am trying to make a very simple program that would help me learn the capitals of each country. yes it is a weird thing to do but i just it might help.
anyway i started doing it using access and made a table with three columns primary key number, country and capital.
I started making a form and im tryin to make it so the program would randomly pick a country and i would have to choose from a list box the capital and i would have a command button which when i click will tell me if i am right or not and if i am not then it would say the right capital.
How should i do this?
p.s ino the basics of using the code builder
help much appreciated.
View 3 Replies
View Related
Feb 26, 2008
Hi all
I have a form which is used for entering holiday requests.
It has fields such as name, type of holiday, date from and date to but it also has a list box value. This list box contains the number of days between the date from and date to and exludes weekends and bankholidays using data from a table. The data from the form is inserted into a table using an sql string when the submit button is pressed. The problem I have is that the sql does not pick up the number of days from the list box unless I click on the list box before I click on submit. It is almost as if the value is in the list box but it is simply not recognised by the sql unless I select the list box using the mouse. I have tried to select the list box using code when the submit button is pressed but to no avail. Does anyone have any ideas how I can over come this or perhaps how to physically select the list box so it is highlighted when the submit button is pressed??
Thanks all
Gareth
View 1 Replies
View Related
Dec 2, 2005
This is driving me absolutely insane, can anyone find why this won't work?
UPDATE tbl_PrivateMessages SET tbl_PrivateMessages.RecipientStatus = 1
WHERE tbl_PrivateMessages.MessageID IN ([Enter MessageIDs]);
I've tried updating it through ASP with the following:
Execute UPDATE_INBOX_MSG_STATUS 0,26,25
And also just tried running the query from access itself using 25,26 as IDs.
Neither of those seem to work...please advise. Also, I don't even get an error message as to why it didn't update.
TABLE STRUCTURE
MessageID - AutoNumber
MessageUserID - Number
MessageSubject - Text
MessageContent - Memo
MessageAuthor - Number
MessageDate - Date/Time
AuthorStatus - Number
RecipientStatus - Number
Many thanks
Steve Johnson
View 4 Replies
View Related
Dec 8, 2006
Hello I'm helping a friend with an access project and the Referential Integrity check is failing for some reason unknown to me. I recreated the entire database from scratch and added all relationships before adding any data and then added in the data. The tables giving me problems are the following:
Orders Table:
OrderID - Primary Key Autonumber, Format:1000, Indexed: Yes(No Dupes)
CustID - Number, foreign key to a customers table, 1-many relationship
SalesID - Number, foreign key to a sales table, 1-many relationship
ShippingID - Number, foreign key to a shipping table, 1-many relationship
3 different date fields and a price field
This table is filled with data.
Order Details Table:
ItemID - Primary Key Autonumber, Indexed: Yes(no dupes)
OrderID - Number, Dec. Places: Auto, Required: Yes, Indexed: Yes(dupes OK)
RingID - foreign key to rings table, 1-many relationship
StoneID - foreign key to gemstones table, 1-many relationship
Ring Size - number
There is no data in the order details table.
The Orders to Order Details is a 1 to Many relationship with ref integrity and cascading updates and deletes enforced.
When I try to manually type in a record I get the "You cannot add or change a record because a related record is required in table 'Orders'" even though I added a lookup list box listing all the OrderIDs from the Order table. I know I'm typing in a valid OrderID number, but it's not taking it and throwing this error.
Any ideas why? You advice is greatly appriciated!
Thank you.
View 4 Replies
View Related
Nov 29, 2004
Hi,
I have inherited a buggy database thas has a simple piece of code that won't behave the way I think it should!
The code should add a new record and have that new record appear on the list form (where the button On_Click event is called) making it available for user edditing. In 99% of cases though, the blank new record does not appear. The record is getting created, as i can see it in the table and have added a watch to the RecordCount property, it just doesn't display automatically. The existing workaround for users is to click a second button that invokes a simple one line function sub that performs only a Me.Requery. The user presses that up to two or three times before the new blank record finally appears.
One solution I read about, but hesitated in trying due to the unprofessional 'flash' that occurs, was to close the form and the re-open it...anyone experienced this or can comeup with a neat workaround?
The code
Private Sub CmdAddRule_Click()
Dim db As AO.Database, intnextseq As Integer
Dim TestThis As String
Set db = CurrentDb()
Dim rstDao As DAO.Recordset
Set rstDao = db.OpenRecordset("select * from stblAllocRules order by intRUSequence desc;", dbOpenDynaset)
If rstDao.RecordCount > 0 Then
intnextseq = rstDao!intRUSequence + 1
Else
intnextseq = 1
End If
With rstDao
.AddNew
!AutRU_id = 0
!intRUSequence = intnextseq
!bytAppendBack = 1
!bytUpdateBack = 0
!bytDeleteBack = 0
.Update
.Close
End With
Set rstDao = Nothing
Set db = Nothing
Me.Requery
Me.Repaint 'I have used this staement in addition to and instead of the Me.Requery to no effect
Me.Recordsource = Me.RecordSource 'I have used this statement also in addition to the above to and in isolation, to no effect.
End Sub
View 2 Replies
View Related
Dec 7, 2006
Hello all,
I have created a FE/BE database which is operating on a network. There is one FE for each person (approx 150) all accessing 1 BE. There are at most, maybe 10 people using it at once. The database has been working well for about 2 months.
My problem is not with data corruption (touch-wood!), but with a complex form.
The Form is split into a Header/Footer, with a Detail section containing a subform. The main form filters records that appear on the subform. The subform is continous, with several conditional formatting cells per record.
Most of the time it works fine, but occasionally, the main form opens, but the subform fails to load/open. All I see is a white page with the main form header displaying correctly along the top 1/3rd of the screen. No error message is displayed. If I print the page using a Report (The subform and Report use the same query) the page prints perfectly with all records showing.
I have seen this problem on several different workstations, only to try again later to see it working (having closed the db, logging off then back on again)
I am at a loss with this one. Is it a problem with Access or the network? I am unable to find anything similar to this problem on the forum. Can anyone shed any light on it?
View 2 Replies
View Related
May 15, 2013
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between]
FROM [Main Table]
WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
View 11 Replies
View Related
May 18, 2013
I am using an unbound object frame to display an .png image file which is set in vba.
The image is an excel chart saved as a .png
The problem I have however is that the image I save from Excel is great quality, and is pretty tack sharp, but when I display the same file in the unbound object frame in access, it is not nearly as sharp.
I am setting the picture property of the control as:
ubImageUserChart = "c: empmyChart.png"
It displays alright, but is just a bit fuzzy - still quite legible, but it is a complex graph with a lot going on - has regressions and formulas etc on the graph, and they need to be very clear.
BTW - the unbound frame is the same size as the Excel chart which gets saved as a .png file. If I tile the images (Access unbound frame and original file in picassa preview) side by side - they are identical - size, orientation etc.
It is not practical for me to try and do the chart natively in access as it is way to complex. I am using access vba to drive an excel session to do all the statistical yack work and chart rendering, then displaying a png image of the resulting chart in an unbound object frame in access.
View 7 Replies
View Related
Oct 18, 2005
Hi,
I have a growing Access database in a multi-user environment over a 10/100Mb network. The database is all in one file at the moment, on a shared directory of our XP-Pro 'Server', and the workstations have a mapped drive to it and are W98SE machines. All the machines are 1.2Ghz Fujitsu Siemens machines.
It is still under development, but is also in constant use, and I therefore have to develop on a copy, then get everyone out so I can copy in the changes. I would love it to be a client/server setup and split the db to Tables only backend on the server and progams on the client, but when I tried, the result was a dramatic slow-down in the system...it became unusable.
I do have a budget for this, and could get a proper 'server' or maybe an Ethernet Disk, but what is the best config for speed and admin purposes. Anyone doing something similar??
Thanks
View 14 Replies
View Related
May 18, 2006
While in datasheet view, i enter a value in a text field, then
press the down arrow key. Access then places (on it's own
i might add), a value (from %$&$% thin air) which it must
believe i wanted to add.
The value seems to be a prediction of what should be
in the field, based on previous entries.
How do I stop such nonsense? Anybody.
View 9 Replies
View Related
Apr 5, 2007
Hey,
I noticed something strange in access 2000: sometimes it takes a long time to calculate a report and other times it goes rapidly. I don't see any process taking a lot of CPU %.
When i do the same thing in Access2003, it goes rapidly every time.
Can anyone help me?
Rik
View 1 Replies
View Related
Nov 2, 2005
I have a number of queries which build two or three Union queries which looks at 35,000 records, and when you open the Union queries or run reports it takes forever for them to open.
I have indexed all the tables which have the common fields to see if this speeds it up the queries, but there still slow. The table they look at are in two different tables which are linked into a front end where the queries are!
View 2 Replies
View Related
Mar 24, 2006
I query against a table with 380K records and growing.
There are approx 14 fields in the table, but I only retrieve 7 in my query. Does having those extra fields in there slow the query down, or does the query ignore them?
Just trying to figure out ways to improve speed.
Thanks.
View 3 Replies
View Related
May 23, 2007
Which is faster, placing a calculation
ItemNumber: IIf([MANITEMNO]<>" ",[MANITEMNO],[ITEM])
in a query or placing
=IIf([MANITEMNO]<>" ",[MANITEMNO],[ITEM])as a Control Source in a text field on a form or report :confused:
View 12 Replies
View Related
Dec 19, 2007
I was brought up to believe in Santa Claus and that DLookups were slow. I browsed last night and found many threads mentioning speed issues with DLookups.
My latest project consists of upgrading a database written at a Client Site that is chock full of (you guessed it) DLookups. My normal inclination was to replace them with parameter queries. But before I did, I ran some benchmarks of DLookup vs Parameter Query speed. In all cases (table1 = 11 records, table2 = 1,143 records) the DLookup was faster.
My test was to lookup a field based on the recordset's primary key. In table2 I tested a record mid way down and the last record. DLookup is the winner hands down.
Should I also stop believing in Santa Claus. Too bad because I have a new notebook on my wish list.
Thanks for your wisdon.
View 1 Replies
View Related
May 24, 2005
Can anyone offer suggestions as to why, when I split my db, place the backend on the server, and open Form2 my front end grows from 2.25MB to 3.95?
I was using macros to filter the data for each type of project and that does not affect the db size nearly as much as this does.
I am using the following code on the On_Click event of the "View Projects" button on Form2 to build the criteria for the records to appear on my "Projects" form.
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strSubtype As String
Dim strStatus As String
Dim strSubtypeCondition As String
Dim strSQL As String
Dim strSortOrder As String
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
For Each varItem In Me.lstSubtype.ItemsSelected
strSubtype = strSubtype & "," & Me.lstSubtype.ItemData(varItem)
Next varItem
If Len(strSubtype) = 0 Then
strSubtype = "Like '*'"
Else
strSubtype = Right(strSubtype, Len(strSubtype) - 1)
strSubtype = "IN(" & strSubtype & ")"
End If
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If
If Me.optAnd.Value = True Then
strSubtypeCondition = " AND "
Else
strSubtypeCondition = " OR "
End If
'Build the sort order
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = " ORDER BY tblProjectDetails.[" & Me.cboSortOrder1.Value & "]"
Else
strSortOrder = ""
End If
'Build the SQL statement
strSQL = "SELECT tblProjectDetails.* FROM tblProjectDetails " & _
"WHERE tblProjectDetails.[subtypeid] " & strSubtype & _
strSubtypeCondition & "tblProjectDetails.[status] " & strStatus & _
strSortOrder & ";"
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("Query1").Command
cmd.CommandText = strSQL
Set cat.Views("Query1").Command = cmd
Set cat = Nothing
DoCmd.OpenForm "Projects", , "Query1"
I have cleaned up my code, compact and repaired, compiled, got rid of unneeded tables, queries, etc. and no change.
I have read up on all of the possible causes/solutions and can't seem to narrow it down.
Thanks for any help,
Toni
View 2 Replies
View Related
Dec 27, 2004
I am making a internet-game. It has for every register player quite some variables stored in a database.
On nearly all pages the database is accessed, modified and closed again. Let's say each player has 20-25 variables stored in the database. Would this cause speed problems? Any ideas to solve this?
View 2 Replies
View Related
Oct 4, 2006
I have a database on a server that is accessed by mobile clients using laptops (broadband)
when out of the office. They use'virtual private network service' to do this.
(I did not set this up, I just design and program the front and back ends)
However some report a slow response time when retieving data from the database file.
Would 'Active desktop' be any quicker?
Any suggestions on how they might speed things up, would be most welcome
'Replication' comes to mind but I think their data must remain up to date at all times.
View 3 Replies
View Related
Jun 5, 2007
Our office runs on a pretty large Access database (v2003). We are on a large hospital network and have about 15 users for our database. It tends to run VERY, VERY slow. Are there other options?
View 4 Replies
View Related
