Invalid Argument Error Microsoft Access 2003 SP3

For about three days, I have been trying to make one simple, make table, query run properly. The query seems to run right up until few seconds or so and then it errors out with the following error message:

Invalid Argument

Once it errors out, I cannot save the query. I get the same error message. I must save the query first and then run. I have ported this query to three different servers and the result is the same. Two servers are local and one is the other at our provider in Charlotte, NC (Titan Technologies). Titan is the administrator of our JD Edwards ERP system.

All of the servers are using Microsoft Access 2003 SP3.

I suspect that the problem could be related to the fact that the result of this query will be a table with about 3 1/2 million records. Am I just asking too much of Microsoft access?

My query:

SELECT PRODDTA_F0911.* INTO [tblF0911(GDS-4160-09202012-02)]
FROM PRODDTA_F0911
WHERE (((PRODDTA_F0911.GLAID)="00638625") AND ((PRODDTA_F0911.GLSBLT)="W"));

I have also attempted to run the query using Microsoft SQL Server 2008 Management Studio and I cannot seem to get it to create a table. I can display the 3.5MM records but that is too many to past into an Excel file or table.

SELECT PRODDTA.F0911.* INTO [tblF0911(GDS-4160-09202012-02)]
FROM PRODDTA.F0911
WHERE (((PRODDTA.F0911.GLAID)='00638625') AND ((PRODDTA.F0911.GLSBLT)='W'));

Ideas?
Tom WinslowAsked:
Who is Participating?
 
rshqConnect With a Mentor Commented:
Hi
  Please see this link maybe help

  http://support.microsoft.com/kb/835416
0
 
Kelvin SparksCommented:
Does the select statement (without the into part return the records correctly - and can you scroll to the end of the 3.5M records (y=the only way to know that it has run  correctly to the end)?

That is a lot, but no an unmanageable number of records for Access, and it will depand how wide (how many columns and of what size the columns are) to know for sure. There is a 2Gb database size limit for this version of Access - how many other large tables do you have? I have successfully pout 6.5M records into a table - but it was a mission!

In SQL Server, you'd have to do it in two steps, crewate the table (and fields), then append the data in.


Kelvin
0
 
mbizupCommented:
That error often goes hand in hand with corruption.   Try compact/repairing your database.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Tom WinslowAuthor Commented:
>>> Does the select statement (without the into part return the records correctly - and can you scroll to the end of the 3.5M records (y=the only way to know that it has run  correctly to the end)?

I have not tried to scroll to the end of the file. I will try that on Firday morning.

>>> One approach was to begin with a new, empty, DB (except for the two attached JDE tables) but I got the same error.

>>> In SQL Server, you'd have to do it in two steps, crewate the table (and fields), then append the data in.

That is another approach I will try on Friday morning.

>>> There is a 2Gb database size limit for this version of Access

Uh, I was not aware of this. Have to investigate. That may be the issue. What veraion of Access has a higher limit?
0
 
Tom WinslowAuthor Commented:
>>> That error often goes hand in hand with corruption.   Try compact/repairing your database.

Yes. One step was to ceate a new, fresh, empty, DB but got the same error.

tw
0
 
mbizupConnect With a Mentor Commented:
At what point in creating the new database did the error resurface?
0
 
Michael VasilevskySolutions ArchitectCommented:
>>> Uh, I was not aware of this. Have to investigate. That may be the issue. What veraion of Access has a higher limit?

There is no version of Access without a 2 GB file size limit. At that point it's time to look at another database solution like MySQL or MS SQL Server.
0
 
Tom WinslowAuthor Commented:
>>> At what point in creating the new database did the error resurface?

At the end. Once the status bar has transited completely from left to right.
0
 
jerryb30Commented:
Invalid argument will sometimes show when you do meet the 2GB limit.

SQL Server is better for managing large recordsets. If Access is your front end, you can use a pass-through query, so the SQL Server does the work. Then, you link to the newly created table.
I can't think of why you cannot do a 'select into' statement in SQL Server, although if you do this many times, you will have to be sure to kill the destination table, in case it exists already.
0
 
jerryb30Connect With a Mentor Commented:
(Note to self-Refresh.)
0
 
Tom WinslowAuthor Commented:
Sorry to take up so much time. I still have not been able to resolve the issue. But, I believe that the problem exists solely because of the large number of records that I am dealing with. I have NOT had an issue with other work since experiencing this error when I am dealing with a smaller number of records - like less than a million. Not sure how to proceed from here.
0
 
Tom WinslowAuthor Commented:
How do I close this ticket and award points? I did not get a solution from the experts because I asked a dumb question. My problem was (is) caused by a data table with way too many records at the other end of a T1 line. Too much to try to pull thru the pipe. It choaked.

Someone please tell me the correct way to handle this question.
0
 
Tom WinslowAuthor Commented:
I never got a resolution to this question. I thank all that tried to help.

Actually, today, I got the Invalid Argument error when I tried to modify the query descriotion in the properties window. I cannot change the properties. WTF.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.