Cut the umbilical cord? Access connection to Excel

T

Toby Erkson

I have a sheet where I have data from an Access table imported (Data -->
Import External Data --> Import Data...). No problem there. What I've
discovered is that a connection, it seems, stays open between Excel and Access
so that, in effect, that particular Access data source is "open" and remains
so until Excel is closed or the Excel file is Saved As something else -- but
if Refresh Data is performed then that link is established again and the
problem is back.

Now this is bad because the Access mdb cannot be opened for
changes/edits/modifications because it acts like it is opened by another user
(and technically it is). So I'm thinking about having VBA severe the
connection after the data has been downloaded because that's what seems to
happen when the file is saved elsewhere or if it's opened without a Refresh.
Can this be done and if so, how?
 
J

Jake Marx

Hi Toby,

This isn't the way it's supposed to work (at least I have never run into
this before). Do you have your MDB set up as a single-user database (set to
open exclusively)? What exact steps did you take to set up the database
query?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
N

Nigel

Use ADO to connect to your database using the open method to establish the
connection

Something like.......

Sub ADOMethod()
' make a reference to Microsoft ActiveX Data Obects Library

Dim dCon As New Connection
Dim dRecs As New Recordset
Dim dSQL As String

dCon.Open "Provider=microsoft.jet.oledb.4.0;" + _
"Data Source=your path and mdb file.mdb"

dSQL = "your database query string"

dRecs.Open Sql, dCon

While Not dRecs.EOF

'Load your data records into the worksheet

Wend

dRecs.Close: dCon.Close
End Sub

Cheers
Nigel
 
T

Toby Erkson

Nigel, thanks but too much work right now so Jake...

Good questions. It's been YEARS since I've used Access with any proficiency
so bear with me. I'm currently using MS Access 2002, SP-2, on XP.
Yes, single user...how would I change this?
The only modification I made to the query was change the network drive path
into a UNC path.

Toby
 
J

Jake Marx

Hi Toby,

Toby said:
Good questions. It's been YEARS since I've used Access with any
proficiency so bear with me. I'm currently using MS Access 2002,
SP-2, on XP.
Yes, single user...how would I change this?
Click to expand...

Tools | Options, Advanced tab...look under Default open mode.
The only modification I made to the query was change the network
drive path into a UNC path.
Click to expand...

So you created the query using the Query Wizard, right? What did you select
as the data source? The ODBC driver for Access, or do you have a DSN set up
already?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
T

Toby Erkson

Default open mode was shared. Well, still is shared.
ODBC driver for Access.

Now, I had the db open. When I opened the workbook a very odd window popped
up:
Please Enter MS JET OLE DB Initialization Information
and it showed the data source, user name, etc., and the last item was Open
Mode: with a drop-down box. In it was selected DB_MODE_READWRITE.

Ah-soo.

So I selected DB_MODE_SHARE_DENY_NONE. Clicked OK and the data downloaded and
everything worked like it was supposed to. Here is the new output of the
connection from the "Edit OLE DB Query" from the Edit Query... menu item:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=\\pdxberkompas02\Reporting$\public_reporting\Siebel_Export.mdb;Mode=Sha
re Deny None;Extended Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine
Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair=False;Jet OLEDB:SFP=False

The key is "Mode=Share Deny None" in the second sentance above. Previously it
was ReadWrite (or close to that) and that, I surmise, was my problem. Now
everything is fine.

Thanks Jake for helping me along :) Now I know how to configure my other
queries for this process.

Toby

Jake Marx said:
Hi Toby,

Toby said:
Good questions. It's been YEARS since I've used Access with any
proficiency so bear with me. I'm currently using MS Access 2002,
SP-2, on XP.
Yes, single user...how would I change this?
Click to expand...

Tools | Options, Advanced tab...look under Default open mode.
The only modification I made to the query was change the network
drive path into a UNC path.
Click to expand...

So you created the query using the Query Wizard, right? What did you select
as the data source? The ODBC driver for Access, or do you have a DSN set up
already?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
Click to expand...
 
Top