ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦p¦ó§âEXCEL¸ê®Æ¿é¤J¨ìAccess

¦p¦ó§âEXCEL¸ê®Æ¿é¤J¨ìAccess

¦U¦ì¤j¤j§Ú¥ÎEXCEL VB¼g¤Fµ{¦¡«á¡A¤£À´«ç»ò§âAccess·í§@EXCELªºDB¡A¸Ó¦p¦ó±NEXCEL¸ê®Æ¦Û°Ê©ß¨ìACCESS
·P®¦
hello

¦^´_ 1# tsengs


    ªþ¤WÀÉ®×

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2018-1-2 06:15 ½s¿è

¦^´_ 1# tsengs
´£¨Ñ§A°Ñ¦Ò¡G
  1. Option Explicit   
  2. ' ACCDB ª©
  3. Option Base 1

  4. '  Dim cnn As Object       '  New ADODB.Connection
  5. '  Dim cmd As Object       '  New ADODB.Command
  6. '  Dim rs As Object        '  New ADODB.Recordset
  7. Dim cnn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
  8. Dim strSQL As String
  9. Dim editMode As Boolean
  10. Public dic3 As Object   '  ¦b ThisWorkbook ¤Þ¥Î¡A¦¹³B­n«Å§i Public

  11. '  ³]©w¤Þ¥Î¶µ¥Ø - VBAProject -> ¥i¤Þ¥Îªº¶µ¥Ø (A)¡G
  12. '  Visual Basic For Applications
  13. '  Microsoft Excel 14.0 Object Library
  14. '  OLE Automation
  15. '  Microsoft Office 14.0 Object Library
  16. '  Microsoft Forms 2.0 Object Library
  17. '  Microsoft DAO 3.6 Object Library
  18. '  Microsoft ADO Ext 6.0 for DDL and Security
  19. '  Microsoft ActiveX Data Object 6.1 Library

  20. Sub OpenDB()
  21.     Set cnn = New ADODB.Connection            
  22.     Set rs = New ADODB.Recordset
  23.     Set cmd = New ADODB.Command
  24.     '  ObjectStateEnum Values
  25.     '  Returns a value describing if the connection is open or closed
  26.     '  Specifies whether an object is open or closed, connecting to a data source,
  27.     '  executing a command, or retrieving data.
  28.     '  --------------------------------------------------------------------------
  29.     '  Constant             Value     Description
  30.     '  --------------------------------------------------------------------------
  31.     '  adStateClosed          0       The object is closed
  32.     '  adStateOpen            1       The object is open
  33.     '  adStateConnecting      2       The object is connecting
  34.     '  adStateExecuting       4       The object is executing a command
  35.     '  adStateFetching        8       The rows of the object are being retrieved
  36.     With cnn
  37.         If .State = 1 Then .Close    '  adStateOpen
  38.         
  39.         '  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  40.         '                    ThisWorkbook.Path & Application.PathSeparator & "°O¿ý©ú²Óªí.mdb" & ";"
  41.         '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  42.         .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  43.                           ThisWorkbook.Path & Application.PathSeparator & "°O¿ý©ú²ÓªíÀÉ®×.accdb" & ";"
  44.         .Open
  45.     End With
  46. End Sub

  47. Sub closeRS()
  48.     With rs
  49.         If .State = 1 Then .Close    '  adStateOpen
  50.         '  The CursorLocation property is used to set or return the location of the cursor.
  51.         '  This property can be set to one of the CursorLocationEnum constants listed in the following table.
  52.         '  Enumeratio0n     Value   Description
  53.         '  adUseNone          1     This value indicates no cursor location.
  54.         '                           This value is not supported by the MicrosoftR OLE DB Provider for AS/400 and VSAM.
  55.         '  adUseServer        2     This value indicates that the data provider or driver-supplied cursor is used.
  56.         '  adUseClient        3     This value indicates that a client-side cursor supplied by a local cursor library
  57.         '                           is to be used.
  58.         '  adUseClientBatch   3     For backward compatibility, this value indicates that a client-side cursor supplied by
  59.         '                           a local cursor library is to be used.
  60.         .CursorLocation = 3          '  adUseClient
  61.     End With
  62. End Sub
½Æ»s¥N½X
´£°Ý°ÝÃD®É¡A½Ð¦P®É±N§Aªºªþ¥ó¤@¨Ö±a¤W¡A§_«h§Aªº´£°Ý·|¥Û¨I¤j®üªº¡C

TOP

¸gADODB.Connection ¦Aª½±µ¥ÎSQL ±±¨îDB

TOP

¦^´_ 3# c_c_lai

½Ð°Ý¤j¤j¬O§_¦³ÀÉ®×¥i¨Ñ¤U¸üÆ[¼¯¡A·PÁÂ¥ý¡C

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD