[AccessD] Excel and SQL Server

Mark Simms marksimms at verizon.net
Thu May 3 12:34:25 CDT 2012


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 ?





More information about the AccessD mailing list