• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

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?
0
Tom Winslow
Asked:
Tom Winslow
  • 6
  • 2
  • 2
  • +3
3 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
mbizupCommented:
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
 
jerryb30Commented:
(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
 
rshqCommented:
Hi
  Please see this link maybe help

  http://support.microsoft.com/kb/835416
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 6
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now