如何修改ArcGIS的字段属性，修改DBF文件；EXCEL修改ACCESS数据库的内容；用EXCEL修改ACCESS-锐单电子商城

ArcGIS字段属性的方法---如果是大量修改，更改前备份原始数据，因为以下三种修改方法基本相同，不可逆转，数据是黄金，多备份总是正确的。

1.如果将使用系统内部修改VB和python，如果数据量过大，系统内修改效率最高，可能仍在数据库中update句子效率高；

2、shp直接修改格式dbf记住：使用wps修改后可直接保存；MS office高版不支持直接保存，要吹wps，以前是个office2003年的重度用户不敢升级到2007年，现在发现了WPS完全支持office2003特别是EXCEL.

参考本文：

win10系统下如何修改？ArcGIS的dbf文件_登山猪金金华好-GIS的博客-CSDN博客_arcgis dbf文件

3、mdb格式，用access打开后修改(如果可以的话)update试着在网上找句子access查询器写在查询器里SQL语句改会方便一点），或者用excel链接access后修改；

以下是通过excel修改access总体思路：access 导出到excel 修改后导回access

尽量不要修改表结构，即不要增加行、删除行、增加列、删除列、更改数据安全性要高得多arcgis修改版本和数据结构，虽然现在有一个数据，shape存储矢量数据，改变属性应该不是问题，不要改变矢量，看看我VBA也有几列不去updata的；

sheet3.以下是基本设置，应设置前三行。告诉程序数据在哪里，操作表格

excel读取access内部的表格，修改并提交

查询数据=Private Sub C_select_Click()--- 把数据从access在数据库中找到更新数据=Private Sub C_update_Click()---在excel修改完成后，写回来access

导入核对= Private Sub C_check_Click() ---这个函数是检查写入是否正确，比较数据库中的数据和查询的数据，比较过程是重新查询数据sheet然后对两个sheet对比数据，如果标红不同。

原因不同，一是数据库字符串长度限制，二是数据字段属性，如数字，不能改为字符，更新失败；

