Network or Disk Error: Trying to save huge mdb to dbf

[Ashank4vba]

Hi,

I've searched the forums and the net for this error but havent come across something similar to my situation. I have a huge access database with 3215898 records in a single table(3 text fields in the table).
I've got to save the table in Dbase 3 format. Although I was successful in doing that for a table with 3192816 records (with some difficulty initially) but Im not able to do it for the current db mentioned above. I've tried compacting but I still get that error.

Plzee help.

-regards
Ashank

 

[willir]

Ashank

Take some time and review this link - Microsoft Access 2000 and 2002 Maximum Capacities

Specifically...
- max size of table, 1 Gb
- max database size, 2 Gb
- max recordset limit, 1 Gb

With 3 million records, I would surmise you have exceeded or are near the limits of Access.

Access also uses "workspace" for storing temparary data such as the results of a query or report. The workspace will be on your hard drive. Queries and reports can generate a large amount of data.

Network or Disk Error suggests you may have run out of disk space.

Cleaning up your temp files may give you some reprieve.

Lastly, this type of work will generate a tremendous amount of network traffic. Even if the data and the software is stored on a server, Access will still copy the files to the local desktop.

You will need to reduce the size of the database, and ensure you have sufficient space on the local drive to perform the required work.

Richard

 

[earthandfire]

Another, though probaly much slower solution would be to transfer the records one at a time through code - this should alleviate the problems.

The dBase file structure unlike Access is well documented. A quick search on google came up with:

http://www.hist.uib.no/dokkeveien/diverse/dbaseiii.htm

Hope this helps.

 

[Ashank4vba]

Thanks for your replies Richard and earthandfire.

I apologize for incomplete information. My database is only 131 MB in size (all 3 fields in its single table are text fields). I have 13.6 GB free size in my disk. I am using Windows 2000 sp 4. My processor is Intel P4 2.66 Ghz. Memory capacity is 515 MB.
Sequentially writing to a dBase file is little over my head and Im a little pressed for time.
So Plz folks, any suggestions with this new information??

Thanks a lot !!

-cheers
Ashank

 

[earthandfire]

Do you have a copy of dBase 3? If so create a .dbf file and then post the filename, field names and sizes. I'll try to put something together for you tomorrow.

 

[willir]

slower solution would be to transfer the records one at a time through code

earthandfire Would you have to create a recordset for this?

Ashank
The network or disk error definitely suggests the system is having a problem writing the output. Some reasons for this are...
- insufficient security / rights
- insufficient space
- time out errors because of network problems or bad sectors on a drive

You may also realize that your target database DBase 3 will have restrictions on size. For example, I recall the old days where we would have to remove the ^Z EOF marker from the database and frequently re-index the database with Ashton Tate's DBase 3 and 4.

Perhaps you can rule out infrastructure issues (disk space, rights, network) by exporting to a CSV file format. If you can export to one file format, then you can focus on the issues with the target databse. If you still can not export to a CSV file, then focus on infrastructure issues.

Richard

 

[earthandfire]

willir, yes - but only as:

Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset(TableName)

and then

Do While Not rs.EOF
'Write out the data for one record
rs.MoveNext
Loop

'close rs
'close file
'reopen file and write recordcount to header


As far as record or size limits are concerned. I'm afraid I have no idea. All my dBase III docs were thrown out years
ago, but as the number of records is stored in a 32 bit field, I don't think 3.5 million or so would cause a problem

 

[Ashank4vba]

Hi Richard and EarthandFire,

I finally got Access to create a dbase 3 format database for this database too!!!!!!!

Well, I did some stupid things like leave all 3 text fields in the table at a maximum of 255 characters. They are not more than 20 characters long. So I did that, compacted and repaired the database (not sure if repair helped much except for validation).

Then I tried saving it to dBase 3 format. Access exported quickly and went on busy mode (hourglass pointer) for about 30 minutes and then finally became 'ready'.

Thanks a lot for your input!!! I guess it was a memory issue.

Thanks.

-cheers
Arvind