0

I want to move my current DB from MDB to a MSSQL server. When I have done this through ODBC, queries became particularly slow. I have red that it's caused ODBC, that doesn't support group by and JOIN, join and so it has to do multiple queries and process datas on the current machine.

How can I switch to something faster ( for MSAccess 2003 )?

M4rk
  • 2,172
  • 5
  • 36
  • 70
  • Are the queries slow when run in MS Access? Are you sure that Access is the problem before you take this large and possibly expensive step? How large is your database for example? – Fionnuala Feb 21 '13 at 00:23

4 Answers4

3

Actually, in most cases Access does a decent job. If your query has some joins, but ESPECIALLY has some aggregate group by, then simply create a view, and then link to that view. It will likely run as fast as a pass-through query, but without the hassle and effort on the Access side.

So in most cases, you should be able obtain excellent performance when using Access as a front end to SQL server.

So, for calling store procedures, or for some reports, you can consider using pass-through query.

However, to save time and effort on your part, then for quires with a join and especially those with aggregates like group by or count, then just create a view server side and then link to that.

You find the performance first rate when you do this.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • MS Access is notorious for causing horrible performance problems when used as a front end for SQL Server. There are workarounds, such as pass-through queries, but most Access users are not sophisticated enough to use them. – Tarzan Feb 21 '13 at 16:05
1

just one basic and stupid question: did you define all needed indexes on your SQL database?

Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
0

Have you tried this approach: http://www.packtpub.com/article/transferring-data-ms-access-2003-to-sql-server-2008 ?

To make it repeat itself, at the very last step, tick "save SSIS package" and schedule it appropriately.

This is the by Microsoft for Microsoft approach, so unless there are known issues with it, I'd use it as the optimal approach.

RandomUs1r
  • 4,010
  • 1
  • 24
  • 44
0

While using MS Access, you can use an Access Data Project. This gives you a MS Access front end with a SQL Server backend. The primary benefits of an Access Data Project are (a) you get the stability, scalability, and security of a real SQL database, while (b) still using the well-known MS Access front-end and programming.

See this stackoverflow question for an in-depth discussion: Pros and Cons of Access Data Project (MS Access front end with SQL Server Backend)

Community
  • 1
  • 1
Tarzan
  • 4,270
  • 8
  • 50
  • 70
  • 1
    Adps are not really supported for the more recent versions of SQL Server. Microsoft is recommending linked tables. – Fionnuala Feb 21 '13 at 00:21
  • ADPs work with SQL Server 2008 and earlier. If you need to use a later version of SQL Server then you can still use ADPs, but in that case designing server objects (tables, views, stored procedures, functions and relationships) is done with SQL Server Management Studio. – Tarzan Feb 21 '13 at 01:25
  • 1
    The lastest version of Access does not even support ADP. And SQL server has announced end of life for oleDB which ADO and ADP Access projects require. So, using a ADP Access project, and in fact choosing ADO is a is a very poor choice at this point in time. You are far better off to use linked tables and stick with the Open Database connective option. – Albert D. Kallal Feb 21 '13 at 08:58
  • 1
    The questions is about MS Access 2003. For people that are more comfortable with Access than SQL Server, ADPs are a good choice. – Tarzan Feb 21 '13 at 16:03