Search results

Hi CJ_London That's great - I didn't realise there's a MsysRelationships table - perfect!:) Many thanks Les

Hi All We've had a few cases recently where a relationship (with referential integrity) between two tables has been broken by access. I think it happens when a record has been corrupted (I'm addressing this issue!). Is there a way to check with code whether all the required relationships are...

Brilliant! That works perfectly - many thanks! Les

Hi All I have written the procedure below to lock/unlock all the fields on a form, and it all works perfectly where the form is open in datasheet view, but when the form is open in continuous form view I get "Object doesn't support this property or method" on line 110/140. I'm guessing the...

Hello, We've got a table that is now obsolete and we want to replace all occurrences of it's name in all tables, queries and reports etc in the database with that of a different table. Is there any decent tool out there which can help do this instead of a manual change? Many thanks, Les

OK, got it! I like the datamart idea - but as you say (stopher) probably not for this case. Thanks folks!!

Thanks for your replies. I understand the principles of normalisation, but my query was really about whether it would be faster to base a report on a single table, rather than on a dataset that uses two tables. I suspect that in this case any speed difference will be negligible (as CJ London...

Hi All I have a table [tblStaff] with a numerical key 'staffID' and a text field 'staffName', and I have a table [tblSickness] with that joins to [tblStaff] on 'staffID'. There are a lot of reports, and functions, that use the data in [tblStaff] and the related data in [tblSickness]. Would it...

Hi CJ_London I realize I could do some of what needs doing manually, but there are a lot of tables involved so I was hoping for a tool to help me! It's the joins that is going to take most of the time. Thanks Les

Hi All I have a complex access2010 accdb in which the key field in the 3 main tables are text fields (e.g. staff_name). I understand that numerical keys are preferable (quite apart from the problem of needing to have two John Smiths!), so I intend to add new numerical keys - probably with data...

Hi Dave Thanks for the speedy reply! Yes - I do have a copy of the pre-compacted accdb with the indices (or is it indexes ;)) intact. I reckon you're right about the compact having partially failed: and I reckon Gates should be warning the user to check indexes after a compact has partially...

Hi All We have a split access2010 application in which the BE is 1.6Gb compacted. It reached 1.8Gb recently, then I compacted it and it returned to 1.6Gb-ish, but some of the FE functionality then virtually ceased to run:eek:. Some (very stressful:banghead:) hours later I discovered that some...

Hi Paul Yep - that seems to have done the trick!! Very many thanks. Les

Hi Folks - just wondering - is anyone still looking at this, or shall I re-post? Thanks hopefully Les

Right! When I tested the query and got the error, this was before I had deleted most of the records: sorry about that:( I now find that if I delete all records from the table practice_bacs where the field 'practice_bacs_submission_date' is empty (for want of a better word!!), the query runs...

Pual I'm really sorry about this: I honestly did test the query in db1, and got the same "Data type mismatch in criteria expression" error - despite not having added the BankHoliday table. Now, having added that table, the query runs fine:confused::confused::confused: I need to investigate the...

Aagh - sorry Paul - I was obviously carried away deleting things:( New db uploaded with that field restored. Les

Hi Paul OK - I've done it the other way round - created a new accdb, imported the relevant tables, query and module, deleted most records, anonomised the rest, compacted and uploaded: so now 'query1' is the problem query, for you/others hopefully to play with;) Thanks as ever. Les

Hi Paul, Paul and Bob Many thanks for your replies. I have tried Paul's suggested query, but got the same result:( I tried adding 'Is Not Null' to the criteria for 'practice_bacs_submission_date': SELECT practice_bacs.practice_bacs_submission_date FROM practice_bacs WHERE...

Hi All I have a query which runs fine until I set a criteria (of True) in the field chase_it: prevwd([practice_bacs_submission_date])<Date() So without the criteria, I have SELECT prevwd([practice_bacs_submission_date])<Date() AS chase_it, practice_bacs.practice_bacs_submission_date FROM...