Performance in Access 2007

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
LVL 1
jhiebAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlan75Commented:
Hi,
From which Database are you importing? 250Mb should be fine, check that your data is not corrupted.
0
jhiebAuthor Commented:
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
tonydemarcoCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jhiebAuthor Commented:
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
tonydemarcoCommented:
Check VB Help (Ctl-G or Alt-F11) - ODBCTimeout Property
0
jhiebAuthor Commented:
Sure. Send me an internet link and I will give you the points.
0
tonydemarcoCommented:

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

Glad to be of help.
0
jhiebAuthor Commented:
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
tonydemarcoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonydemarcoCommented:
cheers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.