העברת DB מ- Access על Sql Server באמצעות SSMA

בפוסט הזה אני מציין מספר נקודות מעניינות לגבי העברה של DB מסביבת Access אל Sql Server ( במקרה שלי גירסת Exrpress )

1- מיקרוסופט הסירו מאקסס את האפשרות  לייצא ישירות ל- SQL SERVER

הם ממליצים להשתמש בכלי שלהם שנקרא Sql Server Migration Assitant

יש גירסאות שלו עבור כמה סוגי DB נפוצים ( MySQL ועוד )

 

2- הכלי עובד יפה, אם כי לא אינטואיטיבי כל כך – ועל כך הפוסט הזה.

דברים שצריך לשים לב אליהם :

שלב 1  – יצוא הטבלאות מ- Access אל Sql Server

 

  • עוד לפני שמפעילים את הכלי – צריך להיות מודעים להתאים גירסת האופיס לגירסת הכלי ( ה=SSMA)
    אם ה-Access הוא בגירסת 32 ביט, צריך להפעיל את ה-SSMA 32 Bit  ( כי ה-Default הוא 64  ביט )
    שימו לב שההתקנה הרגילה מתקינה אצלכם ברשימת התוכניות – 2 גירסאות (32+64 )
    אם לא מפעילים את הגירסה שתואמת ל-Office שמותקן על המכונה, מקבלים בהמשך הודעת שגיאה שה-DB שניסיתם לטעון בלתי ניתן לקריאה…מה שכמובן לא נכון.
  • יצירת DB – לפני שמפעילים את הכלי, יש לייצור DB ב-SQL Server  שיקבל את הטבלאות שנייצא.
  • יצירת USER עבור ה-DB – שרת ה-Sql server מציע 2 צורות של אוטנטיקציה.
    אם משתמשים ב-Windows Auth… על אותה מכונה, לרוב לא תהיה בעיה.
    אם זה תחת שרתים אירגוניים, צריך לוודא שליוזרים יש הרשאות ל-DB,
    חשוב  לתת ליוזרים הרשאות , גם תחת "Security" של ה-Sql Server (=הכללי בעץ)
    וגם תחת Security של ה-DB הספציפי.
    אפשרות קצת פשוטה יותר -היא שימוש ב-Sql Auth, וגם שם צריך לוודא שהיוזר שיצרתם, מורשה הן לגישה ל-Server והן ל-DB הספציפי.
  • כאשר סף סוף מפעילים את הכלי – עדיף לא להשתמש ב-wizard שקופץ בכניסה – הוא אומנם מוביל שלב אחרי שלב, אך מדלג  על חלק מהשלבים, ולכן לא כל כך עוזר…
  • הגדרה שכדאי, ואפילו חובה לבצע עוד בהתחלה, היא להיכנס למקום שבו מוגדר לאיזה סוג שדה הופך כל אחד מהשדות ( למשל :  שדה TEXT של Access הופך ל- nvarchar של Sql Server וכדומה )
    זה נעשה תחת Tools < Default Project Settings > Type Mapping
    כל ההגדרות תקינות …חוץ מאשר….Date של Access  – שמועבר בצורה שגויה אל Datetime(2) של Sql Server , הבעיה היא, שכאשר נסיים את תהליך העברת הטבלאות, ונקשור את הטבלאות בחזרה אל פרוייקט ה-Access שלנו, תהיה לנו בעיה – ש-Access לא יודע לקרוא את סוג השדה הזה , ולכן ממיר אותו אל Text …וזה כבר יוצר שגיאות וולידציה, ובעיות בקוד ה-VBA שמצפה ל- Datetime.
    הפתרון הוא להגדיר את השדות כ- Datetime רגיל ( בלי ה-2 )
    במקרה זה – ההמרה עוברת תקין.
  • השלב הראשון, הוא "יצירת פרוייקט" (דרך התפריט או דרך כפתור למעלה)
    חשוב לשים לב – לבחור במסך יצירת הפרוייקט את גירסת ה- Sql Server שלכם
    ה-DropDown הוא כמעט מוסתר, וברירת המחדל היא Azure….מה שלא בטוח שהתכוונתם.

    • נקודה נוספת באותו עניין – יש אפשרות להגדיר את זה תחת Tools > Defailt project settings  (גם כאן ה-Dropdown נסתר מהעין)
  • לאחר שמוסיפים את ה-DB של Access באמצעות Add Databases , יש לבצע שמירה של הפרויקט – השמירה גורמת ל-SSMA לטעון את ה-Metadata של הטבלאות.
    למה…ככה (מיקרוסופט ? )
  • השלב הבא הוא חיבור ל-SQL SERVER,  אם לא בחרתם מראש בגירסת ה-SQL SERVER הנכונה, אזי לא תוכלו להתחבר ל-SQL שלכם.
  • בסוף אחרי שהכל מוגדר , כדי לגרום לכפתור ה- Convert-Load_AND-MIGRATE לעבוד , חייבים ללחוץ ללחוץ על ה-Access DB הרלוונטי – ורק אז הכפתור הופך ל-Enabled
  • לאחר לחיצה על כפתור המרה ( Convert..load..and migrate )
    הכלי עובד די מהר וחלק, טוען את כל הטבלאות , ובונה אותן, כולל את המפתחות הזרים והאינדקסים.  אפשר לומר שזה החלק הטוב בכלי הזה, שבסופו של דבר הוא באמת מבצע את העבודה.

