我的EXCEl vba后学ACCESSVBA的经历

lichaobin

我的EXCEl vba后学ACCESSVBA的经历
ＥＸＣＥＬVBA深入了以后，发现其数据处理是多么的便利．越学自己知道所学的太少了．
在您发现ＥＸＣＥＬ代码编程器中工具菜单有一引用的加载项的时候．这么多的引用足够让你迷失向了．
我是这么一路走过来的：
完函数-数组函数-ＶＢＡ－引用中所涉的东东（ＡＤＯ＼ＤＡＯ＼DICTIONARY等用法)后,就到ＡＣＣＥＳＳ了．

如果你要学ＡＣＣ，那么，我建议你先从系统的罗斯文数据库开始学起．
不要买书了，那例子就是最好的教材．
等你了解了ＡＣＣＥＳＳ罗斯文数据库时，你才算开始懂基本概念．
不卖关子，想当初我从来没有想过用ＡＣＣＥＳＳ的．
但为什么后来又学了它呢？
大家知道，ＥＸＣＥＬ数据处理即见即得，很是方便．也形象理解．但它也有短处啊．
一个表才６５５３６行，数据量大的时候就吃不消了．（别拿０７来说）．所以才考虑了它的兄弟ＡＣＣＥＳＳ．
初次用时，觉得ＡＣＣ太呆板了，真受不了．一打开数据库都是菜单操作．这也受限，那也受限．模块乱七八横的．
没有耐心真的学不好ＡＣＣＥＳＳ的．恨不得把它给干掉．
那为什么那么恨它还要学它用它呢？呵呵，因为你知道ＥＸＣＥＬ的缺点之后，正好是ＡＣＣＥＳＳ的优点．
如：它存储容量大，查询快．不需要编程模块化宏或控件操作等．呵呵，因为你不了解它，所以你恨它．
即然一心向道，那么，我现在就道道了．．．
我们在ＥＸＣＥＬ表中知道：
１、数据清单（即表）、字段（表头或表标题）、字段类型、筛选、排序、函数、索引、application对象等等。
其实在ＡＣＣＥＳＳ里也大都是一样的。只是增加的不少的概念。
明显区别比如：关系、联合、ＳＱＬ、查询表、追加、主键、它的单元格操作更严谨。
　　　它的模块作用：表、查询、窗体、宏、代码编辑器、分组。菜单等
　　　有了这些基本概念认识后，你还需纠正以下想法：
　　　没学会爬别想走（没弄清基本概念就想像ＥＸＣＥＬ一样用ＶＢＡ编程来控制ＡＣＣ了。
　　　了解表、查询、窗体、宏、代码编辑器的作用及特点。以便以后想干什么在哪用。
　　　复习一下控件用法，ＡＣＣ大多不需编程，但用得最多就是控件了。主要是了解其常用事件及值(value,item,text)
           控件的数据源，查学一下ＳＱＬ来查询了。ＳＥＬＥＣＴ。。。
　　　别以为ＡＣＣ的宏是录的，你得多花时间了解一下它的常用方法。
　　　最后，你得找个突破口入门了。
　　　要想用ＡＣＣ，则必需了解怎么打开它，它属于哪个对象。
　　　库文件：ＤＢ，当前库文件，currentdb   ,它从属于access.application
          表：table  tabledef     查询表 querydef
         查询：query
         宏是什么，
　　代码是什么，从哪对象引用来的。
    　　
　　说到突破口了吧，呵呵，你现在知道打开数据库，但你得知道怎么放和用数据呀。
　　下面，就拿导入表及链接表作为入口的突破口来主要讲述吧。
　　在打开数据库后，菜单－获取外部数据-导入或链接表.　　
嘿嘿，真有的讲呢。先从手工导入讲起。　[local]1[/local]

[ 本帖最后由 lichaobin 于 2009-3-23 10:24 编辑 ]

lichaobin

何时使用导入或联接

　　在Access中，可以很方便地从外部数据库中获取数据，这些数据库包括现在所有的各种主流数据库，像Dbase、Sybase、Oracle、Foxpro等。这样你就不用去一个一个地重新输入原来的数据了。 在Access中，可以用“获取外部数据”命令来获取外部数据库的数据，这个命令包括两种方式“导入”和“链接表”。对于不同的外部数据库可以用不同的获取方式。而且在Access中，不光可以获取外部数据，还可以导出别的数据库，就是将用Access建立的数据库保存为别的数据库形式，像FOXPRO，DBASE数据库等。

