Ole table and MDB way too big

[mmitchell]

I have a one table database that contains just embedded MS Word files. It has about 1200 records and the files are about 20K each. The MDB (Access 97) is about 900 Meg after compacting. I know that when it reaches 1 Gig Access will complain (I had to break it out of another MDB into its own MDB).

Two questions:

1. Unless I missed some very large files when looking through the records, 1200 x 20K=24,000 KB which, by my math, is only 24 meg. Is there a known reason why this MDB is so large? I know that Access is not real efficient, but I did not think it was that bad.

2. I am moving to MS SQL 2000 and am thinking to save the MS Word files as files and just storing a link back to it, but here is my concern: The reason the files are embedded and not linked is because I can protect the file from being edited if it is embeded, by using Access to allow edits or not. If I link to them, how can I accomplish the same thing? That is, how can I make sure the Word files do not get modified by users?

 

[sfreeman@co.mer]

I assume that you already tried a compact and repair....

In my experience it is almost always better NOT to embed the file, but rather link to it.

To protect files, I would discuss this with my LAN manager, and see if I could set up an ACL on a protected drive.

see link for more info.....

http://www.microsoft.com/resources/...windows/xp/all/reskit/en-us/prdd_sec_tzxs.asp

 

[mmitchell]

Thanks,

Any idea how to get the emebed files out of the database, using code that is, I don't want to go thorugh all of them by hand.

 

[sysHvV]

Word Viewer

I had the same problem where I did not want the files to be edited. For that I used the Word Viewer.
( Wd97vw32.exe installed)
And the Call from Access would be.

Private Sub ShowDocument()
On Error Resume Next
Dim myLink As String

    DoCmd.Hourglass True
    myLink = "C:\Program Files\WordView\Wordview.exe\" & "Something.doc"
    Call Shell(myLink, 1)
    DoCmd.Hourglass False
    
End Sub

 

[mmitchell]

Yes, I have used the viewr before. I just might try that.

Now, if I can just get them out of the database and saved as files!