Utter weirdness with Access

[Mercutio]

Vanilla Win 2000 SP3 File and authentication server. I've only accessed it remotely, but it appears as vanilla as can be.

It hosts some Access 2000 .mdb files that a small number of VB applications work with. The DBs are tables-only. No macros, no code.

I've been using the apps with test data, and they've worked very well.

Now I'm ready to clear the files out, so that things can go live.

And I can only delete 462 records at a time. I've written queries to do it, done it from the table views, whatever... Select All, Delete... 462 records get deleted.

What about the other 9500?!?

I can do 462 more. And more and more. But I can't delete them all.

I created an MDB file on the server myself. 500 records. Tried to delete the data. When I was done, there were 38 records left. It's happening with EVERY mdb file, and it's using the only version of access it has installed.

I copy one of the files to my desktop (also a 2000 Server). Wham. I can delete records all day long.

Weird, huh?

Anyone ever seen anything like this?

 

[Buck]

I haven't seen anything like that, but it sounds like a permissions issue.

 

[Mercutio]

Since I'm logged in as Administrator, creator/owner of the files, have full control from both the share and the files (not that the share perms matter, since I'm on through terminal services) and the problems are within access, not file-level, I don't think that's the case.

 

[Buck]

Under Tools>Options>Tables/Queries there is a section where you can switch the permissions from User to Owner and visa versa. There are other things in the options, but you are smart enough to have gone through them already.

 

[timwhit]

Why don't you reinstall Access. See if there are any updates for Access.

 

[Will Rickards WT]

Can you use a query like the following?
TRUNCATE TABLE name

This deletes all the records without logging the deletions in sql server.
Also for your table only databases you might consider MSDE.
This is a stripped down version of sql server that is redistributable like access. There are limits on the size of database (2GB I think) and concurrent users (5 I think).

 

[Will Rickards WT]

Also your problem is probably related to transaction logging. Some limit must be getting reached like a transaction rollback limit or something. Enclosing all the deletes in one transaction might help.

Not sure why it works on your machine though?
Are there quota/disk space issues on the server?

 

[Handruin]

Does access even use a transaction log? Truncate table definitely works for SQL server 2000, i've used it in the past to delete hundreds of thousands of test rows.

 

[Mercutio]

I'll have to try that when I get back to working on that problem.

The developer swears up and down that there's a program or option installed on Windows 2000 that's making this happen. I'm swearing up and down that she needs to step away from the crack pipe.

This server is vanilla as can be. It has a 60GB drive that's shared with full control for everyone and no quotas. It has Terminal Services running in administration mode. It's a domain controller that authenticates all of 10 people.

Anyway, I don't think Access 2000 supports transactions either.

 

[Mercutio]

The developer is swearing up and down it's an oplocks problem.

 

[B4RSK]

I used to use Access for quite a bit of stuff too, also with VB front ends.

Had some multi-user problems, and issues with performance over the network, so I changed to MySQL.

WOW! Huge, huge improvement. Access is quite a good solution for single-user setups, but MySQL is a way way better solution if you have more than one user, expecially if they have widly varying hardware performance levels.

With the recent addition of SAP DB to the MySQL codebase, the future looks very bright.

Ian

 

[Mercutio]

The developer I'm working with isn't smart enough to use mysql. In fact, she doesn't know SQL at all.

 

[B4RSK]

How is she getting the data into/out of the Access tables then?

Aah... I guess she is only using those nasty nasty VB controls...

I've never used them, (tried, but found doing it by hand easier!), but they should work with MySQL through the MyODBC driver...

Ian

 

[Mercutio]

She's not even very good with VB. It's kind of scary. Fortunately, it's not a complicated program, either.

 

[Handruin]

If she is used to the access interface...you can setup ODBC and make a connection to MySQL. I set this up at work and it works well. I'm not doing anything complicated with it, I did it to experiment the possibility.

Kinda off topic, but I love reading about the success story's on mysql's website. Even if their biased towards their own product, I love to see the support for being able to run up against the big dogs such as Oracle and microsoft.

I love this article: MySQL Used for 2-billion Row Data Warehousing Application at Fourth-Largest Cable Television Provider
.

 

[B4RSK]

Good idea Handruin! I have also used that when moving a program from Access to MySQL -- it works very well.

You can use the Access Export feature to export the tables and data directly into MySQL via ODBC. This works, but not perfectly -- keys and indexes have to be recreated. There are various programs that do a better job of this, try searching on Goolge for "access mysql convert" or something like that.

Ian

PS I also like reading those stories Handruin!