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!
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?
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
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?
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 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;
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.
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??
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.
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
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?
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.
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.
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??
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.
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.
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!
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?
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:
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.
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
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?
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.
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?
