How to Insert file from Excel to Access Using VBA

The most important info is missing:

Could you tell what contains the SelectFile variable? Did you try the INSERT command when the SelectFile contains some simple string?

Set SelectFile = "Test string"
Set rst = ...

Also what do you mean by the following command?

rst.Fields(1).Value = "SelectFile"

This is what I am trying to do

 Dim fd As FileDialog
 Dim SelectFile As String
 Dim cnn As ADODB.Connection
 Set cnn = New ADODB.Connection
 Dim rst As ADODB.Recordset
 Set rst = New ADODB.Recordset
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Please select file to attach"
        If .Show = True Then
            SelectFile = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Set fd = Nothing
       
        cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\SaveFile.accdb;Persist Security Info=False;"
        cnn.Open
        Set rst = cnn.Execute("INSERT INTO Table1(ADDFiles) VALUES(" & SelectFile & ")")
       ' rst.AddNew
        rst.Fields(1).Value = "SelectFile"
       
        rst.Update
        rst.Close
        cnn.Close

Capture.PNG

The line
Set rst = cnn.Execute("INSERT INTO Table1(ADDFiles) VALUES(" & SelectFile & ")")
is different from the one in your question.

So what is the exact line on which it reports the error?

Also this way you are attempting to insert the FILE NAME not the file contents.

And finally the INSERT command does not return RecordSet. You should rather call ExecuteNonQuery

Actually I want to store the path of the file into access database field So what do I need to change in this code to work it?

I don't want to store the file contain I just want to store the name of file into Attachment field of My access Table.

Simply click on Debug button and look what values contain variables.

What is the table name and field name?

Ok when I Debug it takes me to Query line and shows the error that I have attached in previous comment

Table name is "Table1" and the field name is "AddFile"

So once more. Are you able to insert simple text string?

Set SelectFile = "Test string"
Set Result = cnn.ExecuteNonQuery("INSERT INTO Table1(ADDFiles) VALUES('" & SelectFile & "')")

If the field name is AddFile  then you cannot use  AddFiles in the query

It gives me error query cannot contain a multi-valued field

Then what should I need to use instead of AddFiles in query??

If the table field name is AddFile  then you have to use AddFile  in the query.

Capture.PNG

Hmm... this table does not have column named AddFile... Try this

Set SelectFile = "Test string"
Set Result = cnn.Execute("INSERT INTO Table1 VALUES ('" & SelectFile & "')")

No it doesn't work it shows the error "Syntax error (Missing operator) in query expression"

I have added the column name into the table now that is AddFiles

But still it doesn't work any other way I can do it??

Post the error message please

Capture.PNG

Is it the error because I have declare SelectFile as String and when I am selecting file I think it is an Object ???

I would also need the command which caused the error.

Some printscreen from the debugger

Capture.PNG

Great!

Did you test the command proposed earlier?

Set SelectFile = "Test string"
Set Result = cnn.Execute("INSERT INTO Table1 VALUES ('" & SelectFile & "')")

I need the same image with error and the code containing this command

IMG_3569.jpg

Yes I test the command you asked me to but it didn't work even

Have you tried simply linking the Excel worksheet into Access, then running an append query from the linked table into your destination?

The image you added in this post makes it look like the Excel worksheet has attachments (the paperclip icon).  I don't use Excel much, so I'm was not aware you could attach a file to Excel, is that the case here?

Dale

OK, I believe you.  Show me the result please.

Thanks for the jump in Dale but I don't see any Excel sheet in the pointed post.

Actually I want to save the path of file into access database using excel I am able to open file using filedialog box command but the thing is I am having problem to send that file path into database table field as attachment

I am still waiting for the result of the code which contains following commands:

Set SelectFile = "Test string"
Set Result = cnn.Execute("INSERT INTO Table1 VALUES ('" & SelectFile & "')")

Maybe you'll need to start the 2nd line by

Set rst =  ...

instead of

Set Result = ...

And please remember the two lines expect following declarations which are in your code already

 Dim SelectFile As String
 Dim rst As ADODB.Recordset

BTW, don't forget apostrophes in the INSERT command.

I did it as you said but still having same error
Can you please view the code once again and try whats wrong I am doing

