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 ?