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-0920201
FROM PRODDTA_F0911
WHERE (((PRODDTA_F0911.GLAID)="0
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-0920201
FROM PRODDTA.F0911
WHERE (((PRODDTA.F0911.GLAID)='0
Ideas?
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-0920201
FROM PRODDTA_F0911
WHERE (((PRODDTA_F0911.GLAID)="0
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-0920201
FROM PRODDTA.F0911
WHERE (((PRODDTA.F0911.GLAID)='0
Ideas?
That error often goes hand in hand with corruption. Try compact/repairing your database.
ASKER
>>> 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?
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?
ASKER
>>> 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
Yes. One step was to ceate a new, fresh, empty, DB but got the same error.
tw
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>> 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.
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.
ASKER
>>> 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.
At the end. Once the status bar has transited completely from left to right.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
Someone please tell me the correct way to handle this question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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