Link to home
Create AccountLog in
Avatar of etech0
etech0Flag for United States of America

asked on

Something is still wrong with my Access database...

Hi!

Something is wrong with my Access database, and I don't know what.

It's been slow, freezy, and crashy for a while. It's a split front end - back end database.

I suspect corrupt objects or data, but how on earth am I supposed to know what's the problem?

I appreciate all your help...
ASKER CERTIFIED SOLUTION
Avatar of dimmergeek
dimmergeek
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of etech0

ASKER

Thanks for your response.

I actually have two front ends - an mdb and an accdb (for different users). Issues are in both front ends - as far as I know the back end is fine.
I've tried compact and repair, compiling, decompiling, and reimporting all objects.
It's access 2010; the back end is .mdb and the front end is .accdb.
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sorry, I was not part of any previous question related to this topic.
Did the issues start when you started using two versions of access for your front ends?

I used to be a very avid Access user; but I am now reformed.  Access is a great place to learn databases, but if your DB is that large and used by mulitple people, I would extract all the data you can and upgrade to MSSQL.

You will find it much more reliable.
You can take virtually all of your VBA functions and code them in VBS, or classic ASP fairly easily.
Avatar of etech0

ASKER

@boag2000: I don't think it's that bad that I need options like that. My database is functional, it's just very slow and sometimes freezes, and I'd like to fix that if possible.

@dimmergeek: The reason I reposted was to get the attention of other experts.
I think the issue started some time after we started with two front ends. Do you think that's the problem?
How complicated is it to upgrade to MSSQL? This is a large database with about 150 tables and 250 forms, along with queries, reports, a few macros etc.
I'm sure it's related.  New software versions are never really 100% backwards compatible.
Bear in mind that access dbs themselves get buggy and quirky beyond a certain size.  I always heard 2GB is the point of no return, but I always tried to keep mine below 1GB.  How large is your db?
An upgrade to MSSQL is fairly straightforward.  I moved my dbs over to MSSQL from Access with the built-in utilities and re-wrote all my forms and functions into clasic ASP web pages.  The sites are SO much faster notw and I have been able to delploy the tools globally with little in te way of headaches.
Avatar of etech0

ASKER

The back end is about 300mb, and the front ends are about 100 and 20mb (accdb and mdb).
Not so close to 2gb, but who knows?

When you say that you rewrote your forms, that sounds like a huge job, considering that a) I have about 250 forms, and b) I don't know asp.

I've also heard of using a SQL Server back end, with an Access front end. Do you think that could help? Is that simpler?
You really need to post a sample of this database.

"slow and sometimes freezes" are very broad symptoms that could have many causes.

Actually examining thew db will give us a clearer picture of what the issue might be.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
Avatar of etech0

ASKER

Whew - that's quite a list!

How should I know which objects pertain to the issue? I don't know what the issue is, and the slow behavior seems to be pretty much across the board.
Then post the whole thing...
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of etech0

ASKER

Can't post the whole thing - privacy and all that. It would take HOURS to disguise the data. I could just clear out all records from all tables, but that would leave you nothing to work with.

How complicated is it to upgrade to an SQLSVR backend? Might not be a bad idea, especially because we have remote users who currently get in through remote desktop.
It's easy to update to a SQL server backend, especially with Access.
It has built-in tools for exporting to SQL and then you can just use linked tables.
Avatar of etech0

ASKER

Is it easy even if I don't know SQL Server?

I'm willing to learn, but I can't interrupt use of the database. If it could be seamless, it could work.
If you have access to SQL server, you can simply copy all your existing tables over to SQL server.
Then, in your master DB, link all your tables to the SQL server tables.
Your front-end databases will automatically sync up because (i presume) they are linked tasbles to your master.
Therefore, as long as you make sure all your data types map over properly to SQL from Access using wizard built-in to Access, you should be okay.
Avatar of etech0

ASKER

What do you mean by "as long as you make sure all your data types map over properly to SQL from Access using wizard built-in to Access"?

Also, I don't have access to an SQL server. We do have a server, but I think it's running mysql. Can it also run SQL Server?
What data types do you have in your current DB?
Is it all datetime, text and numbers?  They should be fine.
If you have GUIDs or anything like that, you may need to do other tasks to get the data in.

As fo ryour servers, you would need to talk to your server admins.
Avatar of etech0

ASKER

What are GUIDs?

I think that it's all autonumber, text, numbers, currency, dates, hyperlinks, boolean, etc. But I'm not sure. There's someone else here who built a lot of this database, so I can't know for sure.

I'll try to find out, though.

I think that it's all autonumber, text, numbers, currency, dates, hyperlinks, boolean, etc. But I'm not sure. There's someone else here who built a lot of this database, so I can't know for sure.

If this is the case, you should be fine
What format(s) is/are your autonumbers in?  Ints?
Might be simpler if you post the two databases, with empty tables.
Avatar of etech0

ASKER

Ints. What else could autonumbers be?

I'll post an data-free database on Monday.

Thanks!
Autonumbers can be ints, GUID (globally unique identifier), bigint, tinyint text strings with prefixes, etc.
Avatar of etech0

ASKER

even in access? I didn't know that. I've always used ints.
In Access, autonumber is a long (same as int in sql server) or a replication ID (analogous to a GUID)
Avatar of etech0

ASKER

How do I know which it is?
In table design view, look at format.  If it were a replication ID, it would not look like any number you know. {1fd4598cda88e, etc}
Avatar of etech0

ASKER

Sorry for the delay.

In preparation for uploading the database, I  found something interesting.

I made a copy of my front end, and deleted all the tables. (These are mostly linked Access tables, but some are linked to AS400, and a couple to Excel.)

I then reimported all tables from the backend, importing the data as well (ie: not linked).

Then, I decided to check if it was still operating slowly. Lo and behold, the database was speedy fast!

I then deleted those tables, and relinked this front end to the back end. It's not as fast as it was, but it's still pretty fast.

I wonder if there were some old tables or something that were messed up.

I'm going to try relinking the Excel files, and the AS400, and see what happens.
Avatar of etech0

ASKER

The new accdb, with only the access linked tables, got slow. Does that mean that there's a problem in the backend?
Avatar of etech0

ASKER

The new accdb, with only the access linked tables, got slow. Does that mean that there's a problem in the backend?