2a.) Example of visualbasic module in Access to create KML files with Oil & Gas Inventory Geolocations Option Compare Database Option Explicit Public Sub OilGas_Summary_KML_Public() '****** Creates O&G Summary KML file with network links to HTML details on Fbx server.******' '****** this module builds a parameter query that selects all APIs for the selected ******' '****** data type then prints the appropriate summary in KML. A loop rolls thru all ******' '****** active tables. Currently samples with no APIs are not selected for display ******' '****** in the KML files. Wells with no AOGCC Well Header are also not included. ******' '****** This code has no subdirectory and file management -- just file overwriting. ******' '****** Query 1 uses tables [ltbl_API_by_TABLE] and [Wells - Public Header Data] to ******' '****** locate all wells with inventory materials. ******' '****** Query 2 uses table [ltbl_API_by_TABLE] and [ltbl_Table_HTML_Switch] to find ******' '****** each material for that well and the HTML switch information. ******' '****** Query 3 uses tables [tbl_PUBLICATIONS_SOURCE_MAT] and [tbl_PUBLICATIONS] to ******' '****** find GMC Data Reports for the well. '****** INPUT QUERY 1: ******' '****** (0)Well (1)Lat (2)Long (3)API_SNIPPET (4)PERMIT_SNIPPET (5)OPERATOR_SNIPPET ******' '****** (6)DEPTH_SNIPPET (7)AREA_SNIPPET' (8)API (9)COMPLETIONDATE_SNIPPET ******' '****** INPUT QUERY 2: ******' '****** (0)ltbl_API_by_Table.Source_Table (1)tbl_API_by_Table.IntervalTop ******' '****** (2)ltbl_API_by_Table.IntervalBottom (3)ltbl_API_by_Table.Records ******' '****** (4)ltbl_Table_HTML_Switch.Table_Label (5)ltbl_Table_HTML_Switch.Publish_Flag ******' '****** INPUT QUERY 3: ******' '****** (0)tbl_PUBLICATIONS_SOURCE_MAT.API (1)tbl_PUBLICATIONS.Title ******' '****** (2)tbl_PUBLICATIONS.Publication_Number (3)tbl_PUBLICATIONS.Url ******' '****** MODIFICATIONS ******' '****** 4/21/2010 Added Shell command functionality below ******' '****** RetVal = Shell("F:\Databases\Bat\zip-oilgas-kml-public.bat", 1) ******' '****** which runs a .bat file to zip the kml and renames to kmz' ******' '****** Define data types ****** Dim MyDB As Database Dim MyRS As Recordset Dim MyRS2 As Recordset Dim MyRS3 As Recordset Dim strSQLtype As String Dim strSnippet As String Dim strSQL As String Dim strReport As String Dim strWebPath As String Dim strHrefFile As String Dim n As Integer Dim RetVal As String '****** FBX Server path for HTML inventory links ******' strWebPath = "http://www.dggs.alaska.gov/gmc_inventory/google_earth/details/oilgas_inventory/" '****** Export data to kml text files ******' Open "F:\Google~1\Curren~1\public\invent~1\alaska_gmc_oil_gas_pub.kml" For Output Shared As #1 '****** Turn on/off to debug code ******' 'Open "F:\google~1\curren~1\oilgas_public_kml_debug.txt" For Output Shared As #2 '****** Find Wells with materials inventory ******' strSQLtype = "SELECT DISTINCT [WellName]+"" #""+[WellNumber] AS Well, IIf([SurfaceLat]="""",61.3493,[SurfaceLat]) AS Lat, IIf([SurfaceLong]="""",-149.5377,[SurfaceLong]) AS [Long], [Wells - Public Header Data].[API_WellNo], [Wells - Public Header Data].[OperatorName], [Wells - Public Header Data].[PermitNumber], [Wells - Public Header Data].[TotalDepth], [Wells - Public Header Data].[AreaorBasin], ltbl_API_by_TABLE.API, [CompletionDate] FROM [Wells - Public Header Data] INNER JOIN ltbl_API_by_TABLE ON [Wells - Public Header Data].[API_WellNo] = ltbl_API_by_TABLE.API ORDER BY [WellName]+"" #""+[WellNumber];" 'Print #2, strSQLtype Set MyDB = CurrentDb Set MyRS = MyDB.OpenRecordset(strSQLtype) '****** Begin KML Code ******' Print #1, "" Print #1, "" Print #1, " " '****** KML Balloon Style ******' Print #1, " " Print #1, " " Print #1, " -149.5415" Print #1, " 62.8" Print #1, " 20.0" Print #1, " 1600000.0" Print #1, " " '****** Begin Inventory Loop by Wellname ******' With MyRS Do Until .EOF '****** Build Well Header for KML balloon Snippet ******' strSnippet = "API:  " & MyRS.Fields(3) & "
Well Permit:  " & MyRS.Fields(5) & "  -  Help
Operator:  " & MyRS.Fields(4) & "
Total Depth (ft):  " & MyRS.Fields(6) & "
Completion Date:  " & MyRS.Fields(9) & "
Area:  " & MyRS.Fields(7) & "" '****** Construct Well Header ******' Print #1, " " Print #1, " " Print #1, " " & strSnippet & "]]>" Print #1, " " Print #1, " Material Details LinkTopBottomCount" '******* Find Well Summary and HTML Switch Data *****' strSQL = "SELECT ltbl_API_by_Table.Source_Table, ltbl_API_by_Table.IntervalTop, ltbl_API_by_Table.IntervalBottom, ltbl_API_by_Table.Records, ltbl_Table_HTML_Switch.Table_Label, ltbl_Table_HTML_Switch.Publish_Flag FROM ltbl_API_by_Table INNER JOIN ltbl_Table_HTML_Switch ON ltbl_API_by_Table.SOURCE_TABLE = ltbl_Table_HTML_Switch.Table_Name WHERE (ltbl_API_by_Table.API) = """ & MyRS.Fields(8) & """ ORDER BY ltbl_API_by_Table.Source_Table;" 'Print #2, strSQL Set MyRS2 = MyDB.OpenRecordset(strSQL) With MyRS2 Do Until .EOF '******** Find GMC Data Reports ********' strHrefFile = strWebPath & LCase(Mid(MyRS2.Fields(0), 5)) & "/" & MyRS.Fields(8) & "_" & LCase(Mid(MyRS2.Fields(0), 5)) & ".html" 'Print #2, strHrefFile '******** Show only well materials with publish flag = true ********' If MyRS2.Fields(0) = "tbl_DATAREPORTINDEX" Then strReport = "Yes" ElseIf MyRS2.Fields(5) Then Print #1, " " & MyRS2.Fields(4) & "" & MyRS2.Fields(1) & "" & MyRS2.Fields(2) & "" & MyRS2.Fields(3) & "" End If .MoveNext Loop End With Print #1, " " '******** Find GMC Data Reports ********' If strReport = "Yes" Then strReport = "SELECT tbl_PUBLICATIONS_SOURCE_MAT.API, tbl_PUBLICATIONS.Title, tbl_PUBLICATIONS.Publication_Number, tbl_PUBLICATIONS.Url FROM tbl_PUBLICATIONS INNER JOIN tbl_PUBLICATIONS_SOURCE_MAT ON tbl_PUBLICATIONS.PubID=tbl_PUBLICATIONS_SOURCE_MAT.PubID WHERE API = '" & MyRS.Fields(8) & "' ORDER BY tbl_PUBLICATIONS.PubID;" Set MyRS3 = MyDB.OpenRecordset(strReport) With MyRS3 Print #1, "
" Print #1, " " Print #1, " " Print #1, "
GMC Data Report Title(s) for Well
" Print #1, "
" End If '******** GMC Footer ********' Print #1, "
GMC Oil and Gas Inventory Metadata
Reality Check
Alaska Geologic Materials Center
(907) 696-0079
" Print #1, " ]]>" '******** Plot Well Locations in KML ********' Print #1, " #OilGasLabel" Print #1, " " & MyRS.Fields(2) & "" & MyRS.Fields(1) & "1000020" Print #1, " clampToGround" & MyRS.Fields(2) & "," & MyRS.Fields(1) & ",0" Print #1, " " .MoveNext Loop End With '******** Close KML File ********' Print #1, " " Print #1, "" '******** Close Down Module ********' Close #1 Close #2 MyRS.Close Set MyDB = Nothing Set MyRS = Nothing Set MyRS2 = Nothing Set MyRS3 = Nothing '***** RUN BAT FILE TO ZIP THE KML AND RENAME TO KMZ ******' RetVal = Shell("F:\Databases\Bat\zip-oilgas-kml-public.bat", 1) End Sub