I work in a development environment, and need to frequently manually export forms, queries, reports, and even tables over to a production Access database. I've just recently gone to Access 2007, and I have the current service pack and updates installed. The database exports were working ok, until I tried exporting a Table, and then I got the "Reserve error (-1524)" message.

I have re-checked to see that both my development and production databases have all databases set up as Trusted databases, but that has not made any difference.

I'm still getting the error, and can not export a table to another Access database.

Help!

I saw you had asked this in an existing thread and I had responded there, but here's what I wrote:

Were all users out of the database you were trying to export to? What was the name of the object you were trying to export?

I saw you had asked this in an existing thread and I had responded there, but here's what I wrote:

Were all users out of the database you were trying to export to? What was the name of the object you were trying to export?

Thanks bob for a quick response!

I've tried to export tables a number of directions, and have been consistently getting this problem. Many times I need to export from the live database, over to my production, so that I can then have replicated data for real testing. Users are in the live database all day. I used to do this all the time with Access2003. Here's one table name: "FAKS - Capsule". You've given me an idea... I'll play around with table names. I would hate to have to change all table names, database is large.
But I can also make a copy of my user's live database, and then try exporting from it because no one would be in the database but me. I thought I'd tried that, but I'll try it again.

A couple of things -

I know it might not be possible here, but object names (and field names) should not contain special characters (with the possible exception of the underscore) or spaces. That will take care of a lot of potential errors. The hyphen can be a pain because Access thinks you are trying to subtract something and I've seen it cause issues many times when you wouldn't think it should.

Actually, I just found something here:
http://www.granite.ab.ca/access/corruption/symptoms.htm

#38) Reserved error (-1524); there is no message for this error. You have a corrupted record in that table. This is an extremely rare message. I would suggest purchasing some lottery tickets.

A couple of things -

I know it might not be possible here, but object names (and field names) should not contain special characters (with the possible exception of the underscore) or spaces. That will take care of a lot of potential errors. The hyphen can be a pain because Access thinks you are trying to subtract something and I've seen it cause issues many times when you wouldn't think it should.

Actually, I just found something here:
http://www.granite.ab.ca/access/corruption/symptoms.htm

I came into this system after others designed it for 5 years. I probably wouldn’t have given the table names they did, as I agree that spaces and special characters make VB coding more of a pain. However, Access2003 worked fine with hyphens on many different databases I worked with.
I have no reason to think there is corruption in the table, as I have tried the export with a number of tables.

have no reason to think there is corruption in the table, as I have tried the export with a number of tables.[/FONT][/COLOR]
Well, because I know Tony - if he says that is what that error means then I would be willing to back it up and say that is probably your problem. You may have more corruption going on than you realize. How long has it been since you've imported all objects into a fresh, blank mdb file?

Also, is this a split database where the backend is only tables and then a copy of the frontend is on each user's machine? If not, then I would be even more leaning towards the corruption side of things because that is what will happen (not a question of IF but of WHEN and HOW BAD) on a non-split database being used over a network.

It is not a split database, no need to.
I have tried some more testing, I created a blank database, still in 2003 file format. I have been able to export some tables, even a linked table, but some exports still give errors. It does not appear to have to do with the table name, as a successful one does have spaces and special characters.
So leaning towards corruption or something within the table or a field name is what I’ll need to look into. I even get the same error if I do a right click, copy, and paste to create a replicate of the table, within the same database.
I really do appreciate the feedback you’ve given me.

It is not a split database, no need to.
If this is being used over a network then I will argue till I'm blue in the face that it IS necessary. Again - if you don't then it will corrupt - all it takes is ONE dropped packet while someone is connected.

More about that here:
http://members.shaw.ca/AlbertKallal/Articles/split/index.htm

As an Access MVP, I have direct contact with the Access team (the guys who program the actual Access program) and THEY say the same thing. It doesn't matter how "small" and insignificant the database is. If you are using it on a network - not splitting is like playing Russian Roulette.

Are you talking anything different than linked tables?

We have had a lot of linked tables here... people used them for different reasons than an issue of splitting the database... and I can tell you there have been tons of problems with linked tables... too long of a list for me to list here at the moment. They are a headache. Response time in itself is a issue with an application using linked tables.

They are a headache. Response time in itself is a issue with an application using linked tables.
This is not intended to be an insult but that is not true. It has to do with the design of the database and the network infrastructure. It is not that linked tables in and of themselves are slow. The problem lies in that people who have little experience in Microsoft Access can easily build something but it may not be built properly. I'm currently a PROFESSIONAL Access developer with 12 years of experience. I did not consider myself to actually know proper design and implementation until just about 2 years ago, even though I've had a lot of Access experience over the previous 10 years.

So, my question would be "how has this 'negative response time' been dealt with (probably by just not linking tables, eh?).

Here are a couple of articles on that:
http://www.fmsinc.com/free/NewTips/Access/LinkedDatabase.asp
http://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/index.html
http://www.fmsinc.com/MicrosoftAccess/Performance.html

So, anyway - the crux of the deal is this -

Access is a good tool, BUT only if implemented properly.

