Invalid Argument

[nikki2]

Hi

I have a database, with which I have always had problems. Sometimes they were Invalid Argument problems, and sometimes there were others (such as that the record was too large). Some of the tables are very large and only linked in, some are imported. The ODBC is a pervasive odbc engine interface.

In the past, some of the problems have gone away by compacting and repairing the database. Still, it's difficult to keep working with this thing because of the chronic problems with it (I inherited it). It doesn't do much really; some queries are run via a macro to produce two final tables. This is done every month. At this point, it's about two gigs. I copy it to my local pc to work with it and put it back on an sfa when I'm done.

Recently, I used it to add one more table, which was linked. I had similar problems with that table as I had with another large, linked table, so I created a query to make it smaller just as I do with the other table. I was successful in producing a smaller table. I then ran a query that combined data from this and several other tables, into one table.

I copied this one final table into a blank database and did some manipulations. I'm now getting the Invalid Argument message again when I try to copy the table. Also, when I try to delete a table that is an extract of this bigger table, I get a message of 'the ms jet database engine could not find the object '~tmpclp160061. Make sure the object exists and that you spell its name and the path name correctly' (as if I could get that name wrong....).

Per someone's post here, I opened a blank database and tried to import the table creating the problems. I then got a message that I have too many indexes. I'm not sure how to delete these indexes. If I look at the properties of each field in this table, none of them are set to 'Index'.

I have managed to create a new database containing the tables and queries I need. Does anyone have any ideas for what I should do with the original database. It almost seems like that something in the original database is causing the problem even tho I have gotten this table out of that database. Any suggestions would be greatly appreciately. I'm about ready to jump (kidding......)

Thanks
K

[MMcCarthy]

Open table in design view and then go to View - Indexes. These are the table indexes. You will need to delete some of these.

[nikki2]

Open table in design view and then go to View - Indexes. These are the table indexes. You will need to delete some of these.

Thanks. I'll try it.

Stupid question: how did these indexes get set?

[MMcCarthy]

Thanks. I'll try it.

Stupid question: how did these indexes get set?

Access "helpfully" sets them for you when you create primary or foreign keys or a sort order, etc.

[nikki2]

Access "helpfully" sets them for you when you create primary or foreign keys or a sort order, etc.

yes, 'helpfully'. I've gone through and checked each table as you've described, and I don't see any Indexes. It also will not allow me to delete a table-it says the microsoft jet engine has gone on vacation or something-can't find the object. It will not allow me to import data using the odbc connection-it says invalid argument. Won't let me compact and repair-invalid argument.

I usually have to rebuild the whole db. Any ideas would be greatly appreciated.

K

[MMcCarthy]

yes, 'helpfully'. I've gone through and checked each table as you've described, and I don't see any Indexes. It also will not allow me to delete a table-it says the microsoft jet engine has gone on vacation or something-can't find the object. It will not allow me to import data using the odbc connection-it says invalid argument. Won't let me compact and repair-invalid argument.

I usually have to rebuild the whole db. Any ideas would be greatly appreciated.

K

Sounds like corruption.

Try importing the objects into a new database.

[nikki2]

Sounds like corruption.

Try importing the objects into a new database.

That's what I do. I actually created a 'shell', and have to go back and get that and start importing objects into it, which definitely works, but I just don't understand why it keeps happening. I spend a lot of time starting over, rebuilding it because this happens and I can't think of anything I'm doing that could cause it.

Thank you.

[Scott Price]

With just a couple of tables and a couple of queries, I find it somewhat odd that you have nearly 2 gigs of size in this database! If that isn't actually a misprint, I would immediately suspect a couple of things:

Primarily using transferring such a large database over a network is extremely susceptible to corruption introduced by faulty network equipment. This link contains some extremely good information about the possible causes of corruption in linked databases, and reading through your post, the first thing I would check is the network hardware for dropped packets. The IT guys or gals likely won't enjoy having their hardware brought into question, but if you do a simple ping request it could give you an idea if the hardware is, in fact, dropping any packets.

The second thing that comes to mind is that you might benefit from the command line decompile method. Allen Browne's site contains a number of steps to try in recovering from corruption. The decompile command and syntax is on his page as well.

Good luck!

Regards,
Scott

[Stewart Ross]

... Some of the tables are very large and only linked in, some are imported. The ODBC is a pervasive odbc engine interface.
...

Hi. What is it you link to via ODBC? I use Oracle InstantClient to link to an Oracle student records system. InstantClient is distributed and maintained by Oracle itself, but I have found that it is not fault tolerant of (in particular) date-time values it sees as incorrect in some linked Oracle tables, and it stops returning records altogether when faced with just one date-time error in a large dataset. I also find that live ODBC links to the Oracle system are very slow in operation.

For this reason I copy the main tables I link via ODBC to off-line copies on the Access database. There are a lot of rows in some of these tables (175,000 in a student table, for instance). In native Access queries on the off-line tables work well, but via ODBC the performance is so poor (often two or three orders of magnitude poorer than native Access) that I would not be able to work live on these tables at all. the overall DB size of my ODBC off-line linked Db is smaller than yours - 0.5Gb vs 2Gb - but even so I have never experienced any Access data corruption issues in more than a year of daily off-line updates to the tables via ODBC.

Your ODBC driver may be a much better performer than mine, and you might have no integrity issues with the linked tables; however, the constant problems you are experiencing suggest some form of error occurring between the database engine and its tables, which is quite unusual with Access in my experience.

-Stewart

[Denburt]

Even MS Access 2007 has a cap at less than 2 gig. Ive hit it before and it sure does suck sorry to hear this.

http://office.microsoft.com/en-us/access/HA100307391033.aspx

[Denburt]

o.k. one idea that might help is to unlink the huge tables and use the properties sheet in the query to include a "Source connect string" it might not be as quick but it is an idea.

Another idea is that you could create a view (query) in the original database with only the records needed at the time and link to that.

[NeoPa]

Like the others, I think this is a size problem.
Is it possible to remove some of your tables to a back-end database and keep them clear of the main front-end code one.
Sorry if this describes what you've already done somewhat, I wasn't clear exactly what was linked where from your description. If any individual table is getting near that size then you need to consider using other software (at least for the back-end).

[nikki2]

Hi everyone, and thanks for replying

I'll read through all of your comments and links and will hopefully find something that can help me out.

With regard to the comment about the network, the db is local when i'm working with it so I'm not sure that network problems would be causing this but I will investigate this suggestion further. Thank you.

With regard to the comments about size and such, I really do believe that the size is potentially part of the problem because every month, the tables I am linking to do get larger. I will more carefully evaluate all of your suggestions. Thanks to all of you.

I will no doubt have more questions or will at least post what works and what did not, for those interested. Thanks again.

K

[NeoPa]

What lovely manners - always a pleasure :)
Good luck with your investigations.

[nikki2]

What lovely manners - always a pleasure :)
Good luck with your investigations.

I think that everyone was responding to my desperation so having good manners is the least I can do!