Compile Error: Method or Data Member not found. Data Append MVF to Access From Excel.

[pedie]

Hi Guys!
'm try to upload file as attachement to Access from Excel.
This perfectly works in Access to Access even Accross databases but from Excel to Access this Problem is happening...
it errors in Underlined Code line below
Please advice.

[/FONT]
[FONT=Courier New]Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset
Dim MVFrst As Recordset[/FONT]
[FONT=Courier New]Dim rs As DAO.Recordset[/FONT]
[FONT=Courier New]
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\Budget_Expense.accdb", False, False, "MS Access;PWD=")
Set rst = db.OpenRecordset("Select * FROM MyTb;", dbOpenDynaset)[/FONT]
[FONT=Courier New]rst.AddNew
rst!Expense_Detail = "Test Title"
Set MVFrst = rst.Fields("MyAttachment").Value
MVFrst.AddNew
[U]MVFrst.Fields("FileData").LoadFromFile "C:\Book1.xls"
[/U]MVFrst.Update
rst.Update[/FONT]
[FONT=Courier New]If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
If Not ws Is Nothing Then
Set ws = Nothing
End If

 

[Norie]

Pedie

I tried the code with only minor changes, eg table/database name, and it worked.

 

[pedie]

Hi Norie...
'm running this code from excel. Try to send attachment to access db table.
Running this access works perfectly.

 

[james_lankford]

maybe change
Dim MVFrst As Recordset
to
Dim MVFrst As DAO.Recordset

 

[Norie]

Pedie

It wasn't clear the code is in Excel.

LoadFromFile is not in the DAO library in Excel even if you search in the ObjectBrowser.

That explains the problem, I'm afraid I can't find a solution.

Why not just store the file name in text field instead of using an attachment field?

 

[pedie]

yes, excel does not support this method LoadfromFile...
yes Norie, maybe that is one option, storing file as file fullname only in text...

Thanks again...

 

[CharlesChuckieCharles]

You need the Microsoft Office 14.0 Access database engine Object Library



then you need to set the field to a DAO.Field2 object

Dim fld As DAO.Field2
Set rst = dbs.OpenRecordset("Sometablesomewhere")
Set fld = rst("FileData")
fld.LoadFromFile "c:\yourfile"

 

[pedie]

Are you referring to Miscrosoft Acesss 12.0 Object Library?
it is already referenced...still same issue.

 

[CharlesChuckieCharles]

no
Microsoft Office 14.0 Access

OFFICE ACCESS 14.0

 

[Kreszch68]

Try to reference the Microsoft DAO 3.6 Object Library

No:

It has to be the MS Office xx Access database engine object lib