scanning to access table

[K]

Ok.. I have a table that i created in access and with the onclick event ran
the code below. It worked perfect. It called the scanner, scanned the
file(s) and when you clicked done it loaded the file upto the table.

I move the table to a sharepoint which is using sql express as the backend.
I then relink the table back to my database. it scans perfectly, but when
you click done to load the document into the table it gives you a invalid
argument error. I have pasted the code below and at the end I have Pasted
the line which causes the error. As I said it works great with an access
table why wouldn't it work with a sharepoint table? I am able to open the
table and double click the attachment field and add files so I am assuming
that I can do it via code. Any help would be great.


Here is the code:

Private Sub Command0_Click()

If IsNull(Me!Name) Then
MsgBox "You must enter a file name. Do not use any special characters in
the file name", vbOKOnly & vbinformational, "Need File Name"
Me!File.SetFocus
Exit Sub
End If



' Display TWAIN Select Source dialog

Call TWAIN_SelectImageSource(0)

Call TWAIN_LogFile(1)
Call TWAIN_SetHideUI(0)
Call TWAIN_SetJpegQuality(16)
' If you can't use Me.hwnd, pass 0:
Call TWAIN_AcquireMultipageFile(Me.hwnd, "c:\image\" & Me!Name &
Me!Combo5)
If TWAIN_LastErrorCode() <> 0 Then
Call TWAIN_ReportLastError("Unable to scan.")
End If

Const m_strFieldFileData As String = "FileData"
Const strTable = "tblfile"

Dim fileName As String
Dim rstchild As DAO.Recordset2
Dim fldattach As DAO.Field2
Dim rstcurrent As DAO.Recordset
Dim strfieldname As String
Dim dbs As DAO.Database
Dim strsql As String


Set dbs = CurrentDb



Set rstcurrent = Me.Recordset
strfieldname = "File"

rstcurrent.OpenRecordset

fileName = "C:\image\" & Me!Name & Me!Combo5

rstcurrent.Edit




If Dir(fileName) = "" Then ' the specified file does not exist!
MsgBox "The scan didn't work. Try Again. If persists, see Me!!!",
vbCritical, "Error Code: ID-10-T"
Exit Sub

Else


Set rstchild = rstcurrent.Fields(strfieldname).Value ' the .Value for a
complex field returns the underlying Recordset.
rstchild.AddNew ' add a new row to the child Recordset
Set fldattach = rstchild.Fields(m_strFieldFileData) ' set the
DAO.Field2 object to the field that holds the binary data.
fldattach.LoadFromFile fileName ' store the file's contents in the
new row
rstchild.Update ' commit the new row.

rstchild.Close ' close the child Recordset.


End If

rstcurrent.Update
rstcurrent.MoveLast
VBA.Kill fileName
End Sub

It crashes on this line: fldattach.LoadFromFile fileName

 

[Steve Sanford]

I think it crashes because the DAO Object library doesn't have a member
"Field2" (Dim fldattach As DAO.Field2).

Nor is there a "Recordset2" (Dim rstchild As DAO.Recordset2).

"file" is a SQL Server reserved word. See

http://allenbrowne.com/AppIssueBadWord.html



You should have as the first two lines of every code page

Option Compare Database
Option Explicit

Then compile the code (Debug menu/Compile) to check for errors.

In looking at the code, I did find a few of other things:

In the first message box, "vbinformational" should be "vbinformation".

"Name" (Me!Name) is a reserved name in Access. Also it is not very
descriptive - name of what?

You don't close the "rstcurrent" recordset. And the two recordsets and the
dbs should be set to "Nothing" before exiting the Sub.


The line

rstcurrent.OpenRecordset

is incorrect syntax and unnecessary; two lines above, the recordset is
populated .


In the line

If Dir(fileName) = "" Then

"filename" will never equal "" because two lines above it is set to

fileName = "C:\image\" & Me!Name & Me!Combo5



Have you set a breakpoint after the calls to the Twain DLLs (at the line:
Set dbs = CurrentDb) and single stepped thru the code?

You can check the variables to see if they have valid data (not Null).


HTH