Solved Word to Access Record Set

[RevJeff]

Is there a way to open an Access table as a record set from within Word VBA. Basically I want to takes 6 variable out of Word and import them into an Access table and I will be looping from page to page. I'm just not sure how to open a record set in Access from Word or if it's even possible.

TIA

 

[theDBguy]

Hi. I haven't given it a try, but I would think it's similar to how you would do it from Excel.

 

[RevJeff]

Hi. I haven't given it a try, but I would think it's similar to how you would do it from Excel.

Thank you for responding, but the problem I am facing it that I don't know how to open the Access table from within Word or Excel.

 

[Pat Hartman]

You have to use DAO or ADO to open a recordset.

If the values in the Word document are in "fields", you can do this from Access. Here's a link

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk

In the app is a tool that finds the bookmarks in a document and adds them to a table. The objective is to get all the bookmarks. If you only want some, you would need to modify the code.

 

[RevJeff]

Thank you Pat. I use DAO, but now my problem is that the database that I am connecting to is already open and when I go to Set the DB I can't because it is already open.

Set db = OpenDatabase(strDB, False, True)

How can I Set an already open DB.

Thanks

 

[The_Doc_Man]

If it is already open (by any other session) in Exclusive mode, you can't open it again. It has to be opened in non-Exclusive mode.

If it is already open in non-Exclusive mode, you should be able to open it. If you can't, you should get an error message and code unless you have disabled warnings in your VBA code.

If your WORD session already has the database open, I think there should be a DBEngine, Workspace collection, and Databases collection that you could read. If so, the Database object representing that open database should include a name that is the fully qualified file string for the database. I could be wrong on that because I so rarely use VBA anywhere other than from Access. But I'm also pretty sure that if YOU have the DB open, there should be no barrier to you opening it a second time (again, as long as the FIRST time you opened it wasn't EXCLUSIVE mode.)

 

[RevJeff]

Thanks for everyone's help. I ended up doing a Compact/Repair on the database and it solved the issue. Runs like a champ now.