具体如下excel数据和下载的区别在于:没有vba、没有图片，没有按钮，其他一样的，保存一个xml格式，然后使用WPS打开(复制到记事本中，然后另存为abc.xml,然后用wps打开）

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"><DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>缪伟光</Author><LastAuthor>User</LastAuthor><Created>2018-05-26T08:35:34Z</Created><LastSaved>2022-05-21T09:00:43Z</LastSaved><Company>Microsoft</Company></DocumentProperties><CustomDocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><KSOProductBuildVer dt:dt="string">2052-11.1.0.10132</KSOProductBuildVer></CustomDocumentProperties><ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"><WindowWidth>18600</WindowWidth><WindowHeight>6980</WindowHeight><ActiveSheet>2</ActiveSheet><ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook><Styles><Style ss:ID="s30" ss:Name="汇总"><Borders><Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3" ss:Color="#4F81BD"/><Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#4F81BD"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000" ss:Bold="1"/></Style><Style ss:ID="s8" ss:Name="千位分隔"><NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * &quot;-&quot;??_ ;_ @_ "/></Style><Style ss:ID="s25" ss:Name="计算"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/><Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/><Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/><Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FA7D00" ss:Bold="1"/><Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/></Style><Style ss:ID="s20" ss:Name="标题 2"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3" ss:Color="#A7BFDE"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="13" ss:Color="#1F497D" ss:Bold="1"/></Style><Style ss:ID="s16" ss:Name="警告文本"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FF0000"/></Style><Style ss:ID="s1" ss:Name="货币[0]"><NumberFormat ss:Format="_ &quot;￥&quot;* #,##0_ ;_ &quot;￥&quot;* \-#,##0_ ;_ &quot;￥&quot;* &quot;-&quot;_ ;_ @_ "/></Style><Style ss:ID="s7" ss:Name="差"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#9C0006"/><Interior ss:Color="#FFC7CE" ss:Pattern="Solid"/></Style><Style ss:ID="s26" ss:Name="检查单元格"><Borders><Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/><Border ss:Position="Left" ss:LineStyle="Double" ss:Weight="3" ss:Color=#3F3F3F"/><Border ss:Position="Right" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/><Border ss:Position="Top" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/><Interior ss:Color="#A5A5A5" ss:Pattern="Solid"/></Style><Style ss:ID="s24" ss:Name="输出"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/><Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/><Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/><Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#3F3F3F" ss:Bold="1"/><Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/></Style><Style ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Center"/><Borders/><Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/><Interior/><NumberFormat/><Protection/></Style><Style ss:ID="s5" ss:Name="千位分隔[0]"><NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * &quot;-&quot;_ ;_ @_ "/></Style><Style ss:ID="s34" ss:Name="强调文字颜色 1"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#4F81BD" ss:Pattern="Solid"/></Style><Style ss:ID="s4" ss:Name="货币"><NumberFormat ss:Format="_ &quot;￥&quot;* #,##0.00_ ;_ &quot;￥&quot;* \-#,##0.00_ ;_ &quot;￥&quot;* &quot;-&quot;??_ ;_ @_ "/></Style><Style ss:ID="s29" ss:Name="链接单元格"><Borders><Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3" ss:Color="#FF8001"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FA7D00"/></Style><Style ss:ID="s22" ss:Name="标题 3"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#95B3D7"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#1F497D" ss:Bold="1"/></Style><Style ss:ID="s17" ss:Name="标题"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Color="#1F497D" ss:Bold="1"/></Style><Style ss:ID="s10" ss:Name="超链接"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#0000FF" ss:Underline="Single"/></Style><Style ss:ID="s3" ss:Name="输入"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/><Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/><Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/><Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#3F3F76"/><Interior ss:Color="#FFCC99" ss:Pattern="Solid"/></Style><Style ss:ID="s41" ss:Name="20% - 强调文字颜色 4"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#E4DFEC" ss:Pattern="Solid"/></Style><Style ss:ID="s40" ss:Name="强调文字颜色 4"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#8064A2" ss:Pattern="Solid"/></Style><Style ss:ID="s6" ss:Name="40% - 强调文字颜色 3"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/></Style><Style ss:ID="s27" ss:Name="20% - 强调文字颜色 6"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#FDE9D9" ss:Pattern="Solid"/></Style><Style ss:ID="s2" ss:Name="20% - 强调文字颜色 3"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#EBF1DE" ss:Pattern="Solid"/></Style><Style ss:ID="s28" ss:Name="强调文字颜色 2"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#C0504D" ss:Pattern="Solid"/></Style><Style ss:ID="s9" ss:Name="60% - 强调文字颜色 3"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#C4D79B" ss:Pattern="Solid"/></Style><Style ss:ID="s36" ss:Name="40% - 强调文字颜色 1"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#B8CCE4" ss:Pattern="Solid"/></Style><Style ss:ID="s31" ss:Name="好"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#006100"/><Interior ss:Color="#C6EFCE" ss:Pattern="Solid"/></Style><Style ss:ID="s11" ss:Name="百分比"><NumberFormat ss:Format="0%"/></Style><Style ss:ID="s32" ss:Name="适中"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#9C6500"/><Interior ss:Color="#FFEB9C" ss:Pattern="Solid"/></Style><Style ss:ID="s18" ss:Name="解释性文本"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#7F7F7F" ss:Italic="1"/></Style><Style ss:ID="s15" ss:Name="标题 4"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#1F497D" ss:Bold="1"/></Style><Style ss:ID="s12" ss:Name="已访问的超链接"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#800080" ss:Underline="Single"/></Style><Style ss:ID="s35" ss:Name="20% - 强调文字颜色 1"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#DCE6F1" ss:Pattern="Solid"/></Style><Style ss:ID="s19" ss:Name="标题 1"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3" ss:Color="#4F81BD"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="15" ss:Color="#1F497D" ss:Bold="1"/></Style><Style ss:ID="s13" ss:Name="注释"><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/><Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/><Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/><Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/></Borders><Interior ss:Color="#FFFFCC" ss:Pattern="Solid"/></Style><Style ss:ID="s46" ss:Name="强调文字颜色 6"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#F79646" ss:Pattern="Solid"/></Style><Style ss:ID="s23" ss:Name="60% - 强调文字颜色 4"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#B1A0C7" ss:Pattern="Solid"/></Style><Style ss:ID="s14" ss:Name="60% - 强调文字颜色 2"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#DA9694" ss:Pattern="Solid"/></Style><Style ss:ID="s45" ss:Name="60% - 强调文字颜色 5"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#92CDDC" ss:Pattern="Solid"/></Style><Style ss:ID="s43" ss:Name="强调文字颜色 5"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#4BACC6" ss:Pattern="Solid"/></Style><Style ss:ID="s21" ss:Name="60% - 强调文字颜色 1"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#95B3D7" ss:Pattern="Solid"/></Style><Style ss:ID="s48" ss:Name="60% - 强调文字颜色 6"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#FABF8F" ss:Pattern="Solid"/></Style><Style ss:ID="s44" ss:Name="40% - 强调文字颜色 5"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#B7DEE8" ss:Pattern="Solid"/></Style><Style ss:ID="s33" ss:Name="20% - 强调文字颜色 5"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#DAEEF3" ss:Pattern="Solid"/></Style><Style ss:ID="s38" ss:Name="40% - 强调文字颜色 2"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/></Style><Style ss:ID="s37" ss:Name="20% - 强调文字颜色 2"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#F2DCDB" ss:Pattern="Solid"/></Style><Style ss:ID="s47" ss:Name="40% - 强调文字颜色 6"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#FCD5B4" ss:Pattern="Solid"/></Style><Style ss:ID="s42" ss:Name="40% - 强调文字颜色 4"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#CCC0DA" ss:Pattern="Solid"/></Style><Style ss:ID="s39" ss:Name="强调文字颜色 3"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#FFFFFF"/><Interior ss:Color="#9BBB59" ss:Pattern="Solid"/></Style><Style ss:ID="s49"/><Style ss:ID="s50"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/></Style><Style ss:ID="s51"><NumberFormat ss:Format="yyyy&quot;年&quot;m&quot;月&quot;d&quot;日&quot;"/></Style><Style ss:ID="s52"><Interior ss:Color="#FF0000" ss:Pattern="Solid"/></Style><Style ss:ID="s53"><Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#FF0000"/></Style></Styles><Worksheet ss:Name="更新数据"><Names><NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=更新数据!#REF!" ss:Hidden="1"/></Names><Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="11" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="15"><Column ss:Index="9" ss:StyleID="Default" ss:Width="76" ss:Span="1"/><Row><Cell><Data ss:Type="String">OBJECTID_1</Data></Cell><Cell><Data ss:Type="String">Shape</Data></Cell><Cell><Data ss:Type="String">OBJECTID</Data></Cell><Cell><Data ss:Type="String">TBBH</Data></Cell><Cell><Data ss:Type="String">DLMC</Data></Cell><Cell><Data ss:Type="String">ZLDWMC</Data></Cell><Cell><Data ss:Type="String">XMMC</Data></Cell><Cell><Data ss:Type="String">Shape_Leng</Data></Cell><Cell><Data ss:Type="String">Shape_Length</Data></Cell><Cell><Data ss:Type="String">Shape_Area</Data></Cell></Row><Row><Cell><Data ss:Type="Number">1</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell ss:StyleID="s52"><Data ss:Type="Number">1</Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String">甲乙</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">1107.72920863102</Data></Cell><Cell><Data ss:Type="Number">62563.1291520801</Data></Cell></Row><Row><Cell><Data ss:Type="Number">2</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell ss:StyleID="s52"><Data ss:Type="Number">2</Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String">sfef</Data></Cell><Cell><Data ss:Type="String">不能打</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">480.349638353638</Data></Cell><Cell><Data ss:Type="Number">10920.7207650812</Data></Cell></Row><Row><Cell><Data ss:Type="Number">3</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell ss:StyleID="s52"><Data ss:Type="Number">3</Data></Cell><Cell><Data ss:Type="String">电放费</Data></Cell><Cell><Data ss:Type="String">dd</Data></Cell><Cell><Data ss:Type="String">纷纷</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">854.163515962925</Data></Cell><Cell><Data ss:Type="Number">37042.9448216153</Data></Cell></Row><Row><Cell><Data ss:Type="Number">4</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell ss:StyleID="s52"><Data ss:Type="Number">4</Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String">得分</Data></Cell><Cell><Data ss:Type="String">纷纷</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">1198.78632664642</Data></Cell><Cell><Data ss:Type="Number">73417.3455450288</Data></Cell></Row><Row ss:Index="11"><Cell ss:Index="6" ss:StyleID="s53"/></Row></Table><WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"><PageSetup><Header x:Margin="0.3"/><Footer x:Margin="0.3"/><PageMargins x:Left="0.7" x:Right="0.7" x:Top="0.75" x:Bottom="0.75"/></PageSetup><Print><ValidPrinterInfo/><PaperSizeIndex>9</PaperSizeIndex><HorizontalResolution>600</HorizontalResolution><VerticalResolution>600</VerticalResolution></Print><TopRowVisible>0</TopRowVisible><LeftColumnVisible>0</LeftColumnVisible><Panes><Pane><Number>3</Number><ActiveRow>4</ActiveRow><ActiveCol>3</ActiveCol><RangeSelection>R5C4</RangeSelection></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet><Worksheet ss:Name="导入数据后"><Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="15"><Column ss:Index="9" ss:StyleID="Default" ss:Width="76" ss:Span="1"/><Row><Cell><Data ss:Type="String">OBJECTID_1</Data></Cell><Cell><Data ss:Type="String">Shape</Data></Cell><Cell><Data ss:Type="String">OBJECTID</Data></Cell><Cell><Data ss:Type="String">TBBH</Data></Cell><Cell><Data ss:Type="String">DLMC</Data></Cell><Cell><Data ss:Type="String">ZLDWMC</Data></Cell><Cell><Data ss:Type="String">XMMC</Data></Cell><Cell><Data ss:Type="String">Shape_Leng</Data></Cell><Cell><Data ss:Type="String">Shape_Length</Data></Cell><Cell><Data ss:Type="String">Shape_Area</Data></Cell></Row><Row><Cell><Data ss:Type="Number">1</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="String">1</Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String">甲乙</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">1107.72920863102</Data></Cell><Cell><Data ss:Type="Number">62563.1291520801</Data></Cell></Row><Row><Cell><Data ss:Type="Number">2</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="String">2</Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String">sfef</Data></Cell><Cell><Data ss:Type="String">不能打</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">480.349638353638</Data></Cell><Cell><Data ss:Type="Number">10920.7207650812</Data></Cell></Row><Row><Cell><Data ss:Type="Number">3</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="String">3</Data></Cell><Cell><Data ss:Type="String">电放费</Data></Cell><Cell><Data ss:Type="String">dd</Data></Cell><Cell><Data ss:Type="String">纷纷</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">854.163515962925</Data></Cell><Cell><Data ss:Type="Number">37042.9448216153</Data></Cell></Row><Row><Cell><Data ss:Type="Number">4</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="String">4</Data></Cell><Cell><Data ss:Type="String"> </Data></Cell><Cell><Data ss:Type="String">得分</Data></Cell><Cell><Data ss:Type="String">纷纷</Data></Cell><Cell><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="Number">1198.78632664642</Data></Cell><Cell><Data ss:Type="Number">73417.3455450288</Data></Cell></Row></Table><WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"><PageSetup><Header x:Margin="0.3"/><Footer x:Margin="0.3"/><PageMargins x:Left="0.7" x:Right="0.7" x:Top="0.75" x:Bottom="0.75"/></PageSetup><TopRowVisible>3</TopRowVisible><LeftColumnVisible>0</LeftColumnVisible><Panes><Pane><Number>3</Number><ActiveRow>5</ActiveRow><ActiveCol>3</ActiveCol><RangeSelection>R6C4</RangeSelection></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet><Worksheet ss:Name="基础设置"><Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="16" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="15"><Column ss:Index="1" ss:StyleID="s49" ss:AutoFitWidth="0" ss:Width="119.5"/><Column ss:Index="2" ss:StyleID="s49" ss:AutoFitWidth="0" ss:Width="264.5"/><Row><Cell><Data ss:Type="String">数据库地址：</Data></Cell><Cell><Data ss:Type="String">D:\testdb.mdb</Data></Cell><Cell><Data ss:Type="String">说明：绝对路径</Data></Cell></Row><Row><Cell><Data ss:Type="String">查询表名称</Data></Cell><Cell ss:StyleID="s50"><Data ss:Type="String">test1</Data></Cell></Row><Row><Cell ss:StyleID="s50"><Data ss:Type="String">设置主键</Data></Cell><Cell ss:StyleID="s50"><Data ss:Type="String">OBJECTID_1</Data></Cell></Row><Row><Cell><Data ss:Type="String">注意事项：OBJECTID千万不能修改；第一行的名称不能修改，可以删除多列除了objectid这列，可以删除任意行除了第一行</Data></Cell></Row><Row><Cell ss:StyleID="s50"><Data ss:Type="String">核对导入用于核对是否导入完整，有时候文本列，只输入数字，不导入，或者数字列输入文本不导入</Data></Cell></Row><Row ss:Index="9"><Cell><Data ss:Type="String">修改日志</Data></Cell></Row><Row><Cell><Data ss:Type="Number">20191210</Data></Cell><Cell><Data ss:Type="String">基本可用</Data></Cell></Row><Row><Cell><Data ss:Type="Number">20200925</Data></Cell><Cell ss:StyleID="s50"><Data ss:Type="String">修改主键，查询前先看表格是否存在</Data></Cell><Cell ss:StyleID="s50"/></Row><Row><Cell ss:StyleID="s51"><Data ss:Type="Number">44702</Data></Cell><Cell ss:StyleID="s50"><Data ss:Type="String">3076报错，http://www.itmop.com/downinfo/445356.html，下载并安装</Data></Cell><Cell ss:StyleID="s50"/></Row><Row><Cell ss:Index="3" ss:StyleID="s50"/></Row><Row><Cell ss:Index="3" ss:StyleID="s50"><Data ss:Type="String">打开属性表</Data></Cell><Cell ss:Index="9" ss:StyleID="s50"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">OBJECTID_1=主键</ss:Data></Cell></Row><Row><Cell ss:Index="3" ss:StyleID="s50"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">OBJECTID_12是主键</ss:Data></Cell></Row><Row><Cell ss:Index="3" ss:StyleID="s50"><Data ss:Type="String">一般带*的是主键</Data></Cell></Row></Table><WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"><PageSetup><Header x:Margin="0.3"/><Footer x:Margin="0.3"/><PageMargins x:Left="0.7" x:Right="0.7" x:Top="0.75" x:Bottom="0.75"/></PageSetup><Print><ValidPrinterInfo/><PaperSizeIndex>9</PaperSizeIndex><HorizontalResolution>600</HorizontalResolution><VerticalResolution>600</VerticalResolution></Print><Selected/><TopRowVisible>0</TopRowVisible><LeftColumnVisible>0</LeftColumnVisible><Panes><Pane><Number>3</Number><ActiveRow>8</ActiveRow><ActiveCol>1</ActiveCol><RangeSelection>R9C2</RangeSelection></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet></Workbook>

下面是具体的VBA内容

放在sheet1里边就行

Option Explicit
Sub 数据库连接()
'告诉电脑，我们要有ado，引用ado
'创建连接对象
'给对象取名字
Dim con As ADODB.Connection '声明对象变量
'创建对象变量并赋值
Set con = New ADODB.Connection
'建立数据库的连接
'dim con as new adodb.connection
'con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb"
'MsgBox "连接成功"
With con
.Provider = "microsoft.ace.oledb.12.0"
.ConnectionString = ThisWorkbook.Path & "\miaotest.mdb"
.Open

End With
MsgBox "连接成功-数据库连接1"
End Sub
Sub 插入记录()
Dim con As ADODB.Connection '声明对象变量
'创建对象变量并赋值
Set con = New ADODB.Connection
'建立数据库的连接
With con
.Provider = "microsoft.ace.oledb.12.0"
.ConnectionString = ThisWorkbook.Path & "\miaotest.mdb"
.Open

End With
MsgBox "连接成功-插入"

'insert into 表名（列1，列2）values（值1，值2，值3）
Dim sql As String
sql = "insert into 院系(院系编号,院系名,电话) values('A09','人文学院','9999')"
con.Execute (sql)
'释放空间变量
con.Close '关闭连接
Set con = Nothing '释放空间


End Sub

Sub 删除记录()
Dim con As New ADODB.Connection
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\miaotest.mdb"
'Dim sql As String
'sql = "delete from 院系 where 院系编号=''"
Dim sql As String
'sql = "update 院系 set 电话='' where 院系名 =''"
Dim str As String
str = InputBox("输入性别", "提示")
sql = "update 学生 set 班级='2班' where 性别='" & str & " '"



con.Execute (sql)
con.Close '关闭连接
Set con = Nothing '释放空间

End Sub
Sub 简单查询()
Dim con As New ADODB.Connection
Dim str As String
str = Sheet2.Cells(1, 2) ' ThisWorkbook.Path & "\miaotest.mdb"

con.Open "provider=microsoft.ace.oledb.12.0;data source=" & str
Dim sql, stable As String
stable = Sheet2.Cells(2, 2)
sql = "select * from " & stable
'获取记录集
Dim rs As New ADODB.Recordset
Set rs = con.Execute(sql)
'获取字段名
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1) = rs.Fields(i).Name
Next
'将记录集rs的记录返回到工作表中

