[dba-SQLServer] Preparing for upsizing

Jim Lawrence accessd at shaw.ca
Mon Jan 23 11:45:51 CST 2006 

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

More information about the dba-SQLServer mailing list