VB/SQL/Access2007

Henry's Cat · 10-09-2008 10:18AM #1

Hi All,
New to Access2007 and have a quick question regarding the Attachment datatype.

I have a VB6 programme to connect to a database, and to do the usual, SELECT, INSERT INTO, UPDATE etc., I create a Connection object and
execute SQL against it.

Can anyone tell me if and how I can write an SQL statement to Insert a file to an Attachment field in the database table? I've looked on the web but cannot find anything about programmatically inserting from VB to specify a file/attachment?
As in sSQL = "INSERT INTO myTable VALUES('name', 'address' ... picturefile)

There were some resources on doing it from VBA and DoCmd. but I want to keep the application and database seperate.

Thanks in advance.

Ginger · 11-09-2008 03:46PM

http://blogs.msdn.com/access/archive/2007/08/24/adding-removing-and-saving-files-from-the-new-attachment-field-in-access-2007.aspx

You can use DAO to do it..

Henry's Cat · 11-09-2008 04:49PM

Ginger wrote: »

http://blogs.msdn.com/access/archive/2007/08/24/adding-removing-and-saving-files-from-the-new-attachment-field-in-access-2007.aspx

You can use DAO to do it..

I think thats using DAO with VBA within the actual Access2007 database
I'm connecting via a seperate VB6 application.

Is there a different reference I need to access Recordset2 objects from withing VB?

Ginger · 11-09-2008 05:20PM

You need to use C:\Program Files\Microsoft Office\Office12\acedao.dll

Have a look at http://allenbrowne.com/ser-38.html

Ginger · 11-09-2008 05:52PM

Some more info

1) Run AccessDatabaseEngine.exe on the target machine
2) In VB6 Project|References,
a) Deselect Microsoft DAO 3.6 object library
b) Select Microsoft Office 12 access database engine object library
c) Select Microsoft Office 12 object library
3) no special code changes needed when setting db objects

Download AccessDatabaseEngine.exe from here

Henry's Cat · 12-09-2008 09:48AM

Thanks Ginger,
Problem now is I get "Invalid field data type." error when trying to update with a jpg

Confusing now as I can add the same jpg file to the table when in the Datasheet view using the Attachment dialog?

Any ideas?

Ginger · 12-09-2008 10:55AM

No idea.. i dont do Access development at all..

VB/SQL/Access2007

Comments