Corruption WILL occur in Access databases. That is a given. There really is no way to completely keep that from happening. So you have to mitigate the factors that can cause it. And the number one cause of corruption is a lost packet while a user is connected. Your network connection can drop a single packet (and for most programs that isn't a problem. Life just goes on. But for Access it IS a problem and a FATAL one at that).

So splitting the database and linking to the backend on the network while having a COPY of the Frontend on EACH user's machine is the SAFEST way to avoid the threat of corruption. But again - it will not eliminate that risk, only reduce the risk.

Anyway, hopefully you will find the corruption and be able to eliminate it and hopefully some of the articles I've referred to will help with speeding up your databases and linked tables. Good luck (I have a love/hate relationship with Access and understand your frustrations - I've been there and have learned the hard way as well.)

And I am a database developer and Access developer with 30 years of experience. Through the years I've designed some pretty large systems for county governments.
It is not the design. I don't think it is the network infrastructure, because you can do testing as follows.
When you put an Access database/application going through linked tables, side by side with the same Access database/application without linked tables… and nothing terribly complicated, but with tens of thousands of records in the tables… and do benchmarks of access time, counting seconds… the linked tables are slower. I’ve done the benchmarks many times, mostly with Access 2003. I'll need to do more testing with 2007.

Another thing I've noticed, is that there is something about Access 2007 that is much slower for development as well. The development copy is on a network server, I've had to move it to my C drive for the interim. It drags when I'm trying to change forms in design mode on the server, really bad, like 10 times worse than 2003.

I will look through those articles... thanks.

Well, you seem to have your mind made up. I can't say anything more than it has a lot to do with your environment and not just Access. I've had issues before at different client sites due to network and other issues and then using way more linked tables and stuff at other sites and no problem (same program, same service pack level).

So, anyway - good luck in trying to find the actual source of the problem as it isn't easy to do sometimes. And, make sure you get good backups as you will experience corruption - that is a given.

Now I'm done...

To check this all out in 2007, it will take some time to set up some benchmarks... won't happen today on my end. There are a lot of factors... some lengthy string of queries for batch processes, forms with a lot of lookup fields... a lot of forms with subforms of different types, some based on direct sql... so it isn't a 10 minute check-it-out.

Bob, I do want to thank you again... you have given me good feedback.. and I still haven't found what's bad in one or more of my tables.. that too may cross over to another day as I continue to research... but at least I've got a narrower focus, and user's asking for something else right away... agggh.

I still haven't found what's bad in one or more of my tables.. that too may cross over to another day as I continue to research...

Well, I did find my corruption in a table. One record, 2 fields had strange symbols (garbage). I was able to simply delete the field content, and re-enter new text content. After correcting the data, I was able to replicate table and export table, reserve error was gone.

Access 2007 has 'tighter' controls on data than Access 2003. Access 2003 exported this same table with no detection of the field data corruption.

To find the bad data, I copied the table structure to a new table, and then wrote queries to begin appending portions of the old table into the new. It took me about 8 appends, segments at a time, to narrow down and find the bad record. Didn't take all that long to do it once I found time in my schedule.

I checked out one of the suggested performance tips website: One says:
"Every Table Should have a Primary Key. This allows your database application to quickly locate specific records. Additionally, you cannot create secondary indexes on a table's fields unless that table has a Primary Key."

Boy, is that dead wrong.

You most certainly can set up indexes, with or without duplicate values, on fields in a table WITHOUT having a Primary Key.

I do not use Primary Key's unless there is a reason to have a field with a unique value in a table, or unless I need a multiple-field primary key, or you need it in a relationsship model. An index with no duplicates is the same as a primary key. Benchmark time tests have shown me indexes are the way to speed up access to tables via sorts and queries.

http://www.access-programmers.co.uk/forums/showthread.php?t=171030

kathy - i put this thread in the general area, dealing with a backend corruption i had experienced and resolved. corruptions tend to manifest themselves with strange spurious errors, and standard routines start to fail

clearly you understand what you are doing, and yes there is almost bound to be some performance degradation using linked tables compared with non-linked.

one real additional issue with non split databases is that it becomes very hard to modify the functionality - whereas this is easier when the data is separate.

also, if the database itself uses local temporary tables, then you HAVE to have a split database, or ensure only one user can be in the dbs at a time, or one user may overwrite another users temporary table

in balance i would certainly side with bob and separate the data from the code, but its your system - as long as its backed up regularly, no doubt you can always recover to a safe point anyway.

“one real additional issue with non split databases is that it becomes very hard to modify the functionality - whereas this is easier when the data is separate.”

As I’m sure you know, this too depends on the environment… particularly the number of users. I’m working on a database with about 25 users, 24 hours around the clock. It’s easy to make copies of the database, and therefore, I work in a development copy in a different partition until I get my changes made and fully tested. I have had no trouble getting all users to sign off for 15 minutes per day when I need to do an update (export from my development to the production version.) I just schedule it at noon or 5pm.

So I don’t consider this ‘hard to modify’… for me, it is actually very easy. I can’t imagine any developer trying to design in a production database anyway. In all large scale sites I’ve worked in, I’ve never worked directly in the production partitions.

“also, if the database itself uses local temporary tables, then you HAVE to have a split database, or ensure only one user can be in the dbs at a time, or one user may overwrite another users temporary table”

I’ve never had to set up local tables, even in the largest applications I’ve ever designed. I do find some users like to make and maintain their own personal databases… but if they have data that is going to be shared, then I probably set up an automated import so that their data resides in a central database.