　　单击“文件”菜单中的“获取外部数据”项，弹出一个子菜单，上面有“导入”和“链接表”两个选项。我们单击其中的一个选项就可以实现导入或联接一个外部的数据库。虽然这两个命令的功用都是打开外部的一个数据库，并从中获取需要的数据，但在使用上，还是有差别的。

　　在我们使用“导入”这个方法来获取外部数据时需要注意几个条件。
一、需要我们打开的数据库文件相对来说比较小，而且不会被其他数据库应用程序的用　　户频繁更改。
二、不需要与其他数据库应用程序的用户共享数据。
三、你正在替换以前的数据库应用程序，而且不再需要以前的数据格式。
四、在使用其他数据库中的数据时，需要最佳性能。

　　使用“链接表”方法来获取外部数据时也需要注意几个条件：
一、需要打开的文件比较大，甚至比本地Access数据库的最大容量（1GB）还要大。
二、这个文件经常被其他数据库应用程序的用户更改。
三、必须在网络上与其他数据库应用程序的用户共享这个文件。
四、需要把应用程序分布到几个单独的用户那里，而且，你可能要不断更新开发的应用程序接口，将“应用程序”与数据分开可以更容易地更新应用程序，而不会干扰用户积累的数据。

　　 这就是为什么要在“获取外部数据”这个过程当中设置两种方法。如果要用的数据库并不大，但每天都可能要修改，而且由几个人来修改不同的部分，对于这种情况，最重要的是每天都有新的数据要处理，而单纯的将表导入进来，由于在Access中开发新的系统需要一定的时间，在这段时间内就有可能有新的数据输入进来。开始导入的数据就有很多可能已经更新了。所以这时使用“链接表”就不会出现这种问题，干扰到别的用户积累的数据。当然在Access中执行现在这台机器上的数据库文件是最快的，使用链接常常会因为很多原因使你的工作变得很慢，这点是你必须考虑的。

lichaobin

哈，原来导入的字段类型，分隔符，宽度都是可能在菜单里具体定义的。而且规格文件还可以保存待下次手工再导就不用设了。。。多方便。
这么好用的功能，大家都会想，它的ＶＢＡ代码的导入方法是什么呢？如果用遍历来那不是可以批量导入了。。。那真是帅呆了。。。。呵呵。
以下我的笔记：
我是否可以将Excel数据导入?
可以，手动方式：
打开数据库，或切换到打开数据库的“数据库”窗口
若要导入电子表格，请在“文件”菜单上，指向“获取外部数据”，然后单击“导入”。
若要链接电子表格，请在“文件”菜单上，指向“获取外部数据”，然后单击“链接表”。
在“导入”（或“链接”）对话框中，选择“文件类型”框中的 “Microsoft Excel (*.xls)”。
单击“查找范围”框右侧的箭头，选定电子表格文件所在的驱动器和文件夹，然后双击其图标。
若要导入这些文件，首先必须将每个工作表保存为一个单独的文件。且电子表格列标题与表字段名相匹配，才可以在已有表上追加数据。
请注意，上面文章中其实仍然包含了两种做法和一种扩展做法：
　　1、直接用导入
　　2、用链接表
　　3、当你只是要导入EXCEL数据到现有的表中时，可以先链接某个xls文件，然后直接用 jet sql语句来做：
       insert into [Access表名] (field1,field2) select field1,field2 from [xls链接表]

[ 本帖最后由 lichaobin 于 2009-3-23 02:47 编辑 ]

lichaobin

编程方式：
TransferSpreadsheet方法与transfertext方法有相似之处：
导入导出数据库方式：
文本transfertext方法：
       DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName,CodePage)

表格TransferSpreadsheet方法
DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
执行命令.TransferSpreadsheet(TransferType：=acImport默认方式 或 acLink链接 方式导入导出, SpreadsheetType：=导入导出EXCEL表类型),TableName：=Access表名,FileName：=EXCEL表名，HasFieldNames：=-1为一行作表标题,0则不作,Range:=导入表格区域)

SpreadsheetType、SpecificationName 前者为EXCEL版本类型，后者即创建导入导出规格的分隔格式文件名称，一般同夹使用。
                                   如：schema.ini 文件为系统默认固度宽度文件.如该参数留空，则取默认的导入/导出规格
在导文本中，特别留意其SpecificationName参数．
schema.ini 文件为系统默认文本导入规格文件．不过这个文件不知道怎么查看，在导入文本文件中有指引学习与修改．

