By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,652 Members | 1,701 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,652 IT Pros & Developers. It's quick & easy.

Invalid Argument

P: 10
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
Feb 1 '08 #1
Share this Question
Share on Google+
23 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Open table in design view and then go to View - Indexes. These are the table indexes. You will need to delete some of these.
Feb 5 '08 #2

P: 10
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?
Feb 5 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Feb 5 '08 #4

P: 10
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
Mar 4 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 4 '08 #6

P: 10
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.
Mar 4 '08 #7

Scott Price
Expert 100+
P: 1,384
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
Mar 4 '08 #8

Expert Mod 2.5K+
P: 2,545
... 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
Mar 4 '08 #9

Denburt
Expert 100+
P: 1,356
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/ac...307391033.aspx
Mar 4 '08 #10

Denburt
Expert 100+
P: 1,356
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.
Mar 4 '08 #11

NeoPa
Expert Mod 15k+
P: 31,662
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).
Mar 5 '08 #12

P: 10
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
Mar 6 '08 #13

NeoPa
Expert Mod 15k+
P: 31,662
What lovely manners - always a pleasure :)
Good luck with your investigations.
Mar 6 '08 #14

P: 10
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!
Mar 6 '08 #15

NeoPa
Expert Mod 15k+
P: 31,662
It's nevertheless a pleasure dealing with you :)
Mar 6 '08 #16

P: 10
It's nevertheless a pleasure dealing with you :)
thanks! you too......i'm glad i found an environment that welcomes all......
Mar 6 '08 #17

P: 10
It's nevertheless a pleasure dealing with you :)
while you're there, i've got kind of a silly question-could it be that i am bouncing back and forth betwee 2000 and 2003 when opening the database, and that's what's causing the corruption?
Mar 6 '08 #18

Denburt
Expert 100+
P: 1,356
it's about two gigs
Since I'm here.

Even close to the 2 gig limit is enough to make it bomb out and completely corrupt the db. I don't think switching between versions should cause you grief. I do that quite often, with many different versions over the years.

See the post:
http://www.thescripts.com/forum/post3099476-10.html
Mar 6 '08 #19

P: 10
i think i've got the perfect solution: tel my boss i don't want to work with databases anymore.......thanks!
Mar 6 '08 #20

Denburt
Expert 100+
P: 1,356
No! Don't do that db's can be lots of fun once you understand them. Not to mention decent money can be a result as well. Have you tried to implement any of the suggestions mentioned in the previous posts? If so which one and where do you stand?
Mar 6 '08 #21

NeoPa
Expert Mod 15k+
P: 31,662
i think i've got the perfect solution: tel my boss i don't want to work with databases anymore.......thanks!
LOL
Great idea - except you'd be losing out on a lot of fun. The idea of seeing their face when you told them would be worth a laugh though :D

Anyway, can you explain what is actually in the db that gets corrupted. I'm thinking of how big that one actually is (rather than databases it simply links to)?
Mar 7 '08 #22

P: 10
I haven't worked with it in a couple of weeks. but check this post at the beginng of April if you want to see me yelling and whining about it. As far as I can remember, it just doesn't open the tables, there are no links in any of them, it doesn't allow me to compact and repair, it's only two of the tables and they're both linked, and that's all I can remember. But when I start whining in April, I will try to be methodical in my whining. No guarantees about my yelling tho. Thanks!
Mar 18 '08 #23

NeoPa
Expert Mod 15k+
P: 31,662
Sounds good. I'll wait for the details as I cannot progress without reliable details.

As soon as you post in here it will come up as a flagged thread that needs looking at, so no worries about continually monitoring ;)
Mar 18 '08 #24

Post your reply

Sign in to post your reply or Sign up for a free account.