UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Performance Problem With Access 2013, Access 2013    
 
   
giodev
post Nov 2 2015, 08:17 AM
Post#1



Posts: 142
Joined: 14-September 02
From: Torino - Italy


Hello,
some years ago I developed an application with Access 2007 using the project approach (.adp). It worked fine with good performance.
Now I should migrate it to Access 2013, but the projects are not supported anymore.
I tried to substitute the access to SQL with linked tables, but it is not practically usable because it is drammatically slow.
I tried to use OLE DB connection with ADO commands to access the SQL objects, but in this case I cannot use a lot of masks where many combobox row source are defined with a "SELECT..." statement.
Any suggestion to reach a reasonable result without having to rewrite tons or code and masks design ?

BTW: Why Microsoft decided to stop the project approach ?
Go to the top of the page
 
jleach
post Nov 2 2015, 09:12 AM
Post#2


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


Hello,

One of the easiest and most effective ways to speed up performance with regards to data lists (combo sources, list boxes, etc), is to cache static data to a local sideload database on startup (very quick, no heavy criteria involved) and base the lists off the local version. This is usually quite sufficient for many types of list data that doesn't tend to change often.

I wrote about this (and many other factors) in depth in an article about remote BE performance, but it applies just as equally to any client/server setup: http://www.dymeng.com/techblog/azure-serie...se-performance/

hth
Go to the top of the page
 
giodev
post Nov 2 2015, 09:28 AM
Post#3



Posts: 142
Joined: 14-September 02
From: Torino - Italy


Thank you Jack,
this is a good suggestion. I will try to follow it in my project. And I will read carefully your report.
But, just for info, do you know the real reason why Access discontinued th support for projects (adp) ? As far as I know it worked very well with very good performances.
Go to the top of the page
 
jleach
post Nov 2 2015, 10:09 AM
Post#4


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


I do not. Not a popular decision, I know, but I have no insight as to why support was dropped.
Go to the top of the page
 
giodev
post Nov 2 2015, 10:11 AM
Post#5



Posts: 142
Joined: 14-September 02
From: Torino - Italy


Hi Jack,
I am going to copy an SQL table to the local database with something like this:
'//////////////////////////////////////////////////////////
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB;Data Source=NB-1750;Initial Catalog=VEAM_SQL;Integrated Security=SSPI;"
Set oRst = oConn.Execute("SELECT * FROM SQL_TABLE", adOpenDynamic)
oRst.MoveFirst
Do Until oRst.EOF
CurrentProject.Connection.Execute "INSERT INTO LocalTable (Field1, Field2) VALUES (" & oRst!Field1 & ", '" & oRst!Field2 & "')"
oRst.MoveNext
Loop
'/////////////////////////////////////////////////////////
It seems to work, but is there any faster and more elegant way to do the job ?
Go to the top of the page
 
jleach
post Nov 2 2015, 10:15 AM
Post#6


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


I use a dual recordset approach... with the INSERT method, you invoke the overhead of a new query for each record. Better to use that connection overhead once opening a dynaset recordset.

http://www.dymeng.com/techblog/caching-dat...emote-database/

I routinely use the technique on startup (and throughout the app, but mostly on startup) and load/refresh tens of thousands of records from/to various sources/targets in a matter of a few seconds. Small price to pay on startup, IMO.

hth
Go to the top of the page
 
giodev
post Nov 2 2015, 01:22 PM
Post#7



Posts: 142
Joined: 14-September 02
From: Torino - Italy


Your suggestions are really precious.
One last question: I see in the code found in the page you indicated, that you use DAO recordsets.
I found that with my Access2013 if I try to add reference to DAO, it gives an error.
Is it possible to use your technique also with ADO ?
Go to the top of the page
 
jleach
post Nov 3 2015, 10:15 AM
Post#8


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


Hi, sorry for the delayed response - been a bit tied up here.

You can do the same with ADO, just use the same concept opening an ADO recordset instead of a DAO recordset.

With that said, there should be no reason you can't use DAO... in fact, that's the preferred method as ADO's long term support is questionable (to me, anyway). You ought to be able to connect to the SQL Server tables through the ribbon: External Data -> ODBC (you'll need to set up a DSN to get connected initially, or do it via code). Once that's done, I swap over to DSN-Less connections (google it).

Anyway, you have a few options there. I'm afraid I'm going to be tied up again for the next few days, so replies may be a bit slow.

hope it helps,
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:56 AM