Range("A2").CopyFromRecordset rs
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing





End Sub

Sub 更新表()
Dim con As New ADODB.Connection
Dim stable1, stable2, ssql, scl As String
Dim i, j As Integer
Dim sql, sql1, sqlfield, stable As String
Dim str, sa1, sa2, zhujian As String
checktable
str = Sheet2.Cells(1, 2) ' ThisWorkbook.Path & "\miaotest.mdb"
stable = Sheet2.Cells(2, 2)
zhujian = Sheet2.Cells(3, 2)
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & str


'select  * into abcd  from abc where 1<>1
sqlfield = ""
'  For i = 1 To Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column - 1
j = Cells(1, Columns.Count).End(xlToLeft).Column
   For i = 1 To j
   If Sheet1.Cells(1, i) <> "" Then
sqlfield = sqlfield + "," + Cells(1, i)

 Else: Exit For
 
  End If
Next
  sqlfield = Right(sqlfield, Len(sqlfield) - 1)


sql = "select " & sqlfield & "  into " & stable & "_173   from  " & stable & " where 1<>1"
  Debug.Print sql
con.Execute (sql)

'插入数据
    sql1 = "INSERT INTO " & stable & "_173" & " SELECT * FROM [Excel 8.0;Database=" _
        & ThisWorkbook.FullName & ";].[" & ActiveSheet.Name & "$" & Range("A1").CurrentRegion.Address(0, 0) & "]"
          Debug.Print sql1
   con.Execute (sql1)
          
