{"id":335,"date":"2022-12-05T10:42:16","date_gmt":"2022-12-05T09:42:16","guid":{"rendered":"http:\/\/lesmrdtrucs.mataf35400.fr\/?page_id=335"},"modified":"2022-12-06T07:41:03","modified_gmt":"2022-12-06T06:41:03","slug":"access","status":"publish","type":"page","link":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/access\/","title":{"rendered":"Access"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Lancement d'Access depuis VB<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Exemple d’un bouton qui ouvre une BDD sous Access:<\/p>

Private Sub BtnBdd_Click()<\/em><\/span>
\u00a0\u00a0\u00a0 Dim MaBase As String, Tmp As String<\/em><\/span>
\u00a0\u00a0\u00a0 MaBase = Data.DatabaseName<\/em><\/span>
\u00a0\u00a0\u00a0 Tmp = \u00ab\u00a0C:\\Program Files\\Microsoft Office\\Office\\MSACCESS.EXE \u00a0\u00bb & Chr(34) & MaBase & Chr(34)<\/em><\/span>
\u00a0\u00a0\u00a0 Res = Shell(Tmp, vbMaximizedFocus)<\/em><\/span>
End Sub<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Compactage d'une BDD<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Le projet doit faire r\u00e9f\u00e9rence \u00e0 un moteur de Bdd. (D\u00e9j\u00e0 fait dans L’appli vide MRD).<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Proc\u00e9dure qui compacte:<\/p>

Sub CompactageBdd(MaSourceBdd as string , MaCibleBdd as string)<\/em><\/span>
\u00a0\u00a0 DBEngine.CompactDatabase MaSourceBdd, MaCibleBdd<\/em><\/span>
End Sub<\/em><\/span><\/p>

Attention:<\/p>

  • Le compactage se fait sur une base ferm\u00e9e.<\/li>
  • La base compact\u00e9e (ici MaCibleBdd) ne doit pas exister.<\/li><\/ul>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t

    R\u00e9cup\u00e9rer la description d'un champ Access<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Mettre dans un module:<\/p>

    Function fnctGetFieldDescription(ByVal WhatField As String, Optional ByVal PropertyName As String = \u00ab\u00a0Description\u00a0\u00bb, Optional ByVal CreateIt As Boolean = False)
    \u00a0\u00a0 \u00a0Dim oDB As DAO.Database
    \u00a0\u00a0 \u00a0Dim oTBL As DAO.TableDef
    \u00a0\u00a0 \u00a0Dim oField As DAO.Field
    \u00a0\u00a0 \u00a0Dim oPRP As DAO.Property
    \u00a0\u00a0 \u00a0Dim strDescription As String

    \u00a0\u00a0 \u00a0On Error GoTo L_Err_FieldDescription
    \u00a0\u00a0 \u00a0strDescription = vbNullString

    \u00a0\u00a0 \u00a0Set oDB = FrmDepart.Data.Database
    \u00a0\u00a0 \u00a0Set oTBL = oDB.TableDefs(\u00ab\u00a0Liste\u00a0\u00bb)
    \u00a0\u00a0 \u00a0Set oField = oTBL.Fields(WhatField)
    \u00a0\u00a0 \u00a0strDescription = oField.Properties(PropertyName)

    L_Ex_FieldDescription:
    \u00a0\u00a0 \u00a0Set oDB = Nothing
    \u00a0\u00a0 \u00a0Set oTBL = Nothing
    \u00a0\u00a0 \u00a0Set oPRP = Nothing
    \u00a0\u00a0 \u00a0Set oField = Nothing
    \u00a0\u00a0 \u00a0fnctGetFieldDescription = strDescription
    \u00a0\u00a0 \u00a0Exit Function

    L_Err_FieldDescription:
    \u00a0\u00a0 \u00a0If Err = 3270 Then
    \u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0If CreateIt Then
    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0Set oPRP = oField.CreateProperty(PropertyName, dbText, \u00ab\u00a0Nouvelle description\u00a0\u00bb)
    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0oField.Properties.Append oPRP
    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MsgBox \u00ab\u00a0La propri\u00e9t\u00e9 ‘\u00a0\u00bb & PropertyName & \u00ab\u00a0‘ a \u00e9t\u00e9 ajout\u00e9e au champ \u00a0\u00bb & WhatField & \u00ab\u00a0…\u00a0\u00bb, 48, \u00ab\u00a0Propri\u00e9t\u00e9 ajout\u00e9e \u00e0 la collection\u00a0\u00bb
    \u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0End If
    \u00a0\u00a0 \u00a0Else
    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MsgBox \u00ab\u00a0Impossible d’avoir des information sur le champ ‘\u00a0\u00bb & WhatField & \u00ab\u00a0‘ !\u00a0\u00bb & vbCrLf & Err.Description, 48, \u00ab\u00a0Erreur\u00a0\u00bb
    \u00a0\u00a0 \u00a0End If
    \u00a0\u00a0 \u00a0Resume L_Ex_FieldDescription
    End Function<\/em><\/span><\/p>

    Appeler la fonction:<\/p>

    \u00a0\u00a0\u00a0 MaDescription = fnctGetFieldDescription(.Fields(\u00ab\u00a0MonChamp\u00a0\u00bb))<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t

    Conversion d'une date pour compatibilit\u00e9 Access<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Function MakeUsDate(MaDate As Variant, Optional TimeAussi As Boolean = False)<\/em><\/span>
    \u00a0\u00a0 \u00a0If Not IsDate(MaDate) Then Exit Function<\/em><\/span>
    \u00a0\u00a0 \u00a0MakeUsDate = \u00ab\u00a0#\u00a0\u00bb & Month(MaDate) & \u00ab\u00a0\/\u00a0\u00bb & Day(MaDate) & \u00ab\u00a0\/\u00a0\u00bb & Year(MaDate)<\/em><\/span>
    \u00a0\u00a0\u00a0 If TimeAussi Then MakeUsDate = MakeUsDate & \u00a0\u00bb \u00a0\u00bb & Hour(MaDate) & \u00ab\u00a0:\u00a0\u00bb & Minute(MaDate) & \u00ab\u00a0:\u00a0\u00bb & Second(MaDate)<\/em><\/span>
    \u00a0\u00a0 \u00a0MakeUsDate = MakeUsDate & \u00ab\u00a0#\u00a0\u00bb<\/em><\/span>
    End Function<\/em><\/span>

    L’utilisation dans une requ\u00e8te SQL se fait de la sorte:

    \u00a0\u00a0 \u00a0Data.Recordset.FindFirst \u00ab\u00a0Jour = \u00a0\u00bb & MakeUSDate(MaFrenchDate)<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t

    Utilisation d'un Data dynamique<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Le projet doit faire r\u00e9f\u00e9rence \u00e0 un moteur de Bdd. (D\u00e9j\u00e0 fait dans L’appli vide MRD).<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Il faut d\u00e9clarer les variables: (D\u00e9j\u00e0 fait dans L’appli vide MRD).

    Global MaTmpBdd As Database<\/em><\/span>
    Global MonTmpRec As Recordset<\/em><\/span>

    A mettre dans la proc\u00e9dure qui l’utilise:

    \u00a0\u00a0 \u00a0Set MaTmpBdd = OpenDatabase(\u00ab\u00a0Mettre le nom de la base ici\u00a0\u00bb)<\/em><\/span>
    \u00a0\u00a0 \u00a0Set MonTmpRec = MaTmpBdd.OpenRecordset(\u00ab\u00a0Mettre le nom de la table ici\u00a0\u00bb, dbOpenDynaset)<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t

    Conversion d'une table Access en feuille Excel<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Cet exemple, pomp\u00e9 en 10 min d’apr\u00e8s le prog d’arrachement, est brut et sert \u00e0 cr\u00e9er un fichier Excel \u00ab\u00a0Etiq.xls\u00a0\u00bb comportant une feuille \u00ab\u00a0Etiquette\u00a0\u00bb contenant tous les enregistrements de la table \u00ab\u00a0Etiquette\u00a0\u00bb de la Bdd \u00ab\u00a0Etiq.mdb\u00a0\u00bb.

    Attention: pour que \u00e7a fonctionne, il faut que la feuille \u00ab\u00a0Etiquette\u00a0\u00bb du fichier Excel n’existe pas.

    Dans la zone d\u00e9claration d’une feuille (ou d’un module)

    Dim Ws As Workspace<\/em><\/span>

    Dans la proc\u00e9dure d’un contr\u00f4le (par exemple)

    Private Sub Command1_Click()<\/em><\/span>
    \u00a0\u00a0 \u00a0Dim Bdd As String<\/em><\/span>
    \u00a0\u00a0 \u00a0Dim Xls As String<\/em><\/span>
    \u00a0\u00a0 \u00a0Bdd = \u00ab\u00a0D:\\documents and Settings\\ebap188\\My Documents\\etiq.mdb\u00a0\u00bb<\/em><\/span>
    \u00a0\u00a0 \u00a0Xls = Replace(Bdd, \u00ab\u00a0.mdb\u00a0\u00bb, \u00ab\u00a0.xls\u00a0\u00bb)\u00a0\u00a0 \u00a0<\/em><\/span>

    \u00a0\u00a0 \u00a0Dim DataBdd As Database<\/em><\/span>
    \u00a0\u00a0 \u00a0Dim DataXls As Database\u00a0\u00a0 \u00a0<\/em><\/span>

    \u00a0\u00a0 \u00a0Set Ws = Workspaces(0)<\/em><\/span>
    \u00a0\u00a0 \u00a0Set DataBdd = Ws.OpenDatabase(Bdd)<\/em><\/span>
    \u00a0\u00a0 \u00a0Set DataXls = Ws.OpenDatabase(Xls, 0, 0, \u00ab\u00a0Excel 8.0;\u00a0\u00bb)\u00a0\u00a0 \u00a0<\/em><\/span>

    \u00a0\u00a0\u00a0 ‘DataBdd.Execute \u00ab\u00a0SELECT * into [Excel 8.0;database=\u00a0\u00bb & Xls & \u00ab\u00a0].Etiquette FROM Etiquette WHERE [Specif 1] = ’11KW\/400V'\u00a0\u00bb<\/em><\/span>
    \u00a0\u00a0 \u00a0 DataBdd.Execute \u00ab\u00a0SELECT * into [Excel 8.0;database=\u00a0\u00bb & Xls & \u00ab\u00a0].Etiquette FROM Etiquette\u00a0\u00bb<\/em><\/span>
    End Sub<\/em><\/span>

    Si l’erreur Isam appara\u00eet, c’est qu’il faut dans l’install Office cocher le truc qui va bien dans les donn\u00e9es (de m\u00e9moire. Faut fouiller pour plus de d\u00e9tail). <\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

    Lancement d’Access depuis VB Exemple d’un bouton qui ouvre une BDD sous Access: Private Sub BtnBdd_Click()\u00a0\u00a0\u00a0 Dim MaBase As String, Tmp As String\u00a0\u00a0\u00a0 MaBase = Data.DatabaseName\u00a0\u00a0\u00a0 Tmp = \u00ab\u00a0C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE \u00a0\u00bb & Chr(34) & MaBase & Chr(34)\u00a0\u00a0\u00a0 Res = Shell(Tmp, vbMaximizedFocus)End Sub Compactage d’une BDD Le projet doit faire r\u00e9f\u00e9rence \u00e0 un moteur de […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","footnotes":""},"class_list":["post-335","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages\/335","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/comments?post=335"}],"version-history":[{"count":16,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages\/335\/revisions"}],"predecessor-version":[{"id":403,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages\/335\/revisions\/403"}],"wp:attachment":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/media?parent=335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}