John W Colby
jwcolby at gmail.com
Mon May 13 16:33:12 CDT 2013
I developed the entire thing in SQL Server. Got it entirely working. Then I was informed that the server for production will not be ready for ... an unknown period and they need my changes NOW. The original was really badly written by a "power user" and only ever 1/2 worked and it is so slow and does so little that they need it to fully work. Since I can't use SQL Server for the data store I am writing a schizo app which does a compiler directive to cause the app to decide dynamically whether to run SQL Server (PTQ) or Access native. Of course all of the SQL Server stuff just works. Did you know... That Word automation will work seamlessly with PTQs but fails with any Access native queries which use functions in them (Date() in a where clause etc) because ODBC (or Word) does not understand the non-standard SQL. Apparently Access passes back the SQL itself to Word and since the query now contains embedded function calls, that SQL fails because of the ODBC layer. Sigh. So I take the Access query and use make table to make a temp table... on the theory that select * from MyTempTbl will work. Nope, word "can't find the data" or some such crap. Still haven't figured that out. Ohhhh soooo sucky... Did you know that the SQL statement handed back to Word MUST be something like "SELECT * from [MyTblName]". Word expects the square brackets and if you don't include them you end up with "can't find the table yTempTabl... yep, Word is trying to strip the square brackets off. But of course all of the examples I find on the internet (including Microsoft's own) use a simple "SELECT * FROM MyTblName" (without the square brackets). Ohhhhh sooooo sucky.... John W. Colby Reality is what refuses to go away when you do not believe in it On 5/13/2013 4:42 PM, Arthur Fuller wrote: > Short answer is No. Slightly longer answer is "Why would you want to > switch?" Drop-dead easy to do PTQs and if thBE is anything, MySQL, > PostGres, MS SQL, this is a no-brainer. So where is your problem? And why > would you want to support both local and PTQs? That makes absolutely no > sense to me. Commit or abdicate, and from then on the decisions are simple. > I'm very empatically in favour of PTQs, but I also realize that with > certain apps (badly designed, IMO) there is a need for local tables. So be > it. > > Yes, there is some magic that can swtich, but that is so wrong that I > humbly request that you rethink the problem. There is IME no reason ever to > do this. I can show you the magic, which I wrote about 19 years ago, and it > sucks, and I have abandoned it about 17 years ago, and if you want to > implement it, you're an idiot. That is most emphatically NOT the way to go. > > And in case any reader is interested, JC and I have met and are friends, > and so I can feel free to declare some of his thoughts idiocies without > causing offence or violence. To turn it around, JC has taught me an immense > amount about various subjects, and I visit his blog daily. > > But now and then, we all get it wrong, and IMO this is one such instance of > the MyApp.Stoopids.() collection. > > A. > > > On Mon, May 13, 2013 at 3:32 PM, John W Colby <jwcolby at gmail.com> wrote: > >> Pretty much sucks. >> >> Sigh. >> >> I have code that works now... if the query is a stored procedure pass >> through to SQL >> Server. If it is just a local query to a linked table in Access it fails >> with the very helpful error 5922 "Word was unable to open the data source". >> >> Does anyone know the magic for switching between ODBC pass through queries >> and local queries? It seems to have to do with the >> MailMerge.OpenDataSource parameters but I have spent hours futzing with >> this and I do have it working ... for pass through queries. >> >> -- >> John W. Colby >> >> Reality is what refuses to go away >> when you do not believe in it >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> >> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com> >> > >