更简单的是自己录一个规格文件，即外部数据源-导入表-高级－规格文件保存．该规格表在本数据库里，它隐藏了，只要打开选项，将
去掉勾在表模块中就可以看到规格表了．即（MSysIMEXcolumns、MSysIMEXspecs).


例：TransferType常量AcTextTransferType如下：
ac为常量类型，Ex为出，Im为入.link为链接型形式
Link为链接，Delim定义分隔符,Fixedl固定宽度,Merge为Word for windows合并文件
导出分隔符      导入分隔符              链接表分隔符
acExportDelim        acImportDelim 默认        acLinkDelim
导出固宽符      导入固宽符              链接表固宽符  
acExportFixed        acImportFixed                acLinkFixed
导出 HTML       导入HTML                链接 HTML   
acExportHTML        acImportHTML                acLinkHTML
导出WORD格式
acExportMerge               

其实他们多是是可选参数,有默认值的：只用这个就行：
DoCmd.TransferSpreadsheet , ,"Employees","C:\1.xls", True, "A1:G12"
上面示例1.xls指定范围内将表导入到access的表“Employees”中，并用电子表格中的第一行作为字段名。
再例：
                          TransferType， SpreadsheetType，       TableName，  FileName，HasFieldNames，Range   
DoCmd.TransferSpreadsheet   acImport,   acSpreadsheetTypeExcel9,   "juan",   "c:\d.xls",   True,      "sheet！"
                          默认方式导入,   Excel版本类型,         "access表名", "xls薄名",是否有行标题,"工作表！"


导入表中的另类方法：
如果一个工作薄里有多个表要导入，也可以使用如下方法：
一个按钮导入四个工作表到数据库：
例子：
假设已经建好数据库myAccessTable那么：
以下为插入指定记录的代码:(有表头)
insert into myAccessTable
select * from [EXCEL 8.0;DATABASE=C:\info.XLS].[sheet1]
union all
select * from [EXCEL 8.0;DATABASE=C:\info.XLS].[sheet2]
union all
select * from [EXCEL 8.0;DATABASE=C:\info.XLS].[sheet3]
union all
select * from [EXCEL 8.0;DATABASE=C:\info.XLS].[sheet4]

[ 本帖最后由 lichaobin 于 2009-3-23 03:09 编辑 ]

lichaobin

在导文本中，特别留意其SpecificationName参数．
schema.ini 文件为系统默认文本导入规格文件．不过这个文件不知道怎么查看，在导入文本文件中有指引学习与修改．

更简单的是自己录一个规格文件，即外部数据源-导入表-高级－规格文件保存．该规格表在本数据库里，它隐藏了，只要打开选项，将
去掉勾在表模块中就可以看到规格表了．即（MSysIMEXcolumns、MSysIMEXspecs).

一种方法：有了手工保存的规格文件，在代码文件里直接调用它。
另一种方法：用 Schema.ini 文件也可以达到要求。

作用：Schema.ini用于提供文本文件中记录的构架信息。每个 Schema.ini 项都用于指定表的五个特征之一：
1、文本文件名
2、文件格式
3、字段名、字段长度、字段类型
4、字符集
5、特别数据类型转换

指定文件名
文件名要用方括号括起来，例如如果要对 Sample.txt 使用数据构架信息文件，那么它的对应的项应该是
[Sample.txt]