OK, post the print screen from debugger and I'll review it.

Capture.PNG

OK, this is not my command...

But the most important fact is:  Apostrophes are still missing.

So once more. Try this in your code:

Set SelectFile = "Test string"
Set rst = cnn.Execute("INSERT INTO Table1 VALUES ('" & SelectFile & "')")

Select allOpen in new window

Capture.PNG

For some reason I looked at the connection string in that first image and thought this was about Excel.  But for the life of me, I cannot figure out why you are using ADO to work with Access tables, instead of DAO.

Generally, the syntax for inserting into a table is:

strSQL = "INSERT INTO [tablename] (field1, field2, field3, ... fieldN) " _
            & "Values (1, 'abc', 2, ..., 'N')"

If you are only attempting to insert the name of the file selected from the filedialog box, then it might be:

strSQL = "INSERT into Table1 (Filename) Values ('" & SelectFile & "')"

Dale

Thank you Dale
If you see the code that I have uploaded I have used the same query to just insert the name of the file but still it showing me error

One of the snapshots shows that syntax, but leaves out the ' (single quotes) in the Values section.  The other doesn't list the field to insert into but does contain the single quotes surrounding the filename.

Dale

Capture.PNG

When i add single quote this is what i get

So we have Syntax error now... Are you able to tell what line requires the object?

You could try it without variable:

Set rst = cnn.Execute("INSERT INTO [Table1] VALUES ('Test string')")

Select allOpen in new window

1.PNG

OK, not sure why you are using SET rst = cnn.Execute(...)

if you want to open a recordset, and use the AddNew method, then use the DAO Openrecordset method.  If you simply want to execute the statement, try:

cnn.Execute "INSERT INTO table1 (AddFields) Values ('Test string')"

Select allOpen in new window

You still have not answered why you are using ADO instead of DAO?  Where you should be able to simply use:

currentdb.Execute "INSERT INTO table1 (AddFields) Values ('Test string')"

Select allOpen in new window

OR

set db = currentdb
set rst = db.OpenRecordset("SELECT AddFields FROM Table1 WHERE 0 = 1")
rst.AddNew
rst!AddFields = "Test String"
rst.Update
rst.Close
set rst = nothing

Select allOpen in new window

Dale

Thank you dale
As I am new so I didn't new DAO is better or ADO
IF you can modify this code to DAO and send it to me that it will be a great help
I don't have much knowledge about all this
and the line as u said

[cnn.Execute "INSERT INTO table1 (AddFields) Values ('Test string')"]
I have already tried this but it doesn't work

SelectFile = "Test String"

cnn.Execute ""INSERT INTO table1 (AddFields) Values ('" & Select File & "')"

Hello Dale please have a look to attachment
.PNG

depending on the version of Access you are using, you may have to add a reference to the microsoft DAO 3.6 Object Library. Try it without it first.  You will know if you need it as soon as you type the "DAO."

