Error 3001: Invalid Argument

[DajOne]

The error occurs when I try to make a table from a query
The source is a simple table and the output is also simple however there is 53,0000 output lines with over 15 fields


IF teh output of the same query is 'Select query' there is no problem, however when the output is make table, then the problem occurs..

I looked in the forum and sould not find a solution to this..

The same query has been working for over 3 years without a glitch..

Anyone can help me?

 

[JerryKlmns]

Given that we are telepathically supposed to know in what circumstances this has happened, I will not reply with a telepathic answer but rather have to ask you your sql statement when it is a make table query ...

So please do post that, to start with

 

[DajOne]

JerrkKlmms,

I appreciate your good humor in this early monday morning..

I pasted the SQL query statement and I hope this is what you asked for. If not, I apologize for my lack of knowledge.

I use the menu (query > make table query) to make the table.

SELECT EB_AC5.Region, EB_AC5.AE, EB_AC5.Business_Unit AS [Business Unit], EB_AC5.Orig_Credit_Officer_Name AS [Credit Officer], EB_AC5.[Credit Manager Name], EB_AC5.RAC_Flag AS [RAC Flag], IIf([RAC_Flag]="Y" And [Decision]="1- Approved",1,0) AS [In RAC], EB_AC5.EB_App_No AS [App No], IIf([EB_App_No]>"",1,1) AS [App Count], EB_AC5.Decision, EB_AC5.MinOfDate_received, EB_AC5.[MaxOfCredit Date] AS [Credit Decision Date], EB_AC5.MaxOfExpiry_Date1, EB_AC5.Transaction_Status AS [Transaction Status], IIf([EB_AC5]![Transaction_Status]="Booked",1,0) AS [Booked Count], EB_AC5.IL_Booked_CAD AS [Booked Amount CAD], EB_AC5.[AvgOfApplication_Amount CAD] AS [Average App Amount], EB_AC5.AvgOfResulting_Exposure_CAD, Work_Days([MinOfDate_received],[MaxOfCredit Date]) AS TAT, IIf([AvgOfResulting_Exposure_CAD]<500000,"Less than $500M",IIf([AvgOfResulting_Exposure_CAD]>999999,"$1MM +",IIf([AvgOfResulting_Exposure_CAD]>=500000 And [AvgOfResulting_Exposure_CAD]<=999999,"Between $500M and $1MM","Not Classified"))) AS Range, EB_AC5.[Input Date], EB_AC5.[Fleet Flag] INTO EB_AC6
FROM EB_AC5
WHERE (((EB_AC5.[Input Date]) Between ([Startdate MM/DD/YYYY] & " " & #12/30/1899#) And (([EndDate MM/DD/YYYY] & " " & #12/30/1899 23:59:0#))));

 

[DajOne]

Well, the problem seem to be spreading.. ALl my queries now have the same problem.. SO I guess it is an access issue where my database is not well maintained (by me) and needs maintenance.

 

[DajOne]

I think I found the problem. There is a limit in Access in the size fo teh database, which is 2 GB, which I exceed slightly..

Might be the problem. I hope this may help someone else..

 

[lespaul]

Do you compact and repair your database on a regular basis?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[DajOne]

Nop... and I think that is (read the user) the problem...

I will read up on maintenance and hopefully correct this issue...

Thanks

 

[JerryKlmns]

DajOne

Here is a piece of code to compact and repair an MS-Access using user-level security database from outside the database. You could build an extra database to handle this job for several databases. I dont know how much familiar you are with VBA, but since you sensed my Monday morning humour, this is for you.

Sub C_R_E_Dbase(App_Dbase As String, Optional Sys_Dbase As String, Optional PowerUser As String="Admin", Optional PowerPassword As String ="" )

Dim je As JRO.JetEngine
    
    Set C_R_E_Cnn = New ADODB.Connection
    With C_R_E_Cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = App_Dbase
         If Sys_Dbase <> "" Then
           .Properties("Jet OLEDB:System database") = Sys_Dbase
         Else 
           .Properties("Jet OLEDB:System database") = SysCmd(acSysCmdGetWorkgroupFile)
         End If
           .Properties("User ID") = PowerUser
           .Properties("Password") = PowerPassword
        .Properties("Mode") = adModeShareExclusive
        .Properties("Jet OLEDB:Engine Type") = 5
        .Properties("Locale Identifier") = 1033
    End With

    Set je = New JRO.JetEngine
    je.CompactDatabase C_R_E_Cnn, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Data_Folder & "Compacted.mdb;" '& "Jet OLEDB:Encrypt Database=True"  If you need encryption
    Set je = Nothing
    Set C_R_E_Cnn = Nothing
    Kill Data_Folder & App_Dbase
    Name Data_Folder & "Compacted.mdb" As Data_Folder & App_Dbase
End Sub

App_Dbase = The full path to database
Following parameters are optional and if omitted, use default values.
Sys_Dbase = The full path to the .mdw file, default = currently in use
PowerUser = A UserID with administrative rights, default = Admin
PowerPassword = His password, default = ""

You need to copy this into a module. Also add these references (Tools-->References)
Microsoft AxtiveX Data Objects 2.x Library
Microsoft Jet and Replication Objects 2.x Library

You could build a form to hold the info needed in text boxes and a button, on the click event of which place this

Call C_R_E_Dbase(txtMyDBase, txtMySecDB, txtUser, txtPSW )

Have a nice Tuesday morning!

 

[DajOne]

I am greatfull for your kindness..

I will look at this code and hopefully learn from it..

I now know that maintenance is paramount