OLE Object - INSERT Statement Problems

[MethadoneBoy]

Hi guys,

Have tried looking for a solution to this problem on the net, but I've not been able to get very far. I've created a table with an OLE data type and I've also created a from that will allow users to enter information into this table. The issue is that I the INSERT statement I'm using in the VBA code is throwing this error -

"An expression you entered is the wrong data type for one of the arguments."

sql = "INSERT INTO .... blah blah blah .... Me.TxtAttachment.Value & ");"

The sql statement itself is ok - I've specified the correct field names and table name. The problem appears to be with the Value attribute of the TxtAttachment textbox.

Any idea where I'm going wrong on this?

Thanks in advance.

[salvelinus]

If TxtAttachment is a textbox, you're probably getting a type mismatch because the value in the textbox is a string datatype, not ole.

[MethadoneBoy]

It is a textbox, yes. Is there another way to send an OLE object to a table via a form? My version of Access is 97.

[salvelinus]

Sorry, never saved OLE. I tried Google Groups, but didn't find anything directly applicable. You may be able to use a string but it may need various headers, etc, to store it as an OLE object. Not sure why you'd want to do so, but whatever. Look up AppendChunk & related items in Help. But your error almost certainly comes from non matching data types. Post some code & the error message, maybe some one more familiar with OLE objects can help.

[MethadoneBoy]

Thanks, I'll check that out :-)

In the meantime, this is a more detailed view of the problem. The final field in my table is called Attachment and I've set its data type to OLE object. On my form, I have a text box called TxtAttachment. I have its properties set as follows -

Size Mode:      Clip
Auto Activate:      Double-Click
Display Type:       Icon
Update Options:     Automatic
OLE Type Allowed:   Either

Upon clicking a command button, the following code is called -

sql = "INSERT INTO " & MyTableName & " ([Attachment]) VALUES (" & Me.TxtAttachment.Value & ");"
MsgBox sql
DoCmd.SetWarnings (False)
DoCmd.RunSQL (sql)      ' - Synxtax error in query expression here
DoCmd.SetWarnings (True)

In Form View, I right click on the TxtAttachment box and select 'Insert Object' from the drop down menu (it's the only option on this menu that's enabled). I then select a PDF document from a directory. This appears to work fine (that is, a PDF document icon appears in the textbox).

Clicking on the command button then yields the following error -



And that's where I'm stumped...

[si_the_geek]

You need ' characters aroud text/char fields, eg:

sql = "INSERT INTO " & MyTableName & " ([Attachment]) VALUES ('" & Me.TxtAttachment.Value & "');"

[MethadoneBoy]

Tried that too. Same deal, I'm afraid.

[si_the_geek]

Ok, well in that case you'll need to use something other than SQL to insert values for that field (or change it to a text field, if you only want text in it).

I don't know how you can do it in Access, or DAO (which I think is what Access uses behind the scenes), but in in the Database FAQ there is an ADO example.

[RobDog888]

Can you post the runtime sql statement?

[MethadoneBoy]

Ok, well in that case you'll need to use something other than SQL to insert values for that field (or change it to a text field, if you only want text in it).

I don't know how you can do it in Access, or DAO (which I think is what Access uses behind the scenes), but in in the Database FAQ there is an ADO example.

For the moment, I'm going to work around it by including a command button that will open a table-view subform that will allow a user to insert an OLE object into the table directly. Inelegant, but for the moment, it'll do. I'll take a look at that FAQ, thanks.

Can you post the runtime sql statement?

Er... you're really going to expose my pathetic knowledge of VBA here

I'm assuming you don't mean

sql = "INSERT INTO ..."

[RobDog888]

Yes, your "INSERT INTO ... " part by placing a breakpoint on the sql = part so you can get it as its passed to the db with all parameters filled in etc.

I see your error statement shows 2 unsupported chars and was wondering what the statement actually looks like. Maybe there is an error in it.[/color]