Not Enough Disk Space or Memory During Importing DataHello, Need guidance, can someone help ? I have

[katjoe]

Hello,
Need guidance, can someone help ?


I have a VBA script that imports Excel spreadsheets into 3 Access tables. All works well until I got to the 43 file.
I get the error message “Not enough disk space or memory to be able to perform the undue command, If yes then you can lose the data”

Each table has anywhere from 10,000 – 14,000 records.
Set the “Compact & Repair” to run after closing the database.
Checked the database size which is 81,628 kb.
I also viewed the Task Manager before starting Access and after starting Access. It went from 4% to 9%. Looked like it has plenty of space.
With this error I noticed it will insert the SQL queries that I put in the script such as today’s date, file name, file location but the DoCmd TransferSpreadsheet is not importing the data from Excel.

Can someone please help me understand what is going on and how to fix this error. If the size is not the problem then I am stumped.

Thanks in advance for any guidance!

[Minty]

Can you show the code that does the importing, you may be doing something that isn't closing an object or isn't very efficient and/or causing a memory usage problem.

[katjoe]

Hi I am not able to share the code due to the database in on a secured network. Perhaps I can modify it so I will get back to you with this.
On that note could you help with a query though?
What I am trying to do is:

1. Get the distinct values of the field 1.
2. Find the percentage of the Distinct Values by dividing the distinct value by the total value of field 1.
3. Counting all values in the three fields of the table then divide it by the distinct values to get the overall percentage.

Desired results:

Field 1	Distinct_Field	Percentage_Field	Total_Percentage_Field
X	3	1%	.01%
Y	1	.01%	.001%
Z	2	.02%	.02%

Modified version of Query one that will count the values in three columns.

<CODE>
SELECT Count([Field Table].[Field 1]) as Total_Field, Count([Field Table].[Location]) AS Total_Location, Count([Field Table].[Cars]) AS Total_Cars FROM Dealer;
</code>
Second query that will pull the results from query 1:
<code>
SELECT [Field Table].[Field 1], Count([Field Table].[Field 1]) As Distinct_Field, ([Distinct_Field] / Total_Field]) As Percentage_Field, ([Distinct_Field] / ( [Total_Field], [Total_Location], [Total_Cars])) AS Total_Percentage_Field
From Query
Where [Field Table].[Field 1] Is Not Null
GROUP BY [[Field Table].[Field 1];

Access does not like this. Not sure how to combined the distinct and apply the aggregate functions to get the above results.
Any Help is appreciated!!

[mike60smart]

Hi
Don't know how you can get a "total value of field 1", when Field1 is a Text Field?

[Welshgasman]

Hi
Don't know how you can get a "total value of field 1", when Field1 is a Text Field?

The total is actually a Count() Mike?

[mike60smart]

Whoops, my mistake

[June7]

What has db being on a secured network got to do with sharing code?

[Welshgasman]

Whoops, my mistake

Bit like a Banana being called an Apple.