sql = "select * from " & stable & "_173"
Dim rs As New ADODB.Recordset
Set rs = con.Execute(sql)



    stable1 = stable
    stable2 = stable & "_173"
    sa1 = zhujian '"OBJECTID"
    sa2 = zhujian '"OBJECTID"
    scl = ""
'    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & stable1)
    For i = 0 To rs.Fields.Count - 1
    If rs.Fields(i).Name <> "Shape" And rs.Fields(i).Name <> sa1 Then
    scl = scl & stable1 & "." & rs.Fields(i).Name & "=" & stable2 & "." & rs.Fields(i).Name & ","
        End If
    Next
     scl = Left(scl, Len(scl) - 1)
     
    ssql = "update " & stable1 & "," & stable2 & " set " & scl & " where " & stable1 & "." & sa1 & "=" & stable2 & "." & sa2
    
    Debug.Print ssql
     con.Execute (ssql)
     
 rs.Close: Set rs = Nothing
con.Close: Set con = Nothing

con.Open "provider=microsoft.ace.oledb.12.0;data source=" & str
Sheet3.UsedRange.ClearContents
stable = Sheet2.Cells(2, 2)
sql = "select * from " & stable2
'获取记录集
Set rs = con.Execute(sql)
'获取字段名
For i = 0 To rs.Fields.Count - 1
Sheet3.Cells(1, i + 1) = rs.Fields(i).Name
Next
'将记录集rs的记录返回到工作表中

