May 7, 2010 at 1:22 am
Hi.
i was able "after a long time of trial and error" to migrate access database to sql 2005 and use linked tables to access the sql backend from the MS Access front end .MDB file. using upsizing wizard.
all seemed to be fine. working remotely, local adding records.
one thing is driving me crazy is that when we search against a table in access it takes long time.
i ran the profiler on sql server and i see alot of "exec sp_execute'
i am not very god in access . but on SQL i tried to create indexes"non clustered" on the fields we search by, and nothing..
please point me in the right direction!
please if any one can help please let me know...
Thank you
July 2, 2010 at 10:33 am
ehamouda (5/7/2010)
Hi.i was able "after a long time of trial and error" to migrate access database to sql 2005 and use linked tables to access the sql backend from the MS Access front end .MDB file. using upsizing wizard.
all seemed to be fine. working remotely, local adding records.
one thing is driving me crazy is that when we search against a table in access it takes long time.
i ran the profiler on sql server and i see alot of "exec sp_execute'
i am not very god in access . but on SQL i tried to create indexes"non clustered" on the fields we search by, and nothing..
please point me in the right direction!
please if any one can help please let me know...
Thank you
July 2, 2010 at 10:39 am
1. Where is SQLServer running? Is it on the same file server that the .mdb file was on or was it on another server? Is the other server located in the same facility as the file server?
2. What results do you see when you use SQL Server Management Studio? SQL Server MS is GUI interface that allows you to work with a database directly in a manner similar to Access DB window. If you run a query in Management Studio, the performance should be similar to that when the same query is run in Access. For the sake of testing, try a simple query such as selecting the Top 100 rows of a table.
3. Do the Access forms use query objects or is the SQL Statement explicity stated in the RecordSource of the form? Now that you've upsized to SQL Server, you do need to go through and check all of the queries and RowSource's of the Forms & Reports and shift the work of doing table JOIN's to SQL server via Views. When Access does a join, regardless of the backend database, that adds overhead right off the bat. It has to do them when connecting to an Access DB, however it can all be shiftted to the backend when using a commerical database such as SQL Server.
Side Note: If you have tables that the users should NOT be able to update that should be READ ONLY, create a stored procedure that returns the records in that table.
April 15, 2011 at 8:49 am
Hi,
I am experiencing another issue while Migrating the Backend database from MS Access 2003 to SQL Server 2005. Have also got linked tables. The front end is MS Access 2003.
I have migrated using the wizard and it was successful including the Data import. But when I run the front end (Access 2003), I get errors like File not found (error 53 on a form) or Invalid use of Null Error 3078, something like that.... Though I get these errors, I am still able to run the application with errors. Could you please let me know how to progress on this... If you have faced any errors of this kind, please help.
Thanks.
April 28, 2011 at 10:03 am
This is not the first time that I heard of a performance issue after migrating from Access to SQL Server.
I would answer David's question because they are very important.
Placement of the mdb and the Servers is very important as was previously recommended.
Also if you see sp_prepare, sp_execute, sp_unprepare that is Access trying to execute a lot of individual queries and this results in a lot of network traffic. Access may execute sever individual queries for on Local Access Query and it does the processing on the workstation. I would recommend creating Stored Procedures and rather than creating views use in-line views or subqueries in your SP's
You can run the wizard to convert you tables but do you have the correct indexes. You should have a Primary Key Constraints on each Table. You should have FK Constraints and they should be Indexed to improve performance.
Make sure that you have to Clustered Index on the correct Column.
After you updating Statistics?
Are your mdb's stored on a Server or on each individual workstation?
If you have the time convert Local Access queries and extract SQL Code from behind the forms. Choose queries in which you get you Maximum Return on your Investment.
There is a lot more to do. When you migrated to SQL Server did you have a Development or at least a QA Environment?
If you can you may want to set up a Test environment so that it makes it easier to identify problems.
Are you using SQL Server or Windows Authentication?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 29, 2011 at 12:29 pm
I'm with the Corgi on this one. I've done many migrations from native Access to Access front end with SQL Server back end. I used the upsizing wizard once many years ago and never used it since then. I examine each table and create it on the SQL Server side now.
There's no substitution for understanding what is going on at both ends and being somewhat expert at both SQL Server and Access.
For bound forms based on queries to linked tables the queries usually arrive at the SQL Server side fairly intelligently - if the tables have the proper indexes on them (always re-link the table after adding an index). There is, however, a tipping point in what Access can handle in a bound form. I had an order entry system that maxed out at around 45,000 orders and then it became slow. The order form was bound to the orders table and the order detail sub form bound to the order detail table. There were a few bound controls - mostly combo boxes - that were bound to customers and products and so forth.
Remember that when you open a bound form, the entire record set is read. When the tipping point is reached you have to come up with a different strategy for the user forms. There's no way around it that I've been able to come up with. My solution to the order entry problem was to create local Access tables for orders and detail in the Access MDB. The form was bound to those local tables (each user had a copy of the MDB on their desk top). There was VBA code that could read in the desired order/order detail into the local tables. The user would edit that and then submit the changes (kind of like a web front end) and then VBA code would push it back out to SQL Server. This has been in place for 10 years with the number of orders now at around a million. There is no performance problem.
The idea is that when you reach the tipping point, you have to get creative, write code and create search forms that will find what the user wants and only deal with that in the main forms.
If you haven't reached the tipping point, then you have to analyze the queries that Access is sending. Get a test server and database for SQL Server and link your Access tables there. Run the Profiler and capture what Access is sending when you open a form. Are your joins on indexed columns and so forth.
Todd Fifield
April 29, 2011 at 12:54 pm
If you are using an ODBC connection make sure the provider is the "SQL Native Client" version 9.x for 2005, and not "SQL Server" which is the old 2000 version. The native client can be downloaded it is sqlncli.msi
April 29, 2011 at 2:15 pm
I had some painful experiences using various versions of ODBC Drivers with Oracle Versions 8i, 9i, 10g & 11i.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 29, 2011 at 2:43 pm
Note that you can also use SQL Server Native Client 10 (the current version) with SQL 2000 and 2005 as well as 2008 - it seems to be the most solid of the drivers. Another thing that hasn't been mentioned is adding a timestamp field to each of the SQL Server tables. Access versions from 2003 forward do that, but the doesn't add the timestamp to all tables. In multi-user situations not having a timestamp can cause errors such as you are seeing. See Using the Upsizing Wizard on the Microsoft Office site for detailed info on upsizing. That said, we always build the tables and relationships in SQL Server, as has been suggested.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
June 8, 2011 at 5:03 am
Hi! If you have large linked tables and join them using queries in Access you can have a performance problem. You can try to use views on the server instead of queries local.
June 11, 2011 at 12:07 pm
naphan.710 (6/7/2011)
thanks
What does your ad have to do with this thread?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 11, 2011 at 5:22 pm
naphan.710 (6/7/2011)
thanks
Spam reported.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2011 at 1:40 am
when using SQL views, joining take place on the server which generally gives a better performance than locally by MSAccess queries. So you can replace your queries by SQLServer views and attach these as linked tables. Though there are some restrictions when you want to update.
June 15, 2011 at 5:10 am
Hi
The MS White Paper Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204(v=sql.90).aspx
contains a useful and fairly exhaustive discussion of the issues and the means to address them.
Cheers
June 19, 2011 at 10:09 am
Microsoft Access can be a marvelous Front End application to a SQL Server database, IF you plan well. Here are some quick tips
USE TIMESTAMPS ON EVERY TABLE! Without a timestamp, Access makes a column-by-column comparison on every column in your table for DML queries. So, a simple update like:
UPDATE t1
SET C1 = XX
WHERE ID = N,
is converted to,
UPDATE t1
SET C1 = XX
WHERE t1.c1 = t1.c1, t1.c2 = t1.c2, t1.c3= t1.c3….,
This can cause full-table-scans and locks. Just watch the sql profiler and you will be amazed at the differenece a timestamp makes! This is the biggest mistake that all my students and customers make when using Access against SQL server and it causes everyone to poo poo Access.
USE FILTERS. Depending on your access version and connection, filters are processed server side, so you don’t need to dump the entire table to the Access client in order to filter your SQL server data. see http://support.microsoft.com/kb/304259.
USE PASS THROUGH QUERIES. Depending on your access version and connection, Access queries use the local Jet engine to perform query work. So, selecting one record from a table of 1 million records may require all 1 million records to be dumped to the client in order for Access to process the query and return one record. See http://support.microsoft.com/kb/303968.
QRYTMP
I use one Access query (qrytmp) to run all my procs. It returns and DAO recordset that can be bound to forms and reports, or just browsed in the Access query analyzer. So, I use ONE Access query for EVERYTHING!
Remember, however, passthrough queries return non-updateable recordsets. you still need linked tables and/or access queries and filters for that.)
Public Function ram_fnCreateTmpQuery(strSQL As String, intTimeOut As Integer, blnReturnsRecords) As Integer
Dim qdf As DAO.QueryDef
On Error GoTo Proc_Err
ram_fnCreateTmpQuery = 1
' Creates a temporary access query to call stored procedures
' where strSQL is any T-SQL statement, such as
' EXEC sp_WHO2
' UPDATE t1 SET C1 = X WHERE t1.ID = nn
'Delete the Query if it exists
On Error Resume Next 'in case qrytmp does not exist.
DoCmd.DeleteObject acQuery, "qryTmp"
On Error GoTo Proc_Err
Set qdf = CurrentDb.CreateQueryDef("qryTmp")
With qdf
.Connect = gostrODBCConnect 'Use this if u you have a global ODBC or DSN Connection string
'CurrentProject.Connection 'http://support.microsoft.com/kb/281784
'CurrentProject.AccessConnection 'http://support.microsoft.com/kb/281784
.SQL = strSQL
.ReturnsRecords = blnReturnsRecords
.ODBCTimeout = intTimeOut
End With
ram_fnCreateTmpQuery = 0 ' No Error occured
Exit Function
Proc_Err:
ram_fnCreateTmpQuery = 1 ' An error occured
Exit Function
qdf.Close
End Function
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply