2679

Document Sample
2679 Powered By Docstoc
					    microsoft.public.excel.programming: Re: Cut the umbilical cord? Access connection to Excel

 Re: Cut the umbilical cord? Access connection to
                       Excel

Source: http://www.tech−archive.net/Archive/Excel/microsoft.public.excel.programming/2004−05/2679.html


From: Nigel (nigel−9sw_at_suxnospampanet.com)
Date: 05/07/04

Date: Fri, 7 May 2004 18:12:05 +0100



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

"Toby Erkson" <air_cooled_nut@pobox.com> wrote in message
news:utkiVIENEHA.268@TK2MSFTNGP11.phx.gbl...
> 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


Re: Cut the umbilical cord? Access connection to Excel                                               1
    microsoft.public.excel.programming: Re: Cut the umbilical cord? Access connection to Excel
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?
> −−
> Toby Erkson
> Oregon, USA
>
>




Re: Cut the umbilical cord? Access connection to Excel                                           2

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:2/25/2012
language:
pages:2