指定文件格式
格式说明 表格式 Schema.ini 格式描述
Tab 制表符分隔 文件中的字段用制表符分隔 Format=TabDelimited
CSV 分隔 文件中的字段用逗号来分隔 Format=CSVDelimited
自定义分隔 文件中的字段可以用任何字符来分隔，所有的字符都可以用来分隔，包括空格，但是双引号 ( " ) 除外 Format=Delimited(自定义分隔符)

- 或者没有分隔符 -

Format=Delimited( )

固定宽度 文件中的字段为固定长度 cg1留言，经测试，参数为：Format=FixedLength



指定字段

你可以有两种方法在一个字符分隔的文本文件中指定字段名

1、在文本文件中的第一行包含字段名，并且设置 ColNameHeader 为 True 。
2、用数字编号指定每一列并且指定每一列的名字以及数据类型

你必须用数字编号指定每一列并且指定每一列的名字、数据类型以及长度（在固定长度分隔的文本文件中需要指定长度）

注意，设定了 ColNameHeader 选项，在 Schema.ini 中 Windows 注册时会忽略 FirstRowHasNames 选项。

你也可以指定字段的数据类型，使用 MaxScanRows 选项用来指定在确定列的数据类型时要扫描多少行数据。设置 MaxScanRows 为 0 将扫描整个文件。

如果文本文件第一行包含字段名，并且要扫描整个文件，改项目就要定义如下：
ColNameHeader=True
MaxScanRows=0


接下来的项目用来指定表中的字段，使用列编号(Coln)选项来指定列。字段长度在“固定分隔文本文件中”是必填项目，在“字符分隔文本文件”中是可选项目。
示例：定义 2 个字段，CustomerNumber 是长度为 10 的文本字段、CustomerName 是长度为 30 的文本字段。
Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30


语法如下：
Coln=ColumnName type [Width #]

参数解释如下：
参数 说明
ColumnName 文本，标识字段名，如果包含空格要用双引号括起来
type 数据类型包括：

Microsoft Jet 数据类型：Bit Byte Short Long Currency Single Double DateTime Text Memo

ODBC 数据类型： Char (same as Text) Float (same as Double) Integer (same as Short) LongChar (same as Memo) Date date format

其中date format 是日期的格式字符串例如：Date YYYY-MM-DD

Width 字符串的长度，后面的数字用来指定字段的长度（“固定分隔文本文件”为必填，“文字分隔文本文件”为可选）
# 整形数字，标识字段长度


指定字符集
CharacterSet 项有两个选择：ANSI | OEM
选择 ANSI 字符集用如下方法：
CharacterSet=ANSI

lichaobin

下面给出一个简单的例子，假设有一个表Contacts.txt类似下面：
姓名 单位 联系日期
王海 上海有机化学研究所 2002-1-1
罗炙 数字化机床研究院 2004-1-1  


导入 Access 应该类似下面表格：
姓名 单位 联系日期
王海 上海有机化学研究所 2002-1-1
罗炙 数字化机床研究院 2004-1-1



那么 Schema.ini 则是类似下面的INI文件：
[Contacts.txt]
ColNameHeader=True
format=Delimited(" ")
MaxScanRows=0
CharacterSet=ANSI
Col1="姓名" Char Width 10
Col2="单位" Char Width 9
Col3="联系日期" Date Width 8


注释如下：
[Contacts.txt] ///文本文件名
ColNameHeader=True ///带有表头
format=Delimited( ) ///空格作为分隔符，如果是分号，请用format=Delimited(;) 来解决
MaxScanRows=0 ///扫描整个文件
CharacterSet=ANSI ///ANSI 字符集
Col1="姓名" Char Width 10 ///字段1
Col2="单位" Char Width 9 ///字段2
Col3="联系日期" Date Width 8 ///字段3
///如果有更多字段可 Col4 .... ColN  


注意，Schema.ini 必须和需要导入的文本文件在同一目录。

此后，我们就可以利用下面的语句来导入数据了：

CurrentProject.Connection.Execute "SELECT * INTO NewContact FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\;].[Contacts#txt];"


注意，到 2000 格式的 MDB 为止，以下语句都会导致导入失败，应该是 Access 本身的问题：
到 Access XP / access 2003 出现不知道是否已经解决该问题，大家可以在“评论”中告诉我测试结果。
DoCmd.TransferText acImportFixed, , "Contacts", "C:\contacts.txt"

或者
DoCmd.TransferText acImportFixed, "C:\.ini", "Contacts", "C:\Documents.txt"


错误消息为：

运行时错误 '3625':
文本文件规范 'c:schema.ini' 不存在。不能使用规范进行导入、导出或者链接。
或者
运行时错误 '2511':
这个操作或方法需要一个 Specification Name 参数。


[color=LightBlue]我做的批量导入两种类型的表代码：
Option Compare Database
Option Explicit

Dim FItem As Variant, myFiles As Variant   '定义打开对话框中的文件对象及其集合
Dim FNarr() As String


Sub Multi_Data_ImPortToAccess()
'一般保证导入的工作薄仅有一个导入的工作表
Dim arr() As String
Dim i%
Dim MFileType As String
    MFileType = "*.txt"
    Call DataToAccess_FileDialogType(MFileType)   '从ACCESS对话框中选取指定文件类型的文件
    If VarType(myFiles) = 0 Then  '如果类型为false,则没有文件对象
        MsgBox "你没有选取文件,已放弃选取!"
        Exit Sub
    End If
   
   
    With Excel.Application
       If Not myFiles Is Nothing Then                               '如果有选取文件
            For Each FItem In myFiles                          '遍历
               ReDim arr(myFiles.Count)
               If MFileType = "*.XLS" Then
                    '以默认方式(连接表用aclink)追加导入到当前打开的数据库中的表data中,导入的表头（第一行）为字段名。指定哪个表导入
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "A20B日报(联营租赁)", FItem, True, "sheet1!"
                    arr(i) = Mid(FItem, InStrRev(FItem, "\") + 1, InStrRev(FItem, ".") - 1)
                    i = i + 1
               ElseIf MFileType = "*.txt" Then
                    '导入文本规格文件是在导入表高级选项中保存的规格文件，需在视图－选项-去掉系统及隐藏属性方能在表模块中看到．
                    '一般保存一次后，不需更改了．如有变化，请再次保存规格
                    DoCmd.TransferText acImportDelim, "文本导入规格", "A20A月报", FItem, False
                    'C:\Documents and Settings\Administrator\桌面\导入表\商品销售
               End If
            Next              '在对话框的SelectedItem循环
        End If
    End With
    MsgBox Right(MFileType, 3) & "格式导入成功！"
End Sub

Function DataToAccess_FileDialogType(ByVal FileType As String)  '打开ACCESS文件类型对话框
    Dim i%
    Dim fd As FileDialog, vrtSelectedItem As Variant  '打开对话框变量
    Set fd = Application.FileDialog(msoFileDialogFilePicker) 'FileDialog(3)调用打开对话框
    With fd
        .InitialFileName = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
        '.Show   '下面有显示了
        .AllowMultiSelect = True '允许选取多个文件为True
        .Filters.Clear             '将打开的文件类型如"*.txt"等清空,使它默认为所有文件
        .Filters.Add FileType, FileType, 1   '增加打开对话框中的文件类型 .Filters.Add  显示"*.xls","*.xls"

        
        If .Show = -1 Then  '当为-1时调用打开对话框,如果没有选取文件，则到else
           ReDim FNarr(1 To .SelectedItems.Count)
           Set myFiles = .SelectedItems
           For i = 1 To .SelectedItems.Count
             FNarr(i) = .SelectedItems.Item(i)  '打开对话框中选中的文件名
           Next i
        '放弃选择
        Else
        End If
    End With
    Set fd = Nothing

End Function

[ 本帖最后由 lichaobin 于 2009-3-23 03:11 编辑 ]

lichaobin

发现有别的靓贴，了结一下入门的情况吧，因为知道还有很多东西要学。
假设前台数据库文件名为frontBase.mdb
后台数据库文件名为backData.mdb
frontBase当中有链接表tbl1, tbl2, tbl3, …，链接到backData.mdb中

首先我们要在前台数据库文件的启动窗体加载事件中判断链接是否正确

方法是打开任意一个链接表，假设为tbl1，代码如下：

Public Function CheckLinks() As Boolean
' 检查到后台数据库的链接；如果链接存在且正确的话，返回 True 。　　
　　 Dim dbs As Database, rst As DAO.Recordset　　
　　 Set dbs = CurrentDb()
　　 ' 打开链接表查看表链接信息是否正确。
　　 On Error Resume Next
　　 Set rst = dbs.OpenRecordset(“tbl1”)
　　 rst.Close
　　 ' 如果没有错误，返回 True 。
　　 If Err = 0 Then
　　　　 CheckLinks = True
　　 Else
　　　　 CheckLinks = False
　　 End If　　
End Function

启动窗体的加载事件：
Private Sub FORM_Load()
If CheckLinks = False then
Docmd.OpenFORM “frmConnect”
End If
End Sub

frmConnect 连接窗体如下图

f:\m.bmp


接下来的事情就是如何刷新链接表了。

连接按钮刷新链接表 ，代码如下：
Private Sub OK_Click()
Dim tabDef As TableDef
For Each tabDef In CurrentDb.TableDefs
If Len(tabDef.Connect) > 0 Then
tabDef.Connect = ";DATABASE=" & Me.FileName.Text & ";PWD=" + 后台数据库密码
tabDef.RefreshLink
End If
Next
MsgBox "连接成功！"
DoCmd.Close acFORM, Me.Name
End Sub


其实很简单只有两步，判断链接是否正确和刷新链接表。

leitiger

楼主辛苦了,正想学学access.谢谢分享.

wisdom100

非常好,不错!!请大家顶下

真的感觉EXCEl vba的缺点...数据大则吃不消...
谢谢楼主!!!能否可以提供更多的ACCESSVBA相关的资料~~
~非常感谢!

gxuan2008

谢谢.楼主.
能不能整个附件啊.
呵呵.