?
Solved

Performance in Access 2007

Posted on 2008-02-07
10
Medium Priority
?
516 Views
Last Modified: 2013-11-29
Hello,

This might be a difficlt question to answer without seeing the layout of my tables. But, I will give this a try and hopefully some basic suggestions will help. I am importing quite a bit of data into an Access 2007 database through ODBC and after about 250 MB of data is imported my ODBC connection times out. I suspect that this has to do with Access, but it could be the ODBC connection.

What can I do to improve performance with the Access 2007 database and/or ODBC connection? I do have indexes and the tables are linked together by ID (if that helps).

Thanks,
John
0
Comment
Question by:jhieb
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:dlan75
ID: 20842604
Hi,
From which Database are you importing? 250Mb should be fine, check that your data is not corrupted.
0
 
LVL 1

Author Comment

by:jhieb
ID: 20842764
I am importing from SQL and the data seems to be fine in SQL. So, that is why I am focusing on the Access side of things. With and besides creating indexes, is there anything else i can do to speed up the Access performance?
0
 
LVL 9

Expert Comment

by:tonydemarco
ID: 20844119
Are you are creating a new table from a "linked" SQL table?
250Mb is a large import and is also dependent on your ram size to cache while writing to the table. Why are you importing so much data when it already exists in SQL?
Access works quite well with SQL as a back end.
Can't you just query the attached SQL tables to get what you want without importing the whole table.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 1

Author Comment

by:jhieb
ID: 20844265
Hi Tonydemarco,

The extracts go to customers. My ram size is 2GB. When you mention Cache are you talking about the swap/paging file, or are you referring to something else?

Btw: I don't have a problem when brining this data into Access 2003 databases. My timeout issue happens only with Access 2007.

So, my quesiton is still open (see above). I am still curious if there is anything that needs to change in Access to improve performance. If not within Access, then what do you suggest outside of Access (besides adding more memory)?

Thanks,
John

Thanks,
John
0
 
LVL 9

Expert Comment

by:tonydemarco
ID: 20844586
Check VB Help (Ctl-G or Alt-F11) - ODBCTimeout Property
0
 
LVL 1

Author Comment

by:jhieb
ID: 20845625
Sure. Send me an internet link and I will give you the points.
0
 
LVL 9

Expert Comment

by:tonydemarco
ID: 20845758

Just "Accept" my comment as an answer to award points.

Glad to be of help.
0
 
LVL 1

Author Comment

by:jhieb
ID: 20855563
Hi Tony,

That is not what I meant. Send me the hyperlink to the information you provided so that I can review what you are referring to.

Thanks,
John
0
 
LVL 9

Accepted Solution

by:
tonydemarco earned 2000 total points
ID: 20855773
John,
"Check VB Help (Ctl-G or Alt-F11) - ODBCTimeout Property"
means that the instructions for extending the timeout property is contained within the VB module's help files located on your computer.
While in MS Access do the following:
Alt-F11 for VBA editor then F1 to search Then search for "ODBCTimeout"

Easier yet is just right click in your query in design view and choose properties and change the timeout field.

From Help:
"The ODBCTimeout property is an Integer value representing the number of seconds Microsoft Access waits. The default is 60 seconds. When this property is set to 0, no time-out error occurs.
You can set this property by using the query's property sheet or Visual Basic."

0
 
LVL 9

Expert Comment

by:tonydemarco
ID: 20861850
cheers!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month5 days, 1 hour left to enroll

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question