שלב 2 – חיבור של קובץ ה-Access אל הטבלאות ב-Sql Server

זה תהליך די מוכר לכל מתכנת Access, ואכתוב אותו בקצרה :

  • מוחקים ( בלי לפחד 🙂 ) את הטבלאות המקוריות ( בין אם הם מקושרות, ובין אם מאוחסנות בקובץ הזה )
  • תחת "נתונים חיצוניים" > יבוא וקישור >  מסד נתונים של ODBC > ניצור Connection חדש או בקובץ, או של מערכת ההפעלה  ולאחר יצירת ה-Connection נבחר בו כדי לקשור את הטבלאות.
    • בשלב יצירת ה-Connection יש לבחור בדרייבר של Sql Server , או Sql Server ODBC Driver
    • באחד ממסכי יצירת ה-Connection יש לשים לב לאפשרות של בחירת ה-Default Database – ולהפעיל אותה רק על ה-DB הרלוונטי ב-SQL Server
  • לאחר שנבחר ב-Connection שיצרנו – נסמן את הטבלאות לקישור, יש לשים לב לא לסמן גם את טבלאות המערכת של Sql server , אלא רק את הטבלאות המקוריות ששיכות לפרויקט שלנו ( זה לא משנה, אבל חבל סתם להעמיס טבלאות מקושרות שלא נחוצות על הפרוייקט)
  • אם אתם לא רוצים לשגע את היוזרים – כדאי לסמן את האפשרות לשמור את הסיסמה ( ואז ללחוץ על אישור בנפרד לכל אחת מהטבלאות….מיקרוסופט …!!!! )
  • זה עוד לא נגמר …בשלב זה, כיוון שהטבלאות ב-Sql Server מתחילות תחת "איזור" שנקרא לרוב dbo. אז בקישור ל-Access, פתאום מתווסף לכל הטבלאות בתחילת השם, הביטוי dbo_ , צריך לעבור טבלה-טבלה, ולשנות את השם בחזרה לשם המקורי. (או לכתוב\למצוא סקריפט שעושה זאת …)
    אחרת – אם לא נעשה זאת, השאילתות שלנו לא יעבדו.
  • וכאן הגיע הזמן לבדוק שהכל תקין.

בהצלחה.

 

נקודה אחרונה, אך ממש לא קשורה – למי שמפתח Web על אותה מכונה שעליה מותקן ה-Sql Server.

אחד ה-Services שמופעלים בהתקנת ה-Sql Server, נקרא Sql Server Reporting Service