Sheet3.Range("A2").CopyFromRecordset rs
  rs.Close: Set rs = Nothing
con.Close: Set con = Nothing

con.Open "provider=microsoft.ace.oledb.12.0;data source=" & str
   
sql = "drop table " & stable & "_173"
  '  Debug.Print sql
 con.Execute (sql)
con.Close: Set con = Nothing

 MsgBox "数据更新完毕"

End Sub

Sub checktable()  '检查数据表是否存在()
    Dim mydata As String
    Dim mytable, sql As String
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    mydata = Sheet2.Cells(1, 2) ' ThisWorkbook.Path & "\miaotest.mdb"
mytable = Sheet2.Cells(2, 2) & "_173"
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .Open mydata
        
    End With
    '利用connection对象的openschema方法产生数据表记录集
    Set rs = con.OpenSchema(adSchemaTables)
    '利用循环查询判断是否存在该数据表
'    Do While Not rs.EOF
'    'rs!table_name=rs("table_name")
'        If LCase(rs!table_name) = LCase(mytable) Then
'             MsgBox "数据表《" & mytable & "》存在"
'             GoTo hhh
'        End If
'        rs.MoveNext
'
'    Loop
'     MsgBox "数据表《" & mytable & "》不存在"
    
    '利用recordset 对象的find方法查找数据表并判断是否存在
    'find 方法会直接将光标定位到找的记录，如果没有找的，则将光标移动到eof
    
    rs.Find "table_name='" & mytable & "'"
    If rs.EOF Then
 
    Else
       
        
        sql = "drop table " & mytable
   'Debug.Print sql
