精华内容
下载资源
问答
  • VB access 增加字段

    2018-05-30 14:40:01
    VB access 增加字段,这是可以有用处的,请大家使用,谢谢主。
  • 资源名称:Asp代码增加、删除access数据表和字段资源截图: 资源太大,传百度网盘了,链接附件,有需要的同学自取。
  • 微软的 Access 包含 Data Definition Language (DDL) 来建立删除表以及关系,当然了,这也可以用 DAO 来解决。
  • 搞了一下午,终于解决了,希望能帮助到他人。 '通用-声明 Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sql$ ...driver={Microsoft Access Driver (*.mdb)};dbq=" ...

    搞了一下午,终于解决了,希望能帮助到他人。

    '通用-声明
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql$
    '连接Data.mdb数据库
    Public Sub openConn()
       conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & App.Path & "\Data.mdb;Uid=;Pwd=123;"
    End Sub
    '关闭Data.mdb数据库
    Public Sub closeConn()
       conn.Close
    End Sub
    '查找Data.mdb数据库是否存在某列字段,若不存在则添加
    Sub AddColIfNotExists(tblName As String, colName As String, colType As String, DefaultValue As String)
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim cnStr As String, sqlStr As String
        Dim i As Integer, addCol As Boolean
        '此示例ACCESS数据库放在本VB程序同一目录下
        cnStr = "driver={Microsoft Access Driver (*.mdb)};dbq="
        cnStr = cnStr & App.Path & "\Data.mdb;Uid=;Pwd=123;"
        cn.Open cnStr
        rs.CursorLocation = adUseClient
        sqlStr = "select * from " & tblName & " where false;"
        rs.Open sqlStr, cn, adOpenStatic, adLockReadOnly
        addCol = True
        For i = 0 To (rs.Fields.Count - 1)
            If rs(i).Name = colName Then
                addCol = False
                Exit For
            End If
        Next i
        rs.Close
        Set rs = Nothing
        If addCol = True Then  '字段不存在才添加
            sqlStr = "alter table " & tblName
            sqlStr = sqlStr & " add column " & colName
            sqlStr = sqlStr & " " & colType & ";"
            cn.Execute sqlStr
            '设置缺省值
            Call AddDefaultValue(tblName, colName, DefaultValue)
        End If
        cn.Close
        Set cn = Nothing
    End Sub
    '查找Data.mdb数据库是否存在某列字段,若不存在则添加
    Sub AddDefaultValue(tblName As String, colName As String, DefaultExt As String)
       '打开数据库
       Call openConn
       Dim AddValue As String
       AddValue = "select * from " & tblName
       rs.Open AddValue, conn, adOpenKeyset, adLockOptimistic
       Set rs = Nothing
       sql = "update " & tblName & " set " & colName & " = " & "'" & DefaultExt & "'"
       rs.Open sql, conn
       '关闭数据库
       Call closeConn
    End Sub
    '添加新列并设置该列缺省值
    Private Sub Command1_Click()
      '(表名,新增字段,类型,缺省值)
      Call AddColIfNotExists("学生信息表", "添加的新字段", "varchar(50)", "abc")
    End Sub

    展开全文
  • VB对ACCESS表格及其字段的添加及删除

    热门讨论 2012-03-28 10:09:59
    网上整理的,自己进行加工处理,包括对ACCESS表格的添加及删除,对表格字段的添加及删除。注意先引用:“工程”-->"引用"-->"Microsoft ActiveX Data Objects 2.7 Library" 及'Microsoft ADO Ext.2.7 for DDL ...
  • access调整字段顺序This is the second article on row numbers in Microsoft Access. 这是有关Microsoft Access中行号的第二篇文章。 The first is about Random Rows in Microsoft Access. 首先是关于...

    access调整字段顺序

    This is the second article on row numbers in Microsoft Access.

    这是有关Microsoft Access中行号的第二篇文章。

    The first is about Random Rows in Microsoft Access.

    首先是关于Microsoft Access中的随机行

    The third is about Ranking rows in Microsoft Access.

    第三是关于在Microsoft Access中行进行排名


    (
    )

    这是为了什么 (What is it for?)

    Typically, the reason for assigning a sequential number to each record of a recordset is one of these:

    通常,为记录集的每个记录分配顺序号的原因是以下之一:

    • to add a sequential number to records where no other simple ID is present

      在没有其他简单ID的记录中添加序号
    • to enumerate and count the rows returned from a query or listed in a form

      枚举并计算从查询返回或以表格形式列出的行
    • to view or control the position of specific records

      查看或控制特定记录的位置
    • to add a unique ID or a kind of serial number to records before an export

      在导出之前在记录中添加唯一的ID或一种序列号

    That can, in some cases, be obtained with no code other than a simple expression, while other cases may require a more robust or resistant method where code must be used. For this reason, three methods will be discussed here:

    在某些情况下,仅使用简单的表达式就可以不使用任何代码而获得代码,而在其他情况下,可能需要使用必须使用代码的更健壮或更具抵抗性的方法。 因此,这里将讨论三种方法:

    1. Add a sequential record number to each row in a form

      在表格的每一行中添加一个顺序记录号
    2. Add a sequential row number to each record of a query

      在查询的每个记录中添加顺序行号
    3. Add a sequential user maintained priority number to each record in a form

      向表单中的每个记录添加一个顺序的用户维护的优先级编号

    Each method has some distinct advantages and (perhaps) disadvantages that must be taken into consideration before deciding which method to use in a given scenario.

    每种方法都有一些明显的优点和(也许)缺点,在决定在给定方案中使用哪种方法之前,必须考虑这些优点和缺点。

    1.记录编号 (1. Record Numbers)

    These are similar to the Record Number displayed in the Navigation Bar of a form (left-bottom, in the status bar of the form).

    这些类似于在表单的导航栏中显示的记录编号(在表单的状态栏中为左下)。

    优点 (Advantages)

    • Will always be sequential from top to bottom of the form, no matter how records are ordered, filtered, edited, deleted, or inserted

      无论记录如何排序,过滤,编辑,删除或插入,它将始终从表格的顶部到底部是连续的
    • For a form, the source can be a table; a query is not required

      对于表单,源可以是表格; 不需要查询

    缺点 (Disadvantages)

    • Will not, for the individual record, be static ("sticky")

      对于个人记录,不会是静态的(“粘性”)
    • Belongs to the form, not the recordset

      属于表格,而不是记录集
    • May update slowly when browsing the form

      浏览表单时可能更新缓慢
    • For forms only, not queries

      仅用于表单,不用于查询

    实作 (Implementation)

    The function for this is passed the form itself as a parameter, then uses the RecordsetClone of the form to obtain the AbsolutPosition of the record, which always indicates where the record currently is positioned in the form.

    为此的函数将表单本身作为参数传递,然后使用表单的RecordsetClone获得记录的AbsolutPosition ,该位置始终指示当前在表单中放置记录的位置。

    ' Creates and returns a sequential record number for records displayed
    ' in a form, even if no primary or unique key is present.
    ' For a new record, Null is returned until the record is saved.
    '
    ' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.
    '
    Public Function RecordNumber( _
        ByRef Form As Access.Form, _
        Optional ByVal FirstNumber As Long = 1) _
        As Variant
        ' Error code for "There is no current record."
        Const NoCurrentRecord   As Long = 3021
       
        Dim Records             As DAO.Recordset
       
        Dim Number              As Variant
        Dim Prompt              As String
        Dim Buttons             As VbMsgBoxStyle
        Dim Title               As String
        On Error GoTo Err_RecordNumber
        If Form Is Nothing Then
            ' No form object is passed.
            Number = Null
        ElseIf Form.Dirty = True Then
            ' No record number until the record is saved.
            Number = Null
        Else
            Set Records = Form.RecordsetClone
            Records.Bookmark = Form.Bookmark
            Number = FirstNumber + Records.AbsolutePosition
            Set Records = Nothing
        End If
       
    Exit_RecordNumber:
        RecordNumber = Number
        Exit Function
       
    Err_RecordNumber:
        Select Case Err.Number
            Case NoCurrentRecord
                ' Form is at new record, thus no Bookmark exists.
                ' Ignore and continue.
            Case Else
                ' Unexpected error.
                Prompt = "Error " & Err.Number & ": " & Err.Description
                Buttons = vbCritical + vbOKOnly
                Title = Form.Name
                MsgBox Prompt, Buttons, Title
        End Select
       
        ' Return Null for any error.
        Number = Null
        Resume Exit_RecordNumber
    End Function 
    

    The implementation in the form is extremely simple:

    形式的实现非常简单:

    Create a TextBox to display the record number, and set the ControlSource of this to:

    创建一个TextBox以显示记录号,并将其ControlSource设置为:

    =RecordNumber([Form]) 
    

    The returned number will equal the Current Record displayed in the form's record navigator (bottom-left).

    返回的数字将等于窗体的记录导航器(左下)中显示的“当前记录”。

    Optionally, you may specify a first number other than 1, say 0, by using the second parameter:

    (可选)您可以使用第二个参数指定除1以外的第一个数字,例如0:

    =RecordNumber([Form],0) 
    

    NB: For localised versions of Access, when entering the expression, type:

    注意:对于Access的本地化版本,在输入表达式时,键入:

    =RecordNumber([LocalisedNameOfObjectForm]) 
    

    for example:

    例如:

    =RecordNumber([Formular]) 
    

    and press Enter. The expression will update to:

    然后按Enter。 该表达式将更新为:

    =RecordNumber([Form]) 
    

    If the form can't add or delete records, you're done, but if it is, you will have to requery the textbox to update the numbers:

    如果表单无法添加或删除记录,则说明您已经完成,但是如果是,则必须重新查询文本框以更新数字:

    Private Sub Form_AfterDelConfirm(Status As Integer)
        Me!RecordNumber.Requery
    End Sub
    
    Private Sub Form_AfterInsert()
        Me!RecordNumber.Requery
    End Sub 
    

    其他用法 (Other usage)

    You may also use the function from elsewhere to obtain the record number of an open form:

    您还可以从其他地方使用该函数来获取打开表格的记录号:

    Dim Number As Variant
    
    Number = RecordNumber(Forms(IndexOfSomeFormInFormsCollection))
    ' or
    Number = RecordNumber(Forms("NameOfSomeOpenForm") 
    

    2.行号 (2. Row Numbers)

    These are created in a query, as a separate field of the resulting recordset.

    这些在查询中创建,作为结果记录集的单独字段。

    优点 (Advantages)

    • The numbers will not update if records are deleted, and new records will be assigned the next higher number(s) as long as the query is not required

      如果删除记录,则编号不会更新,并且只要不需要查询,就会为新记录分配下一个更高的编号。
    • The numbers will be defined by the ordering of the query

      数字将通过查询的顺序定义
    • If a form is bound to the query, the numbers will always stick to the individual record, no matter how the form is ordered or filtered, or (if the query or form is not updated/required) if records are added or deleted

      如果将表格绑定到查询,则无论表格如何排序或过滤,或者(如果查询或表格未更新/不需要)是否添加或删除了记录,数字始终会粘贴在单个记录上
    • Generating the numbers takes one table scan only, thus browsing the query, or a form bound to it is very fast

      生成数字仅需进行一次表扫描 ,因此浏览查询或与其绑定的表格非常快
    • As the numbers are static ("sticky"), they are well suited for export or for use in append queries

      由于数字是静态的(“粘性”),因此非常适合导出或在附加查询中使用

    缺点 (Disadvantages)

    • If records are added or deleted, the assigned numbers may change after a re-query to maintain sequentiality

      如果添加或删除记录,则重新查询后分配的编号可能会更改以保持顺序
    • If used in a form, and different filtering or sorting is applied, there is no method to regain sequentiality other than to revert to the original ordering and remove filtering

      如果以某种形式使用,并且应用了不同的过滤或排序,则除了恢复到原始顺序并删除过滤之外,没有其他方法可以恢复顺序

    实作 (Implementation)

    The function to create the row numbers uses a collection to store these. The great advantage of this method is, that it only takes one table scan to calculate and store the numbers. From then on, the numbers are read directly from the collection, which is very fast. Thus, a query or form displaying the row numbers is not degraded when browsed or read multiple times.

    创建行号的功能使用一个集合来存储它们。 该方法的最大优点是,只需进行一次表扫描即可计算和存储数字。 从那时起,可以直接从集合中读取数字,这非常快。 因此,当多次浏览或读取时,显示行号的查询或表单不会降级。

    In addition to the traditional numbering of the full recordset, the function also offers numbering of groups and numbering of records on the group level. This is quite fancy and a bit hard to explain without studying the code line by line, thus extensive in-line comments that explain every step has been included in the function for those interested.

    除了对完整记录集进行传统编号之外,该功能还提供组编号和组级别上的记录编号。 如果不逐行研究代码,这是相当花哨的,并且很难解释,因此,对于那些感兴趣的人,在函数中包含了解释每一步的大量内联注释。

    ' Builds consecutive row numbers in a select, append, or create query
    ' with the option of a initial automatic reset.
    ' Optionally, a grouping key can be passed to reset the row count
    ' for every group key.
    '
    ' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.
    '
    Public Function RowNumber( _
        ByVal Key As String, _
        Optional ByVal GroupKey As String, _
        Optional ByVal Reset As Boolean) _
        As Long
       
        ' Uncommon character string to assemble GroupKey and Key as a compound key.
        Const KeySeparator      As String = "¤§¤"
        ' Expected error codes to accept.
        Const CannotAddKey      As Long = 457
        Const CannotRemoveKey   As Long = 5
     
        Static Keys             As New Collection
        Static GroupKeys        As New Collection
        Dim Count               As Long
        Dim CompoundKey         As String
       
        On Error GoTo Err_RowNumber
       
        If Reset = True Then
            ' Erase the collection of keys and group key counts.
            Set Keys = Nothing
            Set GroupKeys = Nothing
        Else
            ' Create a compound key to uniquely identify GroupKey and its Key.
            ' Note: If GroupKey is not used, only one element will be added.
            CompoundKey = GroupKey & KeySeparator & Key
            Count = Keys(CompoundKey)
           
            If Count = 0 Then
                ' This record has not been enumerated.
                '
                ' Will either fail if the group key is new, leaving Count as zero,
                ' or retrieve the count of already enumerated records with this group key.
                Count = GroupKeys(GroupKey) + 1
                If Count > 0 Then
                    ' The group key has been recorded.
                    ' Remove it to allow it to be recreated holding the new count.
                    GroupKeys.Remove (GroupKey)
                Else
                    ' This record is the first having this group key.
                    ' Thus, the count is 1.
                    Count = 1
                End If
                ' (Re)create the group key item with the value of the count of keys.
                GroupKeys.Add Count, GroupKey
            End If
            ' Add the key and its enumeration.
            ' This will be:
            '   Using no group key: Relative to the full recordset.
            '   Using a group key:  Relative to the group key.
            ' Will fail if the key already has been created.
            Keys.Add Count, CompoundKey
        End If
       
        ' Return the key value as this is the row counter.
        RowNumber = Count
     
    Exit_RowNumber:
        Exit Function
       
    Err_RowNumber:
        Select Case Err
            Case CannotAddKey
                ' Key is present, thus cannot be added again.
                Resume Next
            Case CannotRemoveKey
                ' GroupKey is not present, thus cannot be removed.
                Resume Next
            Case Else
                ' Some other error. Ignore.
                Resume Exit_RowNumber
        End Select
    End Function 
    

    The typical usage in a select query is:

    选择查询的典型用法是:

    SELECT RowNumber(CStr([ID])) AS RowID, *
    FROM SomeTable
    WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True)); 
    

    If the table has no index, it will be slightly different:

    如果表没有索引,则将略有不同:

    SELECT RowNumber(CStr([ID])) AS RowID, *
    FROM SomeTable
    WHERE (RowNumber("","",True)=0);

    The purpose of the WHERE clause is to call the function

    WHERE子句的目的是调用该函数

    once and 一次once onlyfor a reset of the numbers. 一次仅用于重置号码。

    As the call of the function to create a number contains the ID of the record, it will happen for each record, but the call for a reset does not, thus the query will call it first - and once only.

    由于创建数字的函数的调用包含记录的ID,因此每条记录都会发生该调用,但是重置不会发生,因此查询将首先调用它,并且仅调用一次。

    If you need to include a group key, it is easily done - just specify it as the second parameter:

    如果您需要包括一个组密钥 ,则很容易完成-只需将其指定为第二个参数即可:

    SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
    FROM SomeTable
    WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True)); 
    

    If you wish to create an append query, a similar technique can be used:

    如果您希望创建一个追加查询 ,可以使用类似的技术:

    INSERT INTO TempTable ( [RowID] )
    SELECT RowNumber(CStr([ID])) AS RowID, *
    FROM SomeTable
    WHERE (RowNumber("","",True)=0); 
    

    However, it will not always be feasible with an automatic reset. For example: If you wish to run the query several times, every time appending new records, and these records should be assigned one sequential series of numbers, the rest of the row numbers must be done manually.

    但是,使用自动复位并不总是可行的。 例如:如果您希望多次运行查询,每次附加新记录,并且这些记录应分配一个连续的数字序列,则其余的行号必须手动完成。

    So, first call the function this way to reset the counters:

    因此,首先以这种方式调用函数以重置计数器:

    RowNumber(vbNullString, , True) 
    

    Then run the append query - without a row number reset - as many times as needed:

    然后根据需要运行追加查询-无需重置行号-多次:

    INSERT INTO TempTable ( [RowID] )
    SELECT RowNumber(CStr([ID])) AS RowID, *
    FROM SomeTable; 
    

    其他用法 (Other usage)

    Though only intended for usage as shown in a query - opened either on its own or as the source for a form - the function can also be called from code. Say, you have a query or form open, you can easily look up the assigned row number for any ID of the recordset:

    尽管仅用于查询中的用法(可以单独打开或作为表单的源打开),但也可以从代码中调用该函数。 假设您打开了一个查询或表单,则可以轻松查找记录集的任何ID的已分配行号:

    Dim Key As String
    
    
    Key = CStr(IdToLookUp)
    AssignedRowNumber = RowNumber(Key) 
    

    3.优先号码 (3. Priority Numbers)

    The purpose of assigning a priority (or rank) is to define a sort order for a list of records - different from any other possible sort order of the recordset. Typically, this will reflect a decision made by a human (a user), for example for scenarios like these:

    分配优先级(或等级)的目的是为记录列表定义排序顺序-与记录集的任何其他可能的排序顺序不同。 通常,这将反映人类(用户)的决定,例如针对以下情况:

    • Order a list of order lines in an order - different from product numbers or the like

      订购订单中的订单行列表-与产品编号等不同
    • Rank persons

      等级人员
    • Assign priority to tasks

      为任务分配优先级
    • Order steps of a checklist

      清单的订购步骤
    • Order of options for answers to a question in a multiple-choice test

      多项选择题中对问题答案的选项顺序

    The assigned priority is stored in a separate numeric field of the table. Thus, they are persistent, and primarily intended to be maintained by a user having the records listed in a form.

    分配的优先级存储在表的单独数字字段中。 因此,它们是持久的,并且主要旨在由具有以表格形式列出的记录的用户来维护。

    The task for the function published here is to keep the full series of priorities in sequence when any of these is adjusted. This means, that if a number is changed, other numbers must be increased or decreased to maintain the continuous sequence of numbers.

    此处发布的功能的任务是在调整优先级中的任何优先级时保持其顺序。 这意味着,如果更改了数字,则必须增加或减少其他数字以保持数字的连续顺序。

    Here we change the priority of record two.

    在这里,我们更改记录2的优先级。

    If the priority is lowered (numeric value increased):

    如果降低优先级(增加数值):

    BeFORE CHANGE
    CHANGE
    AFTER CHANGE
    1

    1
    2
    4
    4
    3

    2
    4

    3
    5

    5
    更改之前
    更改
    变更后
    1个

    1个
    2
    4
    4
    3

    2
    4

    3
    5

    5

    If the priority is raised (numeric value decreased):

    如果提高优先级(降低数值):

    BeFORE CHANGE
    CHANGE
    AFTER CHANGE
    1

    1
    2

    3
    3

    4
    4
    2
    2
    5

    5
    更改之前
    更改
    变更后
    1个

    1个
    2

    3
    3

    4
    4
    2
    2
    5

    5

    优点 (Advantages)

    • The numbers will be persistent, no matter how the records are filtered or ordered

      无论记录如何过滤或排序,数字都将是永久性的
    • When maintained in a form, a new record will automatically be assigned the next higher number (lowest priority)

      当以表格形式维护时,新记录将自动分配给下一个较高的编号(最低优先级)
    • In the form, records can be inserted or deleted without breaking the sequentiality of the numbers

      在表格中,可以插入或删除记录,而不会破坏数字的顺序
    • Can easily, for example by a button-click, be reset to match the current sorting of the form

      可以轻松地重置(例如通过单击按钮)以匹配表单的当前排序
    • Will not degrade browsing in any way

      不会以任何方式降低浏览速度

    缺点 (Disadvantages)

    • If records can be inserted or deleted from sources not maintaining the sequentiality of the numbers, this will be broken. However, sequentiality can be restored by a call to the function AlignPriority

      如果可以从不保持编号顺序的源中插入或删除记录,则记录将被破坏。 但是,可以通过调用AlignPriority函数来恢复顺序性

    实作 (Implementation)

    A form, a textbox, and a function that takes this textbox as an argument is required.

    需要一个表单,一个文本框和一个以该文本框作为参数的函数。

    A numeric and unique key is required, typical an AutoNumber named ID. If the name is not ID, it must be specified as the second parameter of the function.

    需要数字和唯一键,通常是名为ID的自动编号。 如果名称不是ID,则必须将其指定为函数的第二个参数。

    The function pulls all necessary information from the properties of this textbox, and then rearranges the full sequence of priority numbers to respect the change of value in this textbox by looping the RecordsetClone of the form.

    该函数从此文本框的属性中提取所有必要的信息,然后通过循环表单的RecordsetClone来重新排列优先级数字的完整顺序,以尊重此文本框中的值更改。

    Also here, the function is fully documented in-line, so you can read it line by line for the details:

    同样在这里,该函数已全部内联文档,因此您可以逐行阅读以获取详细信息:

    ' Set the priority order of a record relative to the other records of a form.
    '
    ' The table/query bound to the form must have an updatable numeric field for
    ' storing the priority of the record. Default value of this should be Null.
    '
    ' Requires:
    '   A numeric, primary key, typical an AutoNumber field.
    '
    ' 2018-08-31. Gustav Brock, Cactus Data ApS, CPH.
    '
    Public Sub RowPriority( _
        ByRef TextBox As Access.TextBox, _
        Optional ByVal IdControlName As String = "Id")
       
        ' Error codes.
        ' This action is not supported in transactions.
        Const NotSupported      As Long = 3246
        Dim Form                As Access.Form
        Dim Records             As DAO.Recordset
       
        Dim RecordId            As Long
        Dim NewPriority         As Long
        Dim PriorityFix         As Long
        Dim FieldName           As String
        Dim IdFieldName         As String
       
        Dim Prompt              As String
        Dim Buttons             As VbMsgBoxStyle
        Dim Title               As String
       
        On Error GoTo Err_RowPriority
       
        Set Form = TextBox.Parent
       
        If Form.NewRecord Then
            ' Will happen if the last record of the form is deleted.
            Exit Sub
        Else
            ' Save record.
            Form.Dirty = False
        End If
       
        ' Priority control can have any Name.
        FieldName = TextBox.ControlSource
        ' Id (primary key) control can have any name.
        IdFieldName = Form.Controls(IdControlName).ControlSource
       
        ' Prepare form.
        DoCmd.Hourglass True
        Form.Repaint
        Form.Painting = False
       
        ' Current Id and priority.
        RecordId = Form.Controls(IdControlName).Value
        PriorityFix = Nz(TextBox.Value, 0)
        If PriorityFix <= 0 Then
            PriorityFix = 1
            TextBox.Value = PriorityFix
            Form.Dirty = False
        End If
       
        ' Disable a filter.
        ' If a filter is applied, only the filtered records
        ' will be reordered, and duplicates might be created.
        Form.FilterOn = False
       
        ' Rebuild priority list.
        Set Records = Form.RecordsetClone
        Records.MoveFirst
        While Not Records.EOF
            If Records.Fields(IdFieldName).Value <> RecordId Then
                NewPriority = NewPriority + 1
                If NewPriority = PriorityFix Then
                    ' Move this record to next lower priority.
                    NewPriority = NewPriority + 1
                End If
                If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then
                    ' Priority hasn't changed for this record.
                Else
                    ' Assign new priority.
                    Records.Edit
                        Records.Fields(FieldName).Value = NewPriority
                    Records.Update
                End If
            End If
            Records.MoveNext
        Wend
       
        ' Reorder form and relocate record position.
        ' Will fail if more than one record is pasted in.
        Form.Requery
        Set Records = Form.RecordsetClone
        Records.FindFirst "[" & IdFieldName & "] = " & RecordId & ""
        Form.Bookmark = Records.Bookmark
       
    PreExit_RowPriority:
        ' Enable a filter.
        Form.FilterOn = True
        ' Present form.
        Form.Painting = True
        DoCmd.Hourglass False
       
        Set Records = Nothing
        Set Form = Nothing
       
    Exit_RowPriority:
        Exit Sub
       
    Err_RowPriority:
        Select Case Err.Number
            Case NotSupported
                ' Will happen if more than one record is pasted in.
                Resume PreExit_RowPriority
            Case Else
                ' Unexpected error.
                Prompt = "Error " & Err.Number & ": " & Err.Description
                Buttons = vbCritical + vbOKOnly
                Title = Form.Name
                MsgBox Prompt, Buttons, Title
               
                ' Restore form.
                Form.Painting = True
                DoCmd.Hourglass False
                Resume Exit_RowPriority
        End Select
       
    End Sub 
    

    It requires very little code in the form to work. 

    它只需要很少的代码即可工作。

    If the textbox holding the priority field is named Priority, it will only be this:

    如果包含优先级字段的文本框名为Priority ,则只能是以下形式:

    After updating the Priority textbox:

    更新优先级文本框后:

    Private Sub Priority_AfterUpdate()
        RowPriority Me.Priority
    End Sub 
    

    After deleting or inserting a record, if that is allowed:

    删除或插入记录后,如果允许的话:

    Private Sub Form_AfterDelConfirm(Status As Integer)
        RowPriority Me.Priority
    End Sub
    
    Private Sub Form_AfterInsert()
        RowPriority Me.Priority
    End Sub 
    

    That's it.

    而已。

    其他用法 (Other usage)

    If the table with the priority field somehow can be scrambled by another process that is not aware of this field, or a table initially has no values filled in, a helper function is included, that will align the values to a given sort order.

    如果具有优先级字段的表可以被不知道该字段的另一个进程扰乱,或者某个表最初没有填充值,则包含一个辅助函数,该函数将这些值与给定的排序顺序对齐。

    It takes the recordset as the first parameter, and (re)arranges the priority field of this. If the field is not named Priority, pass its name as the second parameter.

    它以记录集作为第一个参数,并(重新)排列其优先级字段。 如果该字段未命名为Priority ,则将其名称作为第二个参数传递。

    The exact usage is described in the in-line comments in the header:

    确切的用法在标题的内嵌注释中描述:

    ' Loop through a recordset and align the values of a priority field
    ' to be valid and sequential.
    '
    ' Default name for the priority field is Priority.
    ' Another name can be specified in parameter FieldName.
    '
    ' Typical usage:
    '   1.  Run code or query that updates, deletes, or appends records to
    '       a table holding a priority field.
    '
    '   2.  Open an updatable and sorted DAO recordset (Records) with the table:
    '
    '       Dim Records As DAO.Recordset
    '       Set Records = CurrentDb("Select * From Table Order By SomeField")
    '   3.  Call this function, passing it the recordset:
    '
    '       AlignPriority Records
    '
    ' 2018-09-04. Gustav Brock, Cactus Data ApS, CPH.
    '
    Public Sub AlignPriority( _
        ByRef Records As DAO.Recordset, _
        Optional FieldName As String)
        Const FirstNumber       As Long = 1
        Const PriorityFieldName As String = "Priority"
       
        Dim Field               As DAO.Field
       
        Dim CurrentPriority     As Long
        Dim NextPriority        As Long
       
        If FieldName = "" Then
            FieldName = PriorityFieldName
        End If
        ' Verify that the field exists.
        For Each Field In Records.Fields
            If Field.Name = FieldName Then
                Exit For
            End If
        Next
        ' If FieldName is not present, exit silently.
        If Field Is Nothing Then Exit Sub
       
        NextPriority = FirstNumber
        ' Set each record's priority to match its current position as
        ' defined by the sorting of the recordset.
        Records.MoveFirst
        While Not Records.EOF
            CurrentPriority = Nz(Field.Value, 0)
            If CurrentPriority = NextPriority Then
                ' No update needed.
            Else
                ' Assign and save adjusted priority.
                Records.Edit
                    Field.Value = NextPriority
                Records.Update
            End If
            Records.MoveNext
            NextPriority = NextPriority + 1
        Wend
       
    End Sub 
    

    Further, if the priority sequence needs a full rearrange, it can be done with, say, a button click - typically when the records of the form have been sorted in some way. For example:

    此外,如果优先级序列需要完全重新排列,则可以通过单击按钮来完成-通常是在对表单的记录进行某种排序的情况下。 例如:

    Private Sub ResetPriorityButton_Click()
        SetRowPriority Me.Priority
    End Sub 
    

    That will call this function which simply reads the property AbsolutPosition of every record:

    这将调用此函数,该函数仅读取每个记录的属性AbsolutPosition

    ' Set the priority order of the records to match a form's current record order.
    '
    ' The table/query bound to the form must have an updatable numeric field for
    ' storing the priority of the records. Default value of this should be Null.
    '
    ' Usage:
    '   To be called from, say, a button click on the form.
    '   The textbox Me.Priority is bound to the Priority field of the table:
    '
    '       Private Sub ResetPriorityButton_Click()
    '           SetRowPriority Me.Priority
    '       End Sub
    '
    '
    ' 2018-08-27. Gustav Brock, Cactus Data ApS, CPH.
    '
    Public Sub SetRowPriority(ByRef TextBox As Access.TextBox)
        Const FirstNumber       As Long = 1
       
        Dim Form                As Access.Form
        Dim Records             As DAO.Recordset
       
        Dim FieldName           As String
           
        Set Form = TextBox.Parent
        Set Records = Form.RecordsetClone
       
        ' TextBox can have any Name.
        FieldName = TextBox.ControlSource
       
        ' Pause form painting to speed up rebuilding of the records' priority.
        Form.Painting = False
       
        ' Set each record's priority to match its current position in the form.
        Records.MoveFirst
        While Not Records.EOF
            If Records.Fields(FieldName).Value = FirstNumber + Records.AbsolutePosition Then
                ' No update needed.
            Else
                ' Assign and save adjusted priority.
                Records.Edit
                    Records.Fields(FieldName).Value = FirstNumber + Records.AbsolutePosition
                Records.Update
            End If
            Record   s.MoveNext
        Wend
       
        ' Repaint form.
        Form.Painting = True
        Set Records = Nothing
        Set Form = Nothing
    End Sub 
    

    演示版 (Demo)

    The form Products in the attached sample database - a subset of the Northwind 2007 Sample database - shows all three numbering methods side by side, and also the ID of the records:

    随附的示例数据库中的Products表格是Northwind 2007示例数据库的子集,并排显示了所有三种编号方法以及记录的ID:

    If you open it, you can try to sort and filter as you like and see, how the number sequences adopt.

    如果将其打开,则可以尝试根据需要进行排序和过滤,并查看数字序列的采用方式。

    Also, a button, Reset, is present to demonstrate a reset of the priority numbers.

    此外,还有一个按钮Reset ,用于演示优先级数字的重置。

    Further, a form, ProductsSlow, is included. 

    此外,还包括一个ProductsSlow形式。

    This demonstrates the traditional code-less method to obtain row numbers using an expression with DLookup, one relative to field ID, and one relative to field Product Code. Also, for reference, a field, Record, displaying the record number is present.

    这演示了使用带有DLookup的表达式来获得行号的传统的无代码方法,一个表达式相对于字段ID ,一个相对于字段Product Code 。 另外,作为参考,存在显示记录号的字段Record

    The important thing here to notice is, that none of these fields are sortable in this form. In form Products, row number and priority are both sortable fields, as they are retrieved from the source query.

    这里要注意的重要一点是,这些字段都不能以这种形式排序。 在表格Products中 ,行号和优先级都是可排序的字段,因为它们是从源查询中检索到的。

    In module Demo, a function, PriorityCleanTest, is included to show how to call the function AlignPriority described above.

    Demo模块中,包含一个函数PriorityCleanTest ,以显示如何调用上述函数AlignPriority

    Also, two queries are included, showing typical usage of the functions.

    此外,还包括两个查询,显示了该函数的典型用法。

    Finally, as a practical example, a form is included where a custom Record of Records  control is included:

    最后,作为一个实际示例,包括一个表格,其中有一个自定义的“ 记录记录”    控件包括:




    (
    )

    结论 (Conclusion)

    Three quite different methods for sequential numbering of records and rows have been described and listed, each with its advantages and disadvantages. 

    已经描述和列出了记录和行的顺序编号的三种完全不同的方法,每种方法都有其优点和缺点。

    Combined with the first part on random enumeration, they are supposed to cover every real-life need.

    结合有关随机枚举的第一部分,它们应该可以满足现实生活中的所有需求。

    更多信息 (Further information )

    The sequential numbering is part of a project on the general numbering of records.

    顺序编号是项目中记录通用编号的一部分。

    Random ordering is covered here: Random Rows in Microsoft Access

    此处涵盖随机顺序: Microsoft Access中的随机行

    Ranking rows is covered here: Ranking rows in Microsoft Access

    此处包含排名行Microsoft Access中的排名行

    A sample database in Access 365 is attached: RowNumbers 1.4.2.zip

    附加了Access 365中的示例数据库: RowNumbers 1.4.2.zip

    All current code can also be found on GitHub: VBA.RowNumbers

    所有当前代码也可以在GitHub找到: VBA.RowNumbers

    I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

    希望本文对您有所帮助。 鼓励您在下面提出问题,报告任何错误或对此作出任何其他评论。

    Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

    注意 :如果您需要有关此主题的更多“支持”,请考虑使用Experts Exchange 的“提问”功能。 我会监督提出的问题,并很高兴与其他电子工程师一起为以这种方式提出的问题提供所需的任何其他支持。

    Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.

    如果您认为本文对EE成员有用且有价值,请不要忘记按下“竖起大拇指”按钮。

    翻译自: https://www.experts-exchange.com/articles/33069/Sequential-Rows-in-Microsoft-Access.html

    access调整字段顺序

    展开全文
  • %'功能:显示数据库表名、字段名、字段内容'原创:wangsdong'来源:aspprogram.cn'原创文章,转载请保留此信息,谢谢set rs=server.CreateObject("adodb.recordset")db="db1.mdb"set conn=server.CreateObject(...
    Access

    < %
    '功能:显示数据库中表名、字段名、字段内容
    '原创:wangsdong
    '来源:aspprogram.cn
    '原创文章,转载请保留此信息,谢谢

    set rs=server.CreateObject("adodb.recordset")
    db="db1.mdb"
    set conn=server.CreateObject("adodb.connection")
    connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(db)
    conn.open connstr

    Set rs=Conn.OpenSchema(20)
    Do Until rs.EOF

    If rs(3)="TABLE" Then
    response.write "表名:"&rs(2)&"<br />"
    Set rs1=server.CreateObject("adodb.recordset")
    sql="select * from "&rs(2)
    Set rs1=conn.execute(sql)
    response.write "字段名:"
    For i=0 To rs1.fields.count-1
    response.write rs1.fields(i).name&"    "
    Next
    response.write "<br />"

    Do While Not rs1.eof
    response.write "        "
    For i=0 To rs1.fields.count-1
    t=rs1.fields(i).name
    response.write rs1(t)&"    "
    Next
    response.write " "
    rs1.movenext
    Loop
    response.write " "
    End If

    rs.MoveNext
    Loop

    Set rs=Nothing
    Set conn=nothing
    % >



    SQL Server

    <%
          SET Conn=Server.CreateObject("ADODB.Connection")
          Conn.Open "Server=IP地址;Provider=sqloledb;Database=库名称;UID=用户名;PWD=密码;"
    %>
    读SqlServer库中的表名:
       <%
          Set rs=Conn.OpenSchema(20)
          While not rs.EOF
             Response.Write("所在数据库名:" & rs(0) & "
    ")
            Response.Write("所有者:" & rs(1) & "
    ")
              Response.Write("表 名:" & rs(2) & "
    ")
             Response.Write("表的类型:" & rs(3) & "
    ")
             rs.MoveNext
          Wend
        %>
    这样,我们知道表名了,现在我们再来看看怎么对表的字段进行操作。
    假设:其中数据库中有表:[Things],表中字段为:id,thingsName,thingsType
    获取该表的所有字段名:
       <%
           Dim i,j,Sql
           Set rs=Server.CreateObject(“ADODB.Recordset”)
           Sql="select * from [Things] where 1<>1"
           rs.open sql,Conn,1,1
           j=rs.Fields.count
           For i=0 to (j-1)
              Response.Write("第" & i+1 & "个字段名:" & rs.Fields(i).Name & "

    ")
           Next
         %>
    好了,现在我们明白了怎么获取字段名了。
    如果大家想对获取的字段值进行一些操作,这也是可以的:
    比如我们想要删除表[Things]中字段thingsType ,就可
    以这样写:
          <%
         Sql="ALTER TABLE [Things] DROP COLUMN thingsType"
         Conn.execute Sql
       %>
    又比如我们想要添加一个字段thingsCOLOR,它的类型为varchar型,长度为20,且默认值为Red,写法如下:
          <%
         Sql="ALTER TABLE [Things] ADD thingsCOLOR VARCHAR(20) DEFAULT Red"
         Conn.execute Sql
       %>
    以上对字段的基本操作都是用SQL语言实现,在ASP中,通过SQL语言,我们只要有足够的权限就可以完成
    更多的数据库操作,比如用CREATE建表,用DROP删除表等等

    转载于:https://www.cnblogs.com/tecsoon/archive/2009/06/10/1500407.html

    展开全文
  • {$IFDEF sql}//sql server数据库语句进行判断  with dm.ADOQuery1 do  begin  Close;  SQL.Clear;  SQL.Add(' SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID WHERE T1.NAME='...

    {$IFDEF sql}//在sql server数据库语句中进行判断

      with dm.ADOQuery1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add(' SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID WHERE T1.NAME=''czy'' AND T2.NAME=''czyjy'' ');
        Open;
        if Eof then
          try
            with dm.quetemp do
            begin
              Close; sql.Clear;
              SQL.Add(' alter table czy add czyjy bit  ');
              ExecSQL;
              Close; sql.Clear;
              SQL.Add('update czy set czyjy=0');
              ExecSQL;
            end;
          except
          end;
      end;
    {$ENDIF}


    {$IFDEF access}//在access数据库中进行判断


        with dm.ADOQuery1 do
        begin
          Close;
          SQL.Clear;
          SQL.Add(' SELECT * FROM czy');
          Open;
          for i := 0 to Fields.count-1 do
          begin
            if Fields[I].FieldName = 'czyjy' then
            begin
               m := 0;
               Break;
            end
            else
               m := 1 ;
          end;
        end;
        if m = 1 then
          try
            with dm.quetemp do
            begin
              Close; sql.Clear;
              SQL.Add(' alter table czy add czyjy bit  ');
              ExecSQL;
              Close; sql.Clear;
              SQL.Add('update czy set czyjy=0');
              ExecSQL;
            end;
          except
          end;
    {$ENDIF}
    展开全文
  • Access数据库用SQL增加字段

    千次阅读 2010-12-19 16:03:00
    最近用到Access数据库中用SQL语句增加字符串类型字段 于是网上搜了一下,...发现这样的类型用SQL中不出错,但是字段中数据后面会加多余空格,经过查找发现字段类型应该用TEXT(20)   新建字段的SQL为 ALTER T
  • 我想要结果集中增加一个字段,这个字段是表没有的,值是固定的,例如: | 字段1 | 字段2 | 新增字段 | | 值1 | 值2 | 值固定 | 解决措施 假设你的表名为tab1,只有字段为a,b,想查询结果新增字段c...
  • ACCESS中如何批量修改字段

    千次阅读 2013-01-25 16:52:48
    要使用Access2003以上的版本,比如将表table1的content字段里的AA全部改成BB.操作如下:打开数据库后,查询--->设计视图创建查询,然后关闭弹出的窗口,点左上角工具栏的SQL输入:update table1 set content=...
  • asp 创建 ACCESS数据库,建立字段等操作
  • 所以要判断Access中一个字段是否存在,如果不存在就增加。 以下是参考http://topic.csdn.net/t/20021119/11/1188678.html Brunhild的解决方案,我又重写了一下。 private bool checkField(String sTblName, ...
  • C# Access修改字段说明

    千次阅读 2015-10-26 10:44:07
    -----------------------引用 .NET adodb ... Microsoft ADO Ext....2.8 for DDL ...----------------------- ...同理,也可以获取相应的字段属性。 博客来源: http://blog.csdn.net/zdb330906531
  • 今天遇到一个需求,在Access数据库,有个net_id 字段,它的值是由 “jjgrape” 这个字符串和 id 字段组成的,也就是说,要把 ‘jjgrape’ 和 id 字段拼接起来: 那怎么拼接呢? 第一反应是 “ + ”,但结果报错...
  • Access sql语句创建表及字段类型

    千次阅读 2018-07-31 15:04:46
    创建一张空表: Sql="Create TABLE [表名]" 创建一张有字段的表: Sql="Create TABLE [表名]([字段名1] MEMO NOT NULL, [字段名2] MEMO,
  • 学习过Access的读者可能知道,access设计视图有一个很不错的功能-----设计列的注释,这可以让设计者清楚地看到每个列的意义。一般的SQL Server建表时没有这个功能,但是他有扩展属性,这个功能也不错,可以把注释写...
  • VC++2010用ADO方法在ACCESS2010数据库中增加、删除和修改记录。【要机器安装Microsoft.ACE.OLEDB.12.0引擎,网上有下载的】。 本实例参照清华大学出版社的《Visual C++范例开发大全》,ISBN: 978-7-302-22624-...
  • mysql 增加字段或修改字段长度、报错 [Err] 1118 - Row size too large 一般出现该问题的原因是因为超过了mysql允许的最大表单长度65535 最近本人也遇到过该问题上网收了一堆相关的解决办法,最终总结出了正确的...
  • <... 由于操作Access数据库,需要用增加字段,但又不知道字段的属性怎么设置,好不容易找到了一些资料,接凑着把字段属性找到了。 然后又找了一些资料,用一个最笨的办法把字段的所有属性加...
  • 在Access中用代码建立表和字段,通常用DAO的方式。 如:  Dim dbs As DAO.Database  Dim tdf As DAO.TableDef  Dim fld As DAO.Field  Set dbs = CurrentDb  Set tdf = dbs.CreateTableDef("表1")  Set...
  • Access中的数据类型  Access中有10数据类型:文本、备注、数值、日期/时间、货币、自动编号、是/否、OLE对象、超级链接...这种类型允许最大255个字符或数字,Access默认的大小是50个字符,而且系统只保存输入到字段
  • C#创建Access数据库的备注字段

    千次阅读 2015-10-28 16:50:00
    具体的用C#创建方式网上一堆,主要是创建备注字段的时候遇到的问题。用ADOX创建的,工程里添加引用dll就不说了,错误的步骤如下(就少了一步):---------------------------------------------------------------...
  • 30、NC系统单据增量开发(增加字段) ** 打开 uap mdp 透视图 左侧的 模块右键点击 ,弹出的菜单,点击【增 量开发实体组件】 弹出增量开发实体组件对话框,选择[Source MDFile],如下图示所示 选择你...
  • 一次把access中的自增字段改成了数值,再改回自增时,提示:输入了数据之后,则不能将任何字段数据类型改为"自动编号"错误,解决方法: 1、先右键表,设计,打开字段设计状态。 2、把你要改的自增字段删除掉...
  • 1.修改字段类型 改成备注:ALTER TABLE #tablename# alter column #columnname# text 改成文本:ALTER TABLE #tablename# alter column #columnname# varchar(20) 改成数字:ALTER TABLE #tablename# alter ...
  • 使用SQL语句增加字段

    2005-12-02 10:36:26
    使用SQL语句增加字段

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 85,003
精华内容 34,001
关键字:

如何在access中增加字段