MS Access SharePoint Attachment Field

I recently tested a project that is a good candidate for MS Access on Sharepoint. It is an ongoing project keying records daily from scanned images and would require at least 3 keyers daily. The advantages are obvious: ability to work remotely, centralized administration, and flexibility to distribute or redistribute work flow.

I developed forms with needed validation rules for each unique image that we are keying from. Within the table design (and then forms) I included an attachment field. When an attachment field is added to a form, the first image will show. As I will only add one image per record, this works perfectly for our purpose.

For management tools, I designed a separate, client application that links to the Sharepoint lists (tables). I run daily processes and updates from this "management" application. I needed a way to add all new images in a batch process daily. My images arrive as tifs since this is the default and best format on scanning images. However, this format is not "web enabled" and needed to be converted to jpg's to render in the browser. After batch converting the tif's to jpg's, I batch add them to the table with the following two-step vba functions: First, I populate a field in the table (list) with the name of the image (having all new images in a folder):

Public Function PopulateImages()
Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\Folder\NewImages\"
strFile = Dir$(strFolder & "*.jpg")

Do While Len(strFile) > 0
strSQL = "INSERT INTO TicketsTbl (ImageName) " & "VALUES(" & Chr$(34) & strFile & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop
End Function

Then I add the attachments:

Sub AddAttachments()
Dim mfile
Set db = CurrentDb
Dim rsTickets As DAO.Recordset

Set rsTickets = db.OpenRecordset("TicketsTbl")

If Not (rsTickets.EOF And rsTickets.BOF) Then
rsTickets.MoveFirst
Do Until rsTickets.EOF
rsTickets.Edit
Set rsPictures = rsTickets.Fields("Field1").Value
mfile = rsTickets.Fields("ImageName")
mfile = "C:\Folder\1F\" & mfile

rsPictures.AddNew
rsPictures.Fields("FileData").LoadFromFile mfile
rsPictures.Update

rsTickets.Update
rsTickets.MoveNext

Loop
End If

Set rsTickets = Nothing
Set db = Nothing

End Sub

NOTE: I set the process running and checked back later and found that adding 150 jpg attachments took one hour. Not real speedy and an obvious consideration that will make this process unreasonable for some projects.

I have the ability to run counts, do quality control and give feedback to keyers based on the login information that Sharepoint always captures. The built in fields are "SharePointCreatedDate", "Author", "SharePointModifiedDate" and "SharePointEditor." I always add similar fields to my desktop applications but these are included in any SharePoint list.

I found no fully-developed instructions to this process on the web (though I started with a basic outline of the add attachment function from Microsoft) so I'm hopeful that this information will be helpful to some of you. Would love to hear your feedback.