Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cstlaurent
Starting Member
6 Posts |
 Posted - 2011-07-20 : 20:02:00
|
| We are trying to upgrade an Access Application that is using an ODBC with a DB2 Connector to an SQL server 2008 R2 database. The linked server work well on small table, but on big table like 1 millions records we are getting errors where the MsAccess SQL interface is working fine. We are using the same ODBC...Can someone tell me the difference between MsAccess and SQL server Query and any direction to look for. DB2 Connector Iway 5.2 client..This is simply by doing a select count(*) on a 5 millions record tables. Access manage well within secs...Msg 7399, Level 16, State 1, Line 1The OLE DB provider "EDAOLEDB.1" for linked server "TARS" reported an error. The provider ran out of memory. |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-08-01 : 11:35:20
|
| Optimise the SQL Server table using indexing etc to speed up the queries - and write queries to retrieve only data required, i.e. don't do a select * on the 5 million rows. Ideally write stored procedures in SQl Server to do any processing and just pass parameters / retrieve results etc from your application. |
 |
|
|
cstlaurent
Starting Member
6 Posts |
Posted - 2011-08-01 : 15:13:12
|
The main difference I conclude is MsAccess is using ODBC and MsServer is using OLAP. When connecting to the DB2 Using the Linked server ODBC provider I am able to retreive the data.But MsAccess is still faster  |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-08-02 : 04:17:15
|
| Is the Access Database local to the application or also on a remote server? |
|
|
|
cstlaurent
Starting Member
6 Posts |
Posted - 2011-08-02 : 06:39:37
|
| At this point of development, both are local, but the DB2 database is hosted on an other server. |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-08-02 : 10:10:39
|
| Do I understand correctly, your proposed architecture is an Application in MSAccess to connect to a DB2 database with a linked server to SQL Server Database? |
|
|
|
cstlaurent
Starting Member
6 Posts |
Posted - 2011-08-03 : 08:50:20
|
| We had to convert an existing MsAccess Application to a dot net application using SQL server. When we tried to connect to the DB2 the the MsAccess connection was connecting to, we had issue because we used the OLAP provider of DB2. We change to the ODBC provider and it's working now, but slower thant the MsAccess connection was. When we figure out it was the OLAP that was maybe more powerfull on paper but less conviennent we simply change and it's working find now.. |
|
|
|
|
|
|
MsAccess Vs SQLServer ODBC Connection