Robert L. Stewart
rl_stewart at highstream.net
Tue Jan 24 10:37:13 CST 2006
Actually Erwin, I use Erwin (now called AllFusion Data Modeler from Computer Associates) to do all my upsizing. :-) Yes, it is an expensive tool, about $3,000 a seat. But, it is fast and works great with a number of databases. I can redo a design in about 3 or 4 days from Access to almost any database. At 11:13 PM 1/23/2006, you wrote: >Date: Mon, 23 Jan 2006 21:48:24 +0100 >From: "Erwin Craps - IT Helps" <Erwin.Craps at ithelps.be> >Subject: Re: [dba-SQLServer] Preparing for upsizing >To: <dba-sqlserver at databaseadvisors.com> >Message-ID: > <46B976F2B698FF46A4FE7636509B22DF1B62D7 at stekelbes.ithelps.local> >Content-Type: text/plain; charset="us-ascii" > > >Pfff, a month work, getting discouraged already. >Altough I'm aware theres no magic tool. > >I was hoping to migrate the app, without to much change to the code. >Yeah yeah, I still believe in fairytales. >This app is really the app for which I lost sleep, blood, sweat and my >(good?) sense of humor. It's has a huge amount of code and special >tricks in it and its the largest database I have (about 600 MB, >1,608,014 records only in the main table). > >And the funny part is that not a single customer works with it. >We at our office are the only ones using it with 3 people, that is >except for the webclient) > >I know I will need to upsize one day, but I'm gonna get gray hair of it. >Whats the MDB file size limit again? Maybe I can pull it a couple of >years further... > >Maybe in a couple of years MS stops with MDB files and creates us a >wonderfull full analytic and upsize wizard that does the job over a >cofee or two?! > >Erwin > > > > >-----Oorspronkelijk bericht----- >Van: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] Namens Jim Lawrence >Verzonden: maandag 23 januari 2006 18:46 >Aan: dba-sqlserver at databaseadvisors.com >Onderwerp: Re: [dba-SQLServer] Preparing for upsizing > >Erwin: > >Three months ago I upsized a legacy system. Before the final process was >ran, it took almost a month to build and test the importing and data >manipulation routines. The structure needed some better functionality, >better normalisation, some new tables, loss some tables and a thorough >testing as once the process was started on the live data it had to go >straight through without errors. > >The upsize process when started, at 5:00PM Friday night, completed >without an incident by 11:00PM and everyone was online, Monday morning. >It was well worth the preparation time. > >My 2 cents worth. >Jim > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Erwin >Craps >- IT Helps >Sent: January 23, 2006 1:12 AM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Preparing for upsizing > > >I was thinking about writing such a tool to, in order to upsize one of >my biggest database. I'm postponing this upsize already for a year or >two now, and I supose it will be not until after the summer holiday... > > >But I was considering to first store the SQL strings in a table , run a >grouping on it/analyse to minimize the number of queries and posibly to >improve them (manualy), and afterwards alter the forms/reports. > >This to avoid to have 1 query per combo/listbox. > >Erwin > > > > > > > > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan >Waters >Sent: Friday, January 20, 2006 4:10 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Preparing for upsizing > >John, > >There's nothing quite like making your own tools! > >Dan > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John >Colby >Sent: Thursday, January 19, 2006 10:23 PM >To: 'Access Developers discussion and problem solving'; >dba-sqlserver at databaseadvisors.com >Subject: [dba-SQLServer] Preparing for upsizing > >I am preparing for upsizing a client's access database. In order to do >this I needed to search all the objects looking for SQL statements and >save them as saved queries. In order to do this with forms I created >the following two functions: > >FindSQL opens each form in the database in design view, then passes the >name of the form and the form's RecordSource off to a function >BldSavedQuery. It then looks for all controls in the form's control >collection looking for combos and list boxes, and does the same with >them. > >BldSavedQuery looks for issues like nothing in the SQL statement passed >in, the "QryName" already being the name of a query or table, and if >neither of those conditions exist, then it tries to build a named query >def. If it succeeds (the SQL passed in is valid and the name passed in >is not already a saved query) it creates a saved query and passes the >name back to FindSQL which then stores the saved query name back in >place of the SQL statement. >When all processing is finished, the form is then saved and closed. > >It works reasonably well. The only things found so far are delimited >lists instead of SQL statements and what appear to be saved query names >where the saved query was deleted. Only about 17 of those, all but one >are delimited lists. It took me ~ 2 hours to write / debug this code >and saved me who knows how much time manually hunting for these things. > >I now have to do the same thing for reports, though there are so few I >might just do them manually. > >Function BldSavedQuery(strQryName As String, strSQL As String) As String >On Error GoTo Err_BldSavedQuery Dim tdf As DAO.TableDef Dim qdf As >DAO.QueryDef Dim qdfs As DAO.QueryDefs Dim db As DAO.Database Dim >lstrQryName As String > > ' > 'This object has nothing in the rowsource/recordsource (probably an >unbound form) > ' > If strSQL = "" Then > BldSavedQuery = "" > GoTo Exit_BldSavedQuery > End If > Set db = CurrentDb > ' > 'Check to see if the strsql is the name of a table > ' > On Error Resume Next > Set tdf = db.TableDefs(strSQL) > If Err = 0 Then > GoTo Exit_BldSavedQuery > End If > Err.Clear > ' > 'Check if the strSQL is a query name > ' > Set qdf = db.QueryDefs(strSQL) > If Err = 0 Then > GoTo Exit_BldSavedQuery > End If > Err.Clear >On Error GoTo Err_BldSavedQuery > > lstrQryName = "q" & strQryName > Set qdfs = db.QueryDefs > Set qdf = New QueryDef > With qdf > .SQL = strSQL > .Name = lstrQryName > End With > qdfs.Append qdf > qdfs.Refresh > BldSavedQuery = qdf.Name >Exit_BldSavedQuery: >On Error Resume Next > If Not (qdf Is Nothing) Then qdf.Close: Set qdf = Nothing > Set qdfs = Nothing > If Not (db Is Nothing) Then db.Close: Set db = Nothing Exit Function >Err_BldSavedQuery: > Select Case Err > Case 3012 'Query already exists > BldSavedQuery = "" > Debug.Print "ERROR: " & lstrQryName & " already exists, but this >control has a SQL statement in the rowsource" > Resume Exit_BldSavedQuery > Case 3129 'Not an SQL statement - mostly delimited lists or >deleted >saved query names > BldSavedQuery = "" > Debug.Print "ERROR: " & lstrQryName & " not a sql statement in >the rowsource but not a saved query or table either" > Debug.Print vbTab & "Rowsource: " & strSQL > Resume Exit_BldSavedQuery > Case Else > MsgBox Err.Description, , "Error in Function >basFindSQL.BldSavedQuery" > Resume Exit_BldSavedQuery > End Select > Resume 0 '.FOR TROUBLESHOOTING >End Function >' >'This module will find all sql statements in objects such as forms, >combos and list boxes 'and save them as saved queries in the name >format: >' >'qfrmName >'qfrmname-CboName >'qfrmName-LstName >' >Function FindSQL() >On Error GoTo Err_FindSQL >Dim db As DAO.Database >Dim doc As DAO.Document >Dim frm As Form >Dim ctl As Control >Dim strQryName As String > Set db = CurrentDb > For Each doc In db.Containers(2).Documents > DoCmd.OpenForm doc.Name, acDesign > Set frm = Forms(doc.Name) > strQryName = BldSavedQuery(frm.Name, frm.RecordSource) > If Len(strQryName) > 0 Then > frm.RecordSource = strQryName > End If > For Each ctl In frm.Controls > Select Case ctl.ControlType > Case acComboBox, acListBox > strQryName = BldSavedQuery(frm.Name & "-" & ctl.Name, >ctl.RowSource) > If Len(strQryName) > 0 Then > ctl.RowSource = strQryName > End If > Case Else > End Select > > Next ctl > DoCmd.Close acForm, doc.Name, acSaveYes > Next doc >Exit_FindSQL: >On Error Resume Next > If Not (frm Is Nothing) Then frm.Close: Set frm = Nothing > If Not (db Is Nothing) Then db.Close: Set db = Nothing Exit Function >Err_FindSQL: > MsgBox Err.Description, , "Error in Function basFindSQL.FindSQL" > Resume Exit_FindSQL > Resume 0 '.FOR TROUBLESHOOTING >End Function > >John W. Colby >www.ColbyConsulting.com > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com