con.Execute (sql)

    End If
hhh:
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
    
    
End Sub


Sub 错误捕捉()
    Dim mydata As String
    Dim mytable As String
    Dim con As New ADODB.Connection
  mydata = Sheet2.Cells(1, 2) ' ThisWorkbook.Path & "\miaotest.mdb"
mytable = Sheet2.Cells(2, 2) & "_173"
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .Open mydata
    End With
    On Error Resume Next '遇到错误，继续往下执行
    '删除数据表
    con.Execute "drop table " & mytable
    If Err.Number <> 0 Then
         MsgBox Err.Description
    Else
        MsgBox "该表存在"
    End If
    con.Close
    Set con = Nothing
    
End Sub


Private Sub C_select_Click()
Sheet1.UsedRange.ClearContents
 简单查询
End Sub

Private Sub C_update_Click()
C_select.Visible = False
更新表

End Sub


Private Sub C_check_Click()
Dim con As New ADODB.Connection
Dim stable1, stable2, ssql, scl As String
Dim i, j, il, cl As Integer
Dim sql, sql1, sqlfield, stable As String
Dim str, sa1, sa2 As String

il = Cells(Rows.Count, 1).End(xlUp).Row
cl = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To il
   For j = 1 To cl
   If Sheet1.Cells(i, j) <> Sheet3.Cells(i, j) Then
  Sheet1.Cells(i, j).Interior.ColorIndex = 3
  Else
