How To Insert Binary To SQL Express By VBA?

Hi all,

In the end of the post, there is my vba functions and query to create datatable. I got a run-time error message on the red line in the function. It says: [sql native client] string data right truncation. Did I do sometime wrong? Thanks for your time!

-L

Sub test()
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")
Dim mConn As ADODB.Connection
'read the file data to bytes
'Specify stream type - we want To get binary data.
BinaryStream.Type = adTypeBinary
'Open the stream
BinaryStream.Open
'Load the file data from disk To stream object
BinaryStream.LoadFromFile file
Dim mCmd As ADODB.Command
Set mCmd = New ADODB.Command
With mCmd
' define query
.CommandText = "INSERT INTO TEST (FDATA) " & _
"VALUES (?)"
.CommandType = adCmdText
' add parameter
.Parameters.Append .CreateParameter("@P1", adVarBinary, _
adParamInput, BinaryStream.Size, BinaryStream.Read)
End With
' open db connection
mConn.ConnectionString = _
"Driver={SQL Native Client};Server=.SQLEXPRESS;Database=XXXX;Trusted_Connection=yes;"
mCmd.ActiveConnection = mConn
'run-time error: [sql native client] string data right truncation
mCmd.Execute
mConn.Close
End Sub



GO
/****** Object: Table [dbo].[TEST] Script Date: 10/04/2007 11:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FDATA] [varbinary](max) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF