OLE Object - INSERT Statement Problems
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."
VB Code:
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.
Re: OLE Object - INSERT Statement Problems
If TxtAttachment is a textbox, you're probably getting a type mismatch because the value in the textbox is a string datatype, not ole.
Re: OLE Object - INSERT Statement Problems
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.
Re: OLE Object - INSERT Statement Problems
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.
Re: OLE Object - INSERT Statement Problems
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 -
VB Code:
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 -
VB Code:
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 -
http://img.photobucket.com/albums/v2...y/SQLError.jpg
And that's where I'm stumped...
Re: OLE Object - INSERT Statement Problems
You need ' characters aroud text/char fields, eg:
VB Code:
sql = "INSERT INTO " & MyTableName & " ([Attachment]) VALUES ('" & Me.TxtAttachment.Value & "');"
Re: OLE Object - INSERT Statement Problems
Tried that too. Same deal, I'm afraid.
Re: OLE Object - INSERT Statement Problems
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.
Re: OLE Object - INSERT Statement Problems
Can you post the runtime sql statement?
Re: OLE Object - INSERT Statement Problems
Quote:
Originally Posted by 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.
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.
Quote:
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
Re: OLE Object - INSERT Statement Problems
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]