Hi There SQL masters!!,
We have a relativelly complex Access 2010 database that we are wanting to upsize to SQL.
We have done some upsize testing and most functions seem to work fine at or around the same speed that the current Access back end delivers, but there are other functions that either do not work at all, or are alot slowever in delivering a result than the current Access BE is.
The database has alot of queries in it, about 150, and some of these are pretty long and complex, union queries etc.
The developer I am working with does not have alot of SQL experiance and we are trying to get our heads around making the most of the SQL database engine to run these more complex queries for us rather than having them all in the access front end file.
Can someone explain, in very basic talk, how you go about getting the SQL database engine to run these more complex queries? Do you create a query direct in the SQL management studio and then somehow link it to the Access FE? like the tables link via ODBC?
Someone gave me this example:
You have 100000 records but only want ten returned:
If this query runs direct in the Access FE, all the data for the 100000 records comes down the pipe to access, then it it sorts it to 100 and displays the result.
But if it runs in the SQL BE only the 100 records you want come down the pipe, hence making the process faster with the speed it processes and the drop in network traffic. Is this correct?
Thanks, please be gentle!!
JohnG
We have a relativelly complex Access 2010 database that we are wanting to upsize to SQL.
We have done some upsize testing and most functions seem to work fine at or around the same speed that the current Access back end delivers, but there are other functions that either do not work at all, or are alot slowever in delivering a result than the current Access BE is.
The database has alot of queries in it, about 150, and some of these are pretty long and complex, union queries etc.
The developer I am working with does not have alot of SQL experiance and we are trying to get our heads around making the most of the SQL database engine to run these more complex queries for us rather than having them all in the access front end file.
Can someone explain, in very basic talk, how you go about getting the SQL database engine to run these more complex queries? Do you create a query direct in the SQL management studio and then somehow link it to the Access FE? like the tables link via ODBC?
Someone gave me this example:
You have 100000 records but only want ten returned:
If this query runs direct in the Access FE, all the data for the 100000 records comes down the pipe to access, then it it sorts it to 100 and displays the result.
But if it runs in the SQL BE only the 100 records you want come down the pipe, hence making the process faster with the speed it processes and the drop in network traffic. Is this correct?
Thanks, please be gentle!!
JohnG