'  Sheet1.Cells(i, j).Font.ColorIndex = 3
 Sheet1.Cells(i, j).Interior.Pattern = xlNone
   End If
   
   Next
   Next
   

End Sub


Private Sub Worksheet_Activate()
   Sheet1.C_select.Visible = True
End Sub

下面这段短的放在thisworkbook中 --就是防止导出误点，改着改着又点一次导出就覆盖刚才修改的内容；所以设置一下，点导出后，把导出按钮隐藏；再次打开这个表格就又看到了；


Private Sub Workbook_open()
   Sheet1.C_select.Visible = True
End Sub

具体操作很简单，第一导出数据点一下，第二自行修改，第三导入数据点一下，然后点一下核对数据看看有没有红色的，如果有红色的，那就自己找找原因修改一下。改access数据前建议先备份一下，毕竟这个过程不可逆。

资讯详情

如何修改ArcGIS的字段属性，修改DBF文件；EXCEL修改ACCESS数据库的内容；用EXCEL修改ACCESS

ArcGIS字段属性的方法---如果是大量修改，更改前备份原始数据，因为以下三种修改方法基本相同，不可逆转，数据是黄金，多备份总是正确的。

动力学技术KTU1121 USB Type-C 端口保护器的介绍、特性、及应用

如何修改ArcGIS的字段属性，修改DBF文件；EXCEL修改ACCESS数据库的内容；用EXCEL修改ACCESS

ArcGIS字段属性的方法---如果是大量修改，更改前备份原始数据，因为以下三种修改方法基本相同，不可逆转，数据是黄金，多备份总是正确的。

动力学技术KTU1121 USB Type-C 端口保护器的介绍、特性、及应用

最近热搜

历史搜索 清除历史记录

历史搜索清除历史记录