Need help with VBA - Insert records with OLE Object (Word Doc) along with details

[sjslall]

Scenario:
Have a separate Access GUI front-end (local) and separate mdb (on network share)
Have a table with the following fields on the MDB:

Doc_ID>>ID
Doc_Details >> TEXT
DOC >>> OLE OBJECT

This is what will be done:

1. Browse for Doc file and load into unbound/bound OLE control (This has been done and working)
2. Need to insert the Doc Details and Doc files into the Table (I would like to embed and not link)

What I know but can't figure out

Incase a Bound OLE control is used, I need to set ControlSource to the Table's Doc Field (which is not on the same database) - How would this be done

When I initiate the acOLECreateEmbed action, it will create the record or not?

How do I also insert the Doc Discrition details simultaneously.

Trying and trying...SOS Pls assist

 

[HiTechCoach]

Scenario:
Have a separate Access GUI front-end (local) and separate mdb (on network share)
Have a table with the following fields on the MDB:

Doc_ID>>ID
Doc_Details >> TEXT
DOC >>> OLE OBJECT

This is what will be done:

1. Browse for Doc file and load into unbound/bound OLE control (This has been done and working)
2. Need to insert the Doc Details and Doc files into the Table (I would like to embed and not link)

What I know but can't figure out

Incase a Bound OLE control is used, I need to set ControlSource to the Table's Doc Field (which is not on the same database) - How would this be done

When I initiate the acOLECreateEmbed action, it will create the record or not?

How do I also insert the Doc Discrition details simultaneously.

Trying and trying...SOS Pls assist

are you using the new ne ACE database (.accdb). If not you will have so much database bloat that you will not be able to store very many documents. Since you are limited to just a 2 gig database file since that is not very many document at 10 to 100 meg or more each.

 

[sjslall]

I currently am using MDB on Access 2007. I can shift to the ACE (accdb) database format.

This is something I think might work:

Create a recordset (dynaset)
Insert a Record and add update the fields other then the OLE field
Next at ruintime assign the controlsource property of the OLE Object control with RS![OLE Field] and invoke the adcreateembed action and then do a complete update on the recordset.
I still haven't tested this...but would this work?

 

[HiTechCoach]

I currently am using MDB on Access 2007. I can shift to the ACE (accdb) database format.

This is something I think might work:

Create a recordset (dynaset)
Insert a Record and add update the fields other then the OLE field
Next at ruintime assign the controlsource property of the OLE Object control with RS![OLE Field] and invoke the adcreateembed action and then do a complete update on the recordset.
I still haven't tested this...but would this work?

I do not know. I would never do what you are attempting.

I would highly recommend that you read these: The Access Image FAQ

I have learned a long time about form working with very large scale ( millions of documents) document management systems using SQL server that you do NOT want to embedded the document in a record in the database.

I would highly recommend that you read these: The Access Image FAQ




If you must embedded the files then I would urge you to use this: DBPix (Click here)






Did I mention:
I would highly recommend that you read these: The Access Image FAQ

 

[sjslall]

Thanks on the update.
I will try this code I want to and will update.
I am doing this project as we are creating a knowledgebase of our process.
Currently we have the word files on our sharepoint server but would like to have a smaller more user friendly version with us. will keep u posted.

 

[HiTechCoach]

Ah ...

I would recommend that you consider converting your database to a ACE database format (.accdb). Then you can use the new attachment data type.

See:
Microsoft® Access 2007 Working with the Attachment DataType

 

[sjslall]

Thanks on your advise. I have decided to go with it.

 

[HiTechCoach]

Thanks on your advise. I have decided to go with it.

Would that be going with the ACE (2007) format database and the new attachment data type?

 

[sjslall]

yes I have done the changes to the database (accdb).
Created an Attachment field.
Now would try myself and research on how to get this up and running and would call u out incase I hit a wall, but would like to 1st try myself.
Your recommendation and assistance is greatly appreciated.

 

[HiTechCoach]

yes I have done the changes to the database (accdb).
Created an Attachment field.
Now would try myself and research on how to get this up and running and would call u out incase I hit a wall, but would like to 1st try myself.
Your recommendation and assistance is greatly appreciated.

You're welcome.

Be sure to look at the link for the Tutorial on using the Attachment data type in post #6

 

[sjslall]

Good News:
Was able to add the Docs into the database:

However need help with clearing the contents of a ComboBox which when the form loads get populated with KB_SEC

I would like to know how we can requery or clear the combobox and repopulate with new data.

The rowsourse is not assigned, I do a normal additem of rs![kb_sec] to the combobox.

Code to add the attachment (which has worked):




Dim DB As DAO.Database
Dim RS_Parent As DAO.Recordset2
Dim RS_Child As DAO.Recordset2

Set DB = OpenDatabase(KB_Data)
Set RS_Parent = DB.OpenRecordset("select * from kb_table")
With RS_Parent
.AddNew
RS_Parent![kb_sec] = UCase(Combo7)
RS_Parent![kb_desc] = Text11
Set RS_Child = RS_Parent.Fields("kb_doc").Value
With RS_Child
.AddNew
.Fields("FileData").LoadFromFile (Doc_path)
.Update
End With
.Update
End With
Set RS_Parent = Nothing
Set RS_Child = Nothing
DB.Close
MsgBox "KB Article has been uploaded to the database"

 

[HiTechCoach]

Good News:

However need help with clearing the contents of a ComboBox which when the form loads get populated with KB_SEC

I would like to know how we can requery or clear the combobox and repopulate with new data.

The rowsourse is not assigned, I do a normal additem of rs![kb_sec] to the combobox.

AFAIK, requrey only works is the row source is a tbale or query.


How are you lodaing he conmbo box tghe firs time?

You will ned to rerun the code e very time your what to0 relaod the combo box.


If irt were me I woudl load teh choices into a table. See the rowsource to the table. To remove an item just delete it from the table and requery the combo box.

I rarely do a "normal .additem". That is not the normal way I use a combo box.