tag:blogger.com,1999:blog-5187043.post610989737293618012..comments2024-03-29T09:13:55.008+00:00Comments on UK Commentators: Techie TraumasLabanhttp://www.blogger.com/profile/12031578024191117985noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5187043.post-11465757922546605252008-02-27T09:30:00.000+00:002008-02-27T09:30:00.000+00:00Access has no way of allowing the user to prioriti...Access has no way of allowing the user to prioritise the joins. Or rather it chooses the priority for you. For performance sake, you should create sub queries and then join those. That reduces the amount of joins and makes the result a bit easier to read.<BR/><BR/>Also when Access gets corrupted so as to refuse to show the SQL, you can still sometimes access it from VB.<BR/><BR/>Something along the lines of<BR/>---<BR/>Sub prLabanSafe()<BR/> Dim qry As DAO.QueryDef, f As Long<BR/> With CurrentDb<BR/> 'create a file<BR/> f = FreeFile<BR/> Open .Name & ".txt" For Output As f<BR/> 'loop thru queries<BR/> For Each qry In .QueryDefs<BR/> Print #f, qry.Name<BR/> Print #f, "--------------"<BR/> Print #f, qry.SQL<BR/> Print #f, vbCrLf & vbCrLf<BR/> Next<BR/> 'tidy up<BR/> Close<BR/> Set qry = Nothing<BR/> End With<BR/>End Sub<BR/>---<BR/>That would create a text file containing all the SQL in the db including that included in forms. Then recovery would be achieved by cut and paste. In the past I have found SQL recoverable even though the query won't open, but there's no guarantees.<BR/><BR/>Of course, I'm probably teaching you to suck eggs and you've long moved on by now.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5187043.post-83878546482352556862008-02-27T08:31:00.000+00:002008-02-27T08:31:00.000+00:00Be fair, it's not got a huge amount of data in it ...Be fair, it's not got a huge amount of data in it - a couple of hundred thousand rows scattered over three or four tables. <BR/><BR/>As for the migration/testing - you live and learn. Timescales are tight.<BR/><BR/>After a few hundred thousand rows Access data import/export starts to crumble - but using VB code you can import over a million rows and get meaningful results from queries quite quickly.<BR/><BR/>I think it would take me much longer to develop in LAMP than Access - I'd have to learn it first, for a start. And the client site's administrators would have told me to go away - I have to use their tools. The Access is a 'quick and dirty' solution and approved as such - a mighty .net / SQL Server app is being built, but it's six months away and they want this bit of the functionality in six weeks.Labanhttps://www.blogger.com/profile/12031578024191117985noreply@blogger.comtag:blogger.com,1999:blog-5187043.post-44020102769889428102008-02-27T00:22:00.000+00:002008-02-27T00:22:00.000+00:00ELEVEN unions in one query? In an ACCESS database?...ELEVEN unions in one query? In an ACCESS database? And then you thought you could move from ONE major version of an MS product to another without testing!<BR/><BR/>I had you down as a wise man Mr Tall.<BR/><BR/>One of the things I like about LAMP is that there's very little of this kind of thing to worry about. You kind of know what you're getting in terms of presentation, business and database logic, and it doesn't change very much, and of course if you provide the software as a service the only variable you don't have control over is their browser.Anonymousnoreply@blogger.com