Hello team,
I have imported several tables (8 tables and they have records between 150,000 to 1000) into Microsoft Access with lots of hardship.
I am going to put queries on these tables, Microsoft acts and it doesn't allow me to add the tables to grids, I have run compact and repair. It is very unfortunate, the database doesn't behave as it is supposed.
Any tips.
Regards,
BittenApple
Access Database is too slow
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Database is too slow
As in your previous question, it is impossible to provide helpful advice without knowing what the database looks like.
Best wishes,
Hans
Hans
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hello Hans,
I ran some compact and repair and now it is better.
This is what I have:
I don't have a pk because I can join tables through 3 multiple field that makes the join unique, for queries that I have inserted, this method worked. Could being slow be the result of not having a pk?
Does this explanation suffice your mean?
Please let me know.
Regards,
BittenApple,
I ran some compact and repair and now it is better.
This is what I have:
I don't have a pk because I can join tables through 3 multiple field that makes the join unique, for queries that I have inserted, this method worked. Could being slow be the result of not having a pk?
Does this explanation suffice your mean?
Please let me know.
Regards,
BittenApple,
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Database is too slow
Try creating the primary key on the combination of the three fields.
Best wishes,
Hans
Hans
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hello Hans,
I can create a composite key by combining multiple fields
MemberID + MemberName + MemberDOB + diseasetype
this is what I have in tableA
1 Gina 01/01/2011 diseaseA
1 Gina 01/01/2011 diseaseB
2 Mary 01/11/1945 diseaseA
=================================
I have this data in tableB
1 Gina 01/01/2011 diseaseA
1 Gina 01/01/2011 disease
2 Mary 01/11/1945 diseaseC
3 Tina 01/02/1987 diseaseC
I Make a composite primary key of these and I know how to join them in SQL.
How can I connect these fields in relationship window? Would it be many to many? one to many?
Please guide me through this.
Regards,
BittenApple
I appreciate your help.
Regards,
BittenApple
I can create a composite key by combining multiple fields
MemberID + MemberName + MemberDOB + diseasetype
this is what I have in tableA
1 Gina 01/01/2011 diseaseA
1 Gina 01/01/2011 diseaseB
2 Mary 01/11/1945 diseaseA
=================================
I have this data in tableB
1 Gina 01/01/2011 diseaseA
1 Gina 01/01/2011 disease
2 Mary 01/11/1945 diseaseC
3 Tina 01/02/1987 diseaseC
I Make a composite primary key of these and I know how to join them in SQL.
How can I connect these fields in relationship window? Would it be many to many? one to many?
Please guide me through this.
Regards,
BittenApple
I appreciate your help.
Regards,
BittenApple
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hello Hans,
The data comes from external resources. They belong to two different month: November and December and for different contracts. For example: Contract A for month November and contract A for Month December, contract B for these months and contract c for this months and etc.
in the beginning I was thinking to use union query to combine all the data for November and all the data for December; when my database didn't behave as I wanted, I thought it is better to keep data in small amounts rather then load them in one table only.
I did a left query with a null to find the members who are in November file but not in December file.
I did a left query with a null to find the members who are in December file but not in November file.
Those who are in November file but not in December file should be compared with another table which is the list of members who are released from hospital...
When I use the tables to find out matching or difference of records in each dataset, I don't generate PK. However, if I want to create a database for our department needs to have forms and other object, I would create pk.
Please let me know if this is not clear.
Regards,
BittenApple
The data comes from external resources. They belong to two different month: November and December and for different contracts. For example: Contract A for month November and contract A for Month December, contract B for these months and contract c for this months and etc.
in the beginning I was thinking to use union query to combine all the data for November and all the data for December; when my database didn't behave as I wanted, I thought it is better to keep data in small amounts rather then load them in one table only.
I did a left query with a null to find the members who are in November file but not in December file.
I did a left query with a null to find the members who are in December file but not in November file.
Those who are in November file but not in December file should be compared with another table which is the list of members who are released from hospital...
When I use the tables to find out matching or difference of records in each dataset, I don't generate PK. However, if I want to create a database for our department needs to have forms and other object, I would create pk.
Please let me know if this is not clear.
Regards,
BittenApple
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hans,
In regards of my database being slow:
You suggested me to create a pk from the combination three fields.
The problem is: how to link the fields of a composite key to the fields of another composite key in relationship window? Should I link them one to one? or one to many or many to many?
Regards,
BittenApple
In regards of my database being slow:
You suggested me to create a pk from the combination three fields.
The problem is: how to link the fields of a composite key to the fields of another composite key in relationship window? Should I link them one to one? or one to many or many to many?
Regards,
BittenApple
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Database is too slow
You don't have to specify that in the Relationship window. Just tick the check box for 'Enforce referential integrity'.
Best wishes,
Hans
Hans
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hans,
I have been struggling with this database and my life is hell. I have not split the tables yet per your instruction to create pk. The database is very slow and runs like a snail; queries run very slow. I inserted composite index on MemberID field and MemberName and MemberDOB, that helped a lot, but it is still slow. Now, I will go ahead and split the tables per your instruction. I feel some hope.
Regards,
BittenApple
I have been struggling with this database and my life is hell. I have not split the tables yet per your instruction to create pk. The database is very slow and runs like a snail; queries run very slow. I inserted composite index on MemberID field and MemberName and MemberDOB, that helped a lot, but it is still slow. Now, I will go ahead and split the tables per your instruction. I feel some hope.
Regards,
BittenApple
-
Mark L
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: Access Database is too slow
I don't know the reason for that composite index on MemberID, MemberName, and MemberDOB. Is MemberID the PK? Should it be? I would just have separate indexes on the other 2 fields rather than having them part of a composite. You really need to have a PK on each table, even if it is merely an autonumber field. Access just likes it better.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hello Mark,
By Mark: "I don't know the reason for that composite index on MemberID, MemberName, and MemberDOB.
Ok, I watched a video tutorial for composite index. I can put index only on one field individually and make MemberID a pk on Left table.
Is MemberID the PK? Should it be?" It can be a pk on Left table if I normalize the data. I might have to create a composite pk: MemberID+MemberName+MemberDOB+Visit
Thanks for your help. I have collected/ learned several things over these past last days. I am going to split tables(only into two tables), put a pk on them and then put index on those fields that I want to do the join. I have not gone by this method yet. That might take care of my slow database. I just don't understand why I should split the tables and then at the end for the purpose of reporting, I need to join them back. Isn't it better not to split them at all because I am going to do reporting, just assigning a pk and index on some of the fields planned for joining?
Anyway, I am going to follow what Hans told me in previous posts.
Table Analyzer didn't work for me to normalize my tables. I wonder if you use it at all. I was able to split the tables with make-table query.
I hired a local tutor and met him in a coffee shop right now to end my challenges, he was not that help which I was expecting...!
Thanks for all you did.
Regards,
BittenApple
By Mark: "I don't know the reason for that composite index on MemberID, MemberName, and MemberDOB.
Ok, I watched a video tutorial for composite index. I can put index only on one field individually and make MemberID a pk on Left table.
Is MemberID the PK? Should it be?" It can be a pk on Left table if I normalize the data. I might have to create a composite pk: MemberID+MemberName+MemberDOB+Visit
Thanks for your help. I have collected/ learned several things over these past last days. I am going to split tables(only into two tables), put a pk on them and then put index on those fields that I want to do the join. I have not gone by this method yet. That might take care of my slow database. I just don't understand why I should split the tables and then at the end for the purpose of reporting, I need to join them back. Isn't it better not to split them at all because I am going to do reporting, just assigning a pk and index on some of the fields planned for joining?
Anyway, I am going to follow what Hans told me in previous posts.
Table Analyzer didn't work for me to normalize my tables. I wonder if you use it at all. I was able to split the tables with make-table query.
I hired a local tutor and met him in a coffee shop right now to end my challenges, he was not that help which I was expecting...!
Thanks for all you did.
Regards,
BittenApple
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Database is too slow
In the patient table, each MemberID should only occur once, so you can use that as primary key. Or you could use an AutoNumber field.
In the visit table, you could use MemberID and VisitID as primary key. Or use an AutoNumber field as primary key.
Using MemberID+MemberName+MemberDOB+Visit as primary key is NOT a good idea. MemberName and MemberDOB depend on MemberID so they should NOT be in the primary key.
In the visit table, you could use MemberID and VisitID as primary key. Or use an AutoNumber field as primary key.
Using MemberID+MemberName+MemberDOB+Visit as primary key is NOT a good idea. MemberName and MemberDOB depend on MemberID so they should NOT be in the primary key.
Best wishes,
Hans
Hans
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hello Hans & Mark,
I set the MemberID in left table as a primary key; on the right table, I added autoNumber as primary key; and then I added index on the field by which I want to join the tables; I came to save the table because I want to close the table; I get this message: "A file sharing lock count exceeded. Increase MaxLocksPerFile Registry entry." I googled it and it says that I need to go to registry. I don't want to mess up with registry.
I created composite key and I combined MemberID and VisitID as Primary key; It doesn't allow me since it creates duplicate fields and violates pk. I thought I should go back to combining MemberID with VisitID and some other field to get unique values for PK. The db doesn't allow me to do so, because it says PK can't contain Null values.
I thought to join tables through MemberID even if I can't insert a pk on my right table. No success. It says that your right hand side table has some memberIDs or visa versa and it can't enforce referential integrity.
I should say that I tried the pk on the right hand side table after I ran make-table query and the table was created.
I have no idea as what to do next.
Regards,
BittenApple
I set the MemberID in left table as a primary key; on the right table, I added autoNumber as primary key; and then I added index on the field by which I want to join the tables; I came to save the table because I want to close the table; I get this message: "A file sharing lock count exceeded. Increase MaxLocksPerFile Registry entry." I googled it and it says that I need to go to registry. I don't want to mess up with registry.
I created composite key and I combined MemberID and VisitID as Primary key; It doesn't allow me since it creates duplicate fields and violates pk. I thought I should go back to combining MemberID with VisitID and some other field to get unique values for PK. The db doesn't allow me to do so, because it says PK can't contain Null values.
I thought to join tables through MemberID even if I can't insert a pk on my right table. No success. It says that your right hand side table has some memberIDs or visa versa and it can't enforce referential integrity.
I should say that I tried the pk on the right hand side table after I ran make-table query and the table was created.
I have no idea as what to do next.
Regards,
BittenApple
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Database is too slow
Please study the articles that I posted links to in Post=199565 more carefully.
Best wishes,
Hans
Hans
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hans,
I will surely study it.
Meanwhile, before I got this post, I deleted all the Nulls and tried to create unique identifier for each record at many side, but as soon as I come to insert a pk on the created unique field, it gives me an error message as Increase MaxLocksPerFile Registry entry. At least it has to accept the AutoNumber as PK, but it doesn't.
Thanks for all, I will work on it.
BittenApple
I will surely study it.
Meanwhile, before I got this post, I deleted all the Nulls and tried to create unique identifier for each record at many side, but as soon as I come to insert a pk on the created unique field, it gives me an error message as Increase MaxLocksPerFile Registry entry. At least it has to accept the AutoNumber as PK, but it doesn't.
Thanks for all, I will work on it.
BittenApple
-
HansV
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Database is too slow
If you still have problems, could you do the following?
- Create a new database.
- Import the tables into the new database (we don't need to see forms, reports etc.)
- Remove all but a handful of records from the tables.
- Alter personal/sensitive information: don't leave real names, addresses, social security numbers etc. in the tables, but keep it consistent.
- Compact the database (Database Tools > Compact and Repair Database).
- Create a zip file containing the database.
- Attach it to a reply.
- Create a new database.
- Import the tables into the new database (we don't need to see forms, reports etc.)
- Remove all but a handful of records from the tables.
- Alter personal/sensitive information: don't leave real names, addresses, social security numbers etc. in the tables, but keep it consistent.
- Compact the database (Database Tools > Compact and Repair Database).
- Create a zip file containing the database.
- Attach it to a reply.
Best wishes,
Hans
Hans
-
BittenApple
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Access Database is too slow
Hello Hans & Mark,
I currently handled this issue with copying the structure of the table and paste it as only structure and then I added AutoNumber as PK, then update the table with the data of the other table. I went by this method and it worked; I only did it for one table though. I need to go through all the tables to see whether I get an error or not. I will update every body.
Thanks for all your support.
BittenApple
I currently handled this issue with copying the structure of the table and paste it as only structure and then I added AutoNumber as PK, then update the table with the data of the other table. I went by this method and it worked; I only did it for one table though. I need to go through all the tables to see whether I get an error or not. I will update every body.
Thanks for all your support.
BittenApple