ולמרבה הפלא …הוא תופס את Port 80  ….

כך שאם רוצים להמשיך לעבוד , צריך : או להשבית אותו ( services.msc וכו' )  או להמשיך לעבוד בפורט אחר עבור ה-localhost על המכונה.

פונקציה לבדיקת האם מחרוזת מתחילה באנגלית – VBA (Access)

לפעמים צריך ליישר אוטומטית

שדה ב-VBA

לשם כך בניתי פונקציה שבודקת האם השדה מתחיל באנגלית

במידה וכן – ניישר אותו בהתאם.

Public Function StringStratInEnglish(str As String) As Boolean
 Dim first_char As String
 On Error Resume Next
 
 StringStratInEnglish = False
 
 If Len(str) = 0 Then GoTo ExitHere
 first_char = Mid(str, 1, 1)
 
 If (Asc(first_char) >= 65 And Asc(first_char) <= 90) Or _
 (Asc(first_char) >= 97 And Asc(first_char) <= 122) Then
 
 StringStratInEnglish = True
 End If
 
ExitHere:
 Exit Function
End Function

התקנה שקטה של Access Runtime

כאשר אתה מפיץ תוכנות מבוססות Access, עם Access runtime

ואתה אורז את זה להפצה, נניח עם Inno Setup או תוכנות דומות.

אתה מעוניין שההתקנה של Access Runtime תרוץ בלי שאלות למשתמש

בלי קשקושים, בלי "אני מקבל את ההסכם" וכו' כו'

לצורך כך צריך לבצע את הפעולות הבאות :

הערה מקדימה :  מי שעובד בארגונים ומפיץ גירסה ארגונית , יכול להשתמש בכלי של מיקרוסופט לאריזת התקנות כאלו : OCT.

קובץ ההתקנה שיורד הוא למעשה Self-Extracting  שמכיל קבצים ותיקיות רבים.

אז דבר ראשון צריך אנחנו נחלץ את כל הקבצים לתיקיה , ואז כאשר יהיו לנו את קבצי ההתקנה עצמה של Access Runtime, רק אז נוכל להגדיר את ההתקנה השקטה (silent install).

החילוץ :

YourAccessRuntimeInstallFile /extract:YourFolder

ואז ניכנס לתיקיה שאליה חילצנו את הקבצים.

וניצור קובץ בשם config.xml  שיכיל את ה-XML הבא :

<Configuration Product="AccessRT"> 
<Display Level="Basic" CompletionNotice="Yes" SuppressModal="Yes" NoCancel="Yes" AcceptEula="Yes" /> 
<Logging Type="standard" Path="C:TEMP" Template="Microsoft_Access_2013_Runtime_Setup(*).log" /> 
<COMPANYNAME Value="Y" /> 
<Setting Id="SETUP_REBOOT" Value="Never" /> 
</Configuration>

אפשר למצוא הסבר מלא על כל הפרמטרים , וכל ה-nodes של ה-XML הזה בקישור הבא : http://technet.microsoft.com/en-us/library/cc179195(v=office.15).aspx

ואז מריצים את הקובץ setup שנמצא בתוך התיקיה שחילצנו  עם הפרמטר הבא :

setup.exe /config YourConfigFileFullPath.xml

וזהו… זה רץ בהתקנה שקטה.

 

אקסס – התמודדות עם תקלה 3211 – טבלה נעולה (Access VBA Error 3211 )

קורה לפעמים, מצב שבו צריך לשנות דברים בטבלה , למרות שהיא נעולה

המצב שלי היה כזה : לטופס היה טופס משנה שמבוסס על טבלה זמנית

וברגע שמבצעים פעולה מסויימת, הטבלה הזמנית צריכה להימחק ולהרשם מחדש ואז טופס המשנה מתרענן.

הבעיה – Access לא נותן לך למחוק את הטבלה, כאשר טופס מאוגד אליה.

הפתרון – במקום למחוק את הטבלה, אפשר פשוט למחוק את הרשומות שבה, את זה אקסס כן מתיר.

ואז , את השאילתת שיוצרת את הטבלה הזמנית – יש להחליף בשאילתא INSERT רגילה.

בצורה כזו, Access מאפשרת את כל הפעולות, גם דרך ADO Recordset וגם דרך שאילתות.

בהצלחה!

יצירת טבלה זמנית ב-Access באמצעות VBA ומילוי שלה מתוך Recordset

לפעמים בשימוש ב-Access צריך להשתמש בטבלה זמנית.

ולפעמים הטבלה הזמנית צריכה להיות מבוססת על Recordset ADO

למען הסר ספק אין באמת טבלאות זמנית באקסס, זוהי רק טבלה רגילה, שפשוט נוצרת  לכמה רגעים, ובפעם הבאה שנריץ את הפרוצדורה, היא תימחק.

להלן המודול, ללא יותר מדי הסברים,

בקצרה – שולחים אליו רקורדסט ADO קיים, ואת השם הרצוי של הטבלה שתיווצר, והטבלה נוצרת מייד.

תוך כדי יצירת הטבלה, הפרוצדורה בודקת את השדות השונים, והגדרות השדות עוברות המרה מהפורמט של ADO אל הפורמט של Access .

 

Public Enum ETempTableActions
    CreateNewTableAndFillIt = 1
    OnlyFillExistsTable = 2
End Enum

Public Sub CreateTempLocalTable(tblName As String, ByRef rec As Object)
'                                                           פרוצדורה שיוצרת טבלה מקומית זמנית
'                                                                  על סמך רקורדסט שהועבר אליה
'                                         בהתחלה בודקים אם הטבלה קיימת ואם כן ננסה למחוק אותה
'                                                      אם היא נעולה, אז נמחק רק את תוכן הטבלה
'                                          במידה ומחקנו את הטבלה, ניצור אותה מחדש + נמלא אותה
'                                                 במידה ורק רוקנו את תוכנה, אז נמלא אותה מחדש
    On Error GoTo Error_Handel
      
    Dim dbs As Database, tdfNew As TableDef, fldTemp As Field, rst As DAO.Recordset, DAOfld As Variant, fld As Object, Proccess As Integer
    
    Set dbs = CurrentDb
    
    If isTableExists(tblName) Then
        
        If DeleteTable(tblName) = True Then
            Proccess = ETempTableActions.CreateNewTableAndFillIt
        Else
            Proccess = ETempTableActions.OnlyFillExistsTable
        End If
    
    Else
        
        Proccess = ETempTableActions.CreateNewTableAndFillIt
    
    End If
    
    Select Case Proccess
        
        Case ETempTableActions.CreateNewTableAndFillIt
            
            Call CreateNewTable(dbs, tdfNew, rec, tblName)
            Call InsertRecToTable(rec, tblName)
        
        Case ETempTableActions.OnlyFillExistsTable
            
            Call InsertRecToTable(rec, tblName)
    
    End Select
             
ExitHere:
    
    Exit Sub
Error_Handel:
    Err.Clear
    Resume ExitHere
End Sub
Private Sub InsertRecToTable(ByRef rec As Object, tblName As String)
    
    On Error Resume Next

    Dim rst As Recordset, DAOfld As Variant

    If Not rec.EOF Then
        rec.MoveFirst
        Set rst = CurrentDb.OpenRecordset(tblName) 'פותח עוד רקורדסט שיכיל את  הטבלה החדשה שנוצרה
        
        Do While Not rec.EOF
            
            rst.AddNew
            
            For Each DAOfld In rst.fields
                
                If DAOfld.Type = dbBoolean Then
                    rst.fields(DAOfld.Name).Value = CBool(rec.fields(DAOfld.Name).Value)
                Else
                    rst.fields(DAOfld.Name).Value = rec.fields(DAOfld.Name).Value
                End If
            
            Next DAOfld
            
            rst.Update
            
            rec.MoveNext
         Loop
    End If
    
    rst.Close
    Set rst = Nothing

    If Err.Number <> 0 Then Err.Clear

End Sub
Public Function isTableExists(tblName As String) As Boolean
'                       בודק אם טבלה בשם הזה קיימת
'                                מחזיר ערך בוליאני
    On Error GoTo Error_Handel
    Dim db As Database, tbl As TableDef, I As Integer
    Set db = CurrentDb()
    isTableExists = False
        
    For Each tbl In db.TableDefs
        If tbl.Name = tblName Then
             isTableExists = True
             Exit Function
        End If
     Next tbl

ExitHere:
    Exit Function
Error_Handel:
    Err.Clear
    isTableExists = False
    Resume ExitHere
End Function

Private Sub CreateNewTable(ByRef dbs As Object, ByRef tdfNew As TableDef, ByRef rec As Object, tblName As String)
    
    On Error GoTo Error_Handel
    
    Dim fldType As Variant, fld As Object, fldTemp As Field
    
    Set tdfNew = dbs.CreateTableDef(tblName)
    Set fld = CreateObject("ADOX.Column")   'CreateObject("ADODB.Field")
    
    With tdfNew
          ' Create fields and append them to the new TableDef
          ' object. This must be done before appending the
          ' TableDef object to the TableDefs collection of the
          ' database.
          For Each fld In rec.fields
                
                Select Case fld.Type
                    Case 202
                        fldType = dbText
                    Case 201
                        fldType = dbMemo
                    Case 10
                        fldType = dbText
                    Case 135
                        fldType = dbDate
                    Case 11
                        fldType = dbBoolean
                    Case Else
                        fldType = fld.Type
                End Select
        
                Set fldTemp = tdfNew.CreateField(fld.Name, fldType)
                If (fldType = dbText Or fldType = dbMemo) Then fldTemp.AllowZeroLength = True
                
                tdfNew.fields.Append fldTemp
            
            Next fld
    
    End With

    dbs.TableDefs.Append tdfNew
    dbs.TableDefs.Refresh
    DoEvents

ExitHere:
    Exit Sub
Error_Handel:
    Err.Clear
    Resume ExitHere
End Sub

Public Function DeleteTable(tblName As String) As Boolean
     
     On Error Resume Next
     Err.Clear
     Dim DeleteSql As String
     
     DoCmd.DeleteObject acTable, tblName
    
     DoEvents
'           לפעמים הטבלה נעולה ותתקבל במקרה הזה תקלה
        '                  במקרה כזה, נרוקן את הטבלה מרשומות
        '      ונמשיך מייד למילוי הטבלה , בלי ליצור אותה מחדש
     If Err.Number <> 0 Then '= 3211 Or Err.Number = 3021 Then
            
            DeleteSql = "DELETE * FROM " & tblName & ";"
                
                DoCmd.SetWarnings False
                DoCmd.RunSQL DeleteSql
                DoCmd.SetWarnings True
            
            DoEvents
            Err.Clear
            DeleteTable = False
     Else
            DeleteTable = True
     End If
   
End Function

בדיקה אם שאילתא קיימת, ומחיקת שאילתא דרך VBA ב- Access

ב- Access פעמים מסויימות צריך ליצור שאילתא תוך כדי ריצה

למשל כאשר רוצים ליצור שאילתת Pivot / Transpose  מותאמת למצב מסוים.

להלן 2 פונקציות Access VBA שמטפלות בשאילתות.

בדיקה אם שאילתא קיימת

 

Public Function isQueryExists(qryName As String) As Boolean

    On Error GoTo ErrorHandel


 Dim db As Database
 Dim qry As QueryDef
 Dim I As Integer
 Set db = CurrentDb()
 isQueryExists = False
 
 
 For Each qry In db.QueryDefs
               If qry.Name = qryName Then
                    isQueryExists = True
                    Exit Function
               End If
          Next qry

ExitHere:
    Exit Function
ErrorHandel:
    Err.Clear
    isQueryExists = False
    Resume ExitHere
End Function

מחיקת שאילתא

Public Sub DeleteQuery(qryName As String)
On Error Resume Next
    

    If isQueryExists(qryName) Then
        DoCmd.DeleteObject acQuery, qryName
        DoEvents
    End If

If Err.Number <> 0 Then Err.Clear
End Sub

הצגת מונה רשומות בתוך טופס Access

באקסס , פעמים מסוימות נרצה להציג מונה רשומות בתוך הטופס עצמו

ולא להשתמש במונה המובנה של אקסס

 

השיטה הכי פשוטה היא לעשות תיבת טקסט ובתוכה

=" Record  " & [CurrentRecord] & " From  " & Count(*)


אבל  הבעיה היא שהשיטה הזו מחזיקה מעמד רק עד שפותחים רשומה חדשה

ברגע שפותחים רשומה חדשה – מקבלים דברים מוזרים כמו "רשומה 7 מתוך 6" וכדומה.

 

אז שיטה יותר טובה היא לשים באירוע של OnCurrent ( בנוכחי) קוד שבמידה והטופס נמצא על רשומה קימת – נשתמש ב-DCount כדי למנות את מספר הרשומות הקיים.

 

 If Me.NewRecord Then
 Me.txtCounter.Value = "New Record ( " & DCount("[PolicyID]", "tblPolicies", "[linkID]='" & Nz(Me.linkID, "") & "'") & " Exists Records "
 
 Else
 Me.txtCounter.Value = "Record " & Me.CurrentRecord & " Of " & DCount("[PolicyID]", "tblPolicies", "[linkID]='" & Nz(Me.linkID, "") & "'")
 End If

כמובן שתשנו את ה-Dcount לטבלה הרלוונטית עבורכם.

 

מקור להרחבה ושיטות נוספות : http://www.fontstuff.com/mailbag/qaccess04.htm

 

שימוש ב-Web Control המובנה של Access (דפדפן) ב-VBA

בגירסאות האחרונות של Microsoft Access יש Web Control מובנה

זה נוח מאוד כי הוא יכול להיות קשור לשדה, ולהשתנות בהתאם.

ב-VBA הגישה היא כזו :

.YourWebControl.ControlSource = "=" & Chr(34) & file_name & Chr(34)

לחלופין אם זו כתובת קבועה אפשר כך (ההכפלה של המרכאות היא כדי ליצור מופע אחד של מרכאות בתוך המחרוזת).

YourWebControl.ControlSource = "=""www.google.co.il""

העברת ADO RECORDSET אל מערך ב-VBA

קורה לפעמים שעדיף לרוץ על מערך בזיכרון מאשר על רקורדסט – זה הרבה הרבה יותר מהיר

להלן פונקציה שמעבירה את הרקורדסט למערך.


Public Function RecordsetToArray(ByRef rs As Object) As Variant
    Dim tmp As Variant, cols_num As Integer, K As Integer, rows_num As Integer, J As Integer
    
    If Not (rs.EOF And rs.BOF) Then
    
        rs.MoveFirst
        cols_num = rs.fields.Count
        rows_num = rs.RecordCount
        
        ReDim tmp(rows_num, cols_num)
            
            J = 0
            Do While Not rs.EOF
                For K = 0 To cols_num - 1
                      tmp(J, K) = Nz(rs.fields(K).Value, "")
                Next K
                J = J + 1
                rs.MoveNext
            Loop
    
    Else
        tmp = Array("")
    
    End If
    
    RecordsetToArray = tmp
    
End Function




המתנה ב-ACCESS VBA באמצעות WINDOWS API

אם צריך לחכות מספר שניות, עד שקובץ נטען, או נוצר
או להמתין שפעולה מסוימת תתבצע.

להלן קוד.

#If VBA7 Then
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If


Sub SleepVBA(ms As Integer)
'ms = milisecondes
Sleep ms
End Sub