[AccessD] Excel and SQL Server

To clarify. The situation is that the app which uses/updates the data is
being retired but they want to continue to view the data via Excel. So the
data won't change and there's therefore no need to refresh it. What I need
to do is connect to it by the optimum method and then probably provide
queries, pivots on it.

Andy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: 03 May 2012 18:34
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Excel and SQL Server


A big decision is: embedded in workbook or on-the-fly connection via VBA ?
Another is: when do I update the workbook from the query ? When it opens ?
Via command button ? Every 60 minutes ?
The biggest problem is managing the connection string which is akin to
managing a Word document...it's just free form with a bit of structure and
lots of syntax.
If this changes frequently, you may want to implement the string as a Class
with methods for changing userids, passwords, etc. Otherwise, you'll find
yourself making tons of use of Replace and it's a lot of fun because you'll
have """" (quotes within quotes) to deal with....and watch for those
semi-colons !

An interesting bug in Office 2010: if you use the default connection as
established by Excel, you lock-out all users from editing an Access
database. Microsoft "forgot" to expose the MaintainConnection property in
the Ribbon so it defaults to "True" and can only be changed via VBA. Also,
they set the Mode to "Share Deny Write" which is not the same as "Read".
Don't ask me why.

I see you're a glutton for punishment, eh ?



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com