2-way link: Access to Excel and back

[Ritchie]

I have a database which works in tandem with a complex spreadsheet.
The operator opens the database, and uses a search screen to select a
record on which to operate. The database at this point opens an Excel
spreadsheet template and loads some data, ready for the operator to go
to work.

Here's the part I'm stuck on: When the operator is finished working,
it should be possible for them to click a button on the spreadsheet
and have the values saved back into the database, which is still open.
However, when I try

Set db = OpenDatabase("C:\temp\MyDB.mdb")

from Excel, I receive the error message

"The database has been placed in a state by user 'UserName' on
machine 'MachineName' that prevents it from being opened or
locked."

(Run-time error 3734).

The database in question is opened in Shared mode with no record
locks. Can anyone be of assistance in telling me what's going wrong?

Regards

Ritchie

 

[Andy Wiggins]

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "The Excel Auditor" and "Byg Tools for VBA"