Hello,
We have an SQL backend with our access front end. We use a custom unique ID for the primary keys in our tables that is an INT data type. In the coming months another system that our data base talks to is changing that unique ID to be over 10 digits. To accommodate that change in our system we changed all the data types from int to big int.
This is working across the board but it seems to have caused major latency issues system wide if you want to pull any type of report. In our troubleshooting we found that if you create a query in Access with just ONE table it works fine but once you link two or more tables it slows down considerably. For example I created a simple query that pulled 12 fields from two tables and it took just under 9 minutes to pull 57 records. We also verified that the keys are indexed.
We are using the most recent version of MS Access that comes with the o365 license. Any help would be appreciated in trying to solve the latency issue.
We have an SQL backend with our access front end. We use a custom unique ID for the primary keys in our tables that is an INT data type. In the coming months another system that our data base talks to is changing that unique ID to be over 10 digits. To accommodate that change in our system we changed all the data types from int to big int.
This is working across the board but it seems to have caused major latency issues system wide if you want to pull any type of report. In our troubleshooting we found that if you create a query in Access with just ONE table it works fine but once you link two or more tables it slows down considerably. For example I created a simple query that pulled 12 fields from two tables and it took just under 9 minutes to pull 57 records. We also verified that the keys are indexed.
We are using the most recent version of MS Access that comes with the o365 license. Any help would be appreciated in trying to solve the latency issue.