Remark all of this out (everything after the "Set fd = nothing" line

        cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\SaveFile.accdb;Persist Security Info=False;"
        cnn.Open
        Set rst = cnn.Execute("INSERT INTO Table1(ADDFiles) VALUES(" & SelectFile & ")")
       ' rst.AddNew
        rst.Fields(1).Value = "SelectFile"
       
        rst.Update
        rst.Close
        cnn.Close 

Select allOpen in new window

And replace with:

dim db as DAO.Database
set db = currentdb
db.Execute "INSERT INTO table1 (AddFields) Values ('" & SelectFile & "')", dbfailonerror

Select allOpen in new window

Now I'm confused.

Sorry for ussing the wrong field name "AddFields", instead of "AddFiles"

Is the AddFiles field in your table multi-valued?  If so, it has a fixed set of acceptable values and you will not be able to add a file name to that field.

BTW, the where clause I entered in my example above had nothing to do with the AddFiles field, it simply said WHERE 0 = 1.  The reason for this is to create an empty recordset.

Thank you dale
As you said I tried all but still having errors

Is there any other code I can use and save the file path in access table in attachment field.??

Your Table1 design is wrong.

Create different table and try the code with it.

CREATE TABLE TableX (AddFields varchar(255))

can you copy this table and your code for this operation to a sample database, then delete all of the records from the table and post the database here on EE?

The table is empty just has 1 field that is Attachment

Note:  The above command contains incorrect column name

Hey pcelba
The table must have attachment field that's the only field I need ant then to insert the path of file in that table

So the question is, do you want to save the name of the file in a field, or do you want to actually save the file you selected as an attachment.  I would personally advise against using the attachment field type as it is almost as much of a PITA to deal with as are multi-valued fields.  They also rapidly bloat the size of the Access database, with is extremely limited in size.

I generally store the full name of the file (including the path) in a text field.

ok I have created this now please have a look to the file
.PNG

Dale
I want to store the file as attachment no matter if the size is limited I just want to store couple of files.

To insert attachment is more complex. You may look here for some ideas: https://social.msdn.microsoft.com/Forums/office/en-US/eee6ec2a-cc4b-4c56-8453-2a0ceedf10a3/how-to-insert-attachment-datatype-to-microsoft-access-2007-in-c?forum=accessdev

or here: https://social.msdn.microsoft.com/Forums/vstudio/en-US/d83c6613-dfc9-4862-be22-82365e47a883/ms-access-20072010-attachment-field-retrieval-and-storage?forum=vbgeneral

Hey pcelba
I just checked but it doesn’t have any solution the link u share

Of course, to get solution in a box you have to create Gigs project.

Or you may look here at EE: https://www.experts-exchange.com/questions/28315498/Access-insert-into-Attachment-field-file-from-Path.html

I tried creating new db but doesn’t work
Thank you guys that you are helping me

do you have to do this via VBA?

If your users don't need this capability, you could simply do it by right clicking in the the attachment field when the table is open.  Then select Manage attachments.

Dale

I really want to complete this your help is most appreciated

Dale
I want it to be done through excel vba
Thank you

OK,

So now I understand where my Excel reference came from.

You want to run this code from Excel, to select a file and upload it into an Access database table, in an attachment field?

Well, I'm out for the rest of the day.  Have a meeting with a client and then have 30 hours work to complete before Monday morning.

Hope you get this worked out.  The link the pcelba gave you should help.  Otherwise, do a search on "Access attachment fields".

Dale

Thank you Dale

Hey Pcelba thank ypu for your help
If you can find something let me know

Dale you got it right now you know what exactly I want to do.
Thank you

We've found a few useful links which you may try to accommodate to your needs. They show how to handle Attachment fields which are more complex than simple data types in Access. You'll need some additional investigations and several more attempts to make it working. SQL INSERT command means "no go" for attachment fields.

I m also leaving now as we have Friday evening already...

Hello Dale and pcelba I have found other way can you guys please help me I just have a single error in new code.
PFA for the error message
2.PNG

3.PNG

1.PNG

I am getting the above error message now any help??

rst.Fields("AddFiles").Value = "SelectFile"

but this expects the AddFiles is a text field.

If the AddFiles is still an attachment then you cannot assign a text to it.

Hello pcelba
So what should i need to do can you please suggest i am still stuck did you find anything ??

I've posted links which contain code suitable for Access attachments handling. So try to accommodate the code from these links to your needs. You should test the code as is and try to understand it then start with its changes and porting.

Another way is to avoid attachment fields usage.  You may create a subfolder in the folder where the Access database is located and place all files into this subfolder. Access table will then contain just one single text field which will contain the file name. This seems to be better approach than including large files in the .accdb file. And remember if your database does not contain large files now it can happen in the future...

Hello everyone My code is working but only for Normal field. If I select the field Data Type to Attachment It shows me error. Do anyone has idea what to do with Attachment Data Type. Thank you all for your support

Bushan,

I'm still cramming on the project I started Friday afternoon.  May get a chance to put something together for you tomorrow.

Dale

Any one got the solution guys??

Hi Bhushan,

it seems nobody has spare time to provide complete solution. The reason is the attachment field in Access which is more complex than standard single valued fields.

OTOH, ways how to process Access attachments were provided in the links. So my question is: Did you accommodate some publicly known solution to your working code?

TIA
Pavel