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

[µo°Ý] ½Ð°Ý±±¨îaccess¤ºªº¸ê®Æ­×§ï»P§R°£«ç»ò¼g©O?

[µo°Ý] ½Ð°Ý±±¨îaccess¤ºªº¸ê®Æ­×§ï»P§R°£«ç»ò¼g©O?

½Ð°Ý¤j¤j
°ò¥»¤W§Ú¤w¸g¥i¥H¥Îexcel vba»Paccess¹ï¸Ü°µ·s¼W»P¬d¸ß¡A
¥i¬O¦pªG­n°µ­×§ï»P§R°£«ç»ò¼g©O?¦³¥i¯à¿ì¨ì¶Ü?
°²¦p
´ú¸ÕÀɤºACCESS  ¸ê®Æªí"°e¬~"   ½s¸¹A00002   ªº  ¦ç¸¹¥Ø«e¬O"B00001"
°²³]§Ú­n§ï¦¨"B00002"¡AVBA¸Ó«ç»ò¼g©O

¥H¤U¬O§Úªº´ú¸ÕÀÉ
°e¬~.rar (188.85 KB)

¥»©«³Ì«á¥Ñ algoji3ji3 ©ó 2016-4-17 00:32 ½s¿è

¬ã¨s¥X¨Ó¤F....°Ñ¦Ò¤F³o­Óºô¯¸...https://www.1keydata.com/tw/sql/sqlupdate.html
:L
  1. Sub ­×§ï()

  2.     rc = Range("A2")
  3.     Range("A6:D100").Select
  4.     Selection.ClearContents
  5.     Range("A2").Select
  6. '1.³s½u¥N¸¹
  7.     Dim myCon As ADODB.Connection '¶}¸ê®Æ®w
  8.     Dim mywq As ADODB.Recordset '¶}¸ê®Æªí
  9. '2.«Ø¥ßConnectionª«¥ó
  10.     Set myCon = New ADODB.Connection
  11. '3.³sµ²¸ê®Æ®w
  12.     'ÅX°Êµ{¦¡Provider = Microsoft.ACE.OLEDB.12.0
  13.     'Àɮצì¸mData Source=
  14.     myCon.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\°e¬~.accdb;"
  15. '4.«Ø¥ßRecordsetª«¥ó&³sµ²¸ê®Æªí
  16.     Sql = " UPDATE °e¬~ "
  17.     Sql = Sql & " SET ¦ç¸¹ = 'B00004'"
  18.     Sql = Sql & " WHERE ½s¸¹ = '" & rc & "'"
  19.     Set mywq = myCon.Execute(Sql)

  20.     'mywq.Open Sql, myCon, adOpenDynamic, adLockOptimistic
  21. '5.Ãö³¬³s½u
  22.     myCon.Close
  23. '6.¥á±óª«¥ó
  24.     Set mywq = Nothing
  25.     Set myCon = Nothing
  26.    
  27.     Range("A2") = ""

  28. End Sub
½Æ»s¥N½X
  1. Sub §R°£()

  2.     rc = Range("A2")
  3.     Range("A6:D100").Select
  4.     Selection.ClearContents
  5.     Range("A2").Select
  6. '1.³s½u¥N¸¹
  7.     Dim myCon As ADODB.Connection '¶}¸ê®Æ®w
  8.     Dim mywq As ADODB.Recordset '¶}¸ê®Æªí
  9. '2.«Ø¥ßConnectionª«¥ó
  10.     Set myCon = New ADODB.Connection
  11. '3.³sµ²¸ê®Æ®w
  12.     'ÅX°Êµ{¦¡Provider = Microsoft.ACE.OLEDB.12.0
  13.     'Àɮצì¸mData Source=
  14.     myCon.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\°e¬~.accdb;"
  15. '4.«Ø¥ßRecordsetª«¥ó&³sµ²¸ê®Æªí
  16.     Sql = " DELETE FROM °e¬~ "
  17.     Sql = Sql & " WHERE ½s¸¹ = '" & rc & "'"
  18.     Set mywq = myCon.Execute(Sql)

  19.     'mywq.Open Sql, myCon, adOpenDynamic, adLockOptimistic
  20. '5.Ãö³¬³s½u
  21.     myCon.Close
  22. '6.¥á±óª«¥ó
  23.     Set mywq = Nothing
  24.     Set myCon = Nothing
  25.    
  26.     Range("A2") = ""

  27. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD