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

[µo°Ý] Excel¤u§@ªí³æ»PAccess¸ê®Æ®wªºVBA¤¬°Ê°ÝÃD

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-12-26 10:50 ½s¿è

¦^´_ 19# JEAN
¦Ü©ó "¾÷²¼§@·~"¸Ì­±ªºªí®æ³£¤£¯à¥Î
½Ð°Ñ¦Òªþ¥ó¡G
¾÷²¼°O¿ý©ú²Óªí.rar (11.43 KB)
¾÷²¼°O¿ý©ú²ÓªíÀÉ®×.rar (17.9 KB)
¦pªG©p¨ººÝ¬O¶}±Ò Access MDF ªºÀÉ®× (Office 2003) ¡G
  1. Sub OpenDB()
  2.     If ExcelData.Value = True Then    '  [data$]
  3.         Set cnn = CreateObject("ADODB.Connection")
  4.         Set rs = CreateObject("ADODB.Recordset")
  5.         Set cmd = CreateObject("ADODB.Command")
  6.     Else                              '  ¾÷²¼¬ö¿ý
  7.         Set cnn = New ADODB.Connection
  8.         Set rs = New ADODB.Recordset
  9.         Set cmd = New ADODB.Command
  10.     End If

  11.     With cnn
  12.         If .State = 1 Then .Close    '  adStateOpen
  13.         
  14.         If ExcelData.Value = True Then
  15.             .ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
  16.                                 ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
  17.         Else
  18.             '  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  19.             '                    ThisWorkbook.Path & Application.PathSeparator & "¾÷²¼°O¿ý©ú²Óªí.mdb" & ";"
  20.             '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  21.             .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  22.                               ThisWorkbook.Path & Application.PathSeparator & "¾÷²¼°O¿ý©ú²ÓªíÀÉ®×.accdb" & ";"
  23.         End If
  24.         .Open
  25.     End With
  26. End Sub
½Æ»s¥N½X
«h±N¤W¦Cµ{¦¡½X¤§ Else ªº³¡¤À§ï¦¨¦p¤U¡G
  1.         Else
  2.            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  3.                              ThisWorkbook.Path & Application.PathSeparator & "¾÷²¼°O¿ý©ú²Óªí.mdb" & ";"
  4.              '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  5.             '   .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  6.             '                   ThisWorkbook.Path & Application.PathSeparator & "¾÷²¼°O¿ý©ú²ÓªíÀÉ®×.accdb" & ";"
  7.         End If
½Æ»s¥N½X
§Y¥i¡C
¦¹µ{¦¡½X¥i¥H³B²z MDF (For Excel 2003) ©ÎªÌ¬O ACCDB (For Excel 2007¡B2010) µ¥¸ê®Æ®w®æ¦¡¡C

TOP

¦^´_ 21# c_c_lai


±z¦n:

§A°µªº¾÷²¼§@·~,°µªº«Ü¦n¡C ;P
¤£¹L§Ú¶}±Ò³£¤£¯à©l¥Î....
2013-12-26 ¤U¤È 01-41-55.jpg
2013-12-26 13:50

TOP

¦^´_ 22# JEAN
¤@¡B ±N 2014¦~¾÷²¼°O¿ý.xls¡B¾÷²¼°O¿ý©ú²Óªí.mdb (§í©Î¬O ¾÷²¼°O¿ý©ú²ÓªíÀÉ®×.accdb)
     ¥þ³¡³£©ñ¸m©ó¦P¤@¥Ø¿ý¤U¡C ¨Ò¦p¡G §Ú¬°¤F¤è«K´ú¸Õ°_¨£¡A¦Ó±N¥¦­Ì¥þ¼Æ©ñ¦b¤@­Ó¦W¬°
     "2014¦~¾÷²¼°O¿ý" D ºÏºÐ¾÷ªº¦¸¥Ø¿ý¤U¡C
¤G¡B ¦pªG©p¨ººÝ¬O¶}±Ò Access MDF ªºÀÉ®× (Office 2003)¡A­þ»ò©p¥²¶·¥ý±N 21# ©Ò»¡ªº
     µ{¦¡½X (Else ³¡¤À) ¥ý¦æ­×¥¿¡C
     ¥Ø«e§Ú¬O¹w³]¶}±Òªº¬O Access ACCDB ªº¸ê®Æ®w¡A¦pªG­n¨Ï¥Î Access MDF ªºÀɮסA½Ð±N
     ACCDB ªº³¡¤À¤©¥H Mark °_¨Ó¡A¦Ó±N­ì¥» Mark ¤§ MDF ªº³¡¤À¥´¶} (²¾°£ Mark ' )¡A
     ¦p¦¹§Y¥i¨Ï¥Î MDF ªºÀɮפF¡C
     (¨âºØ¸ê®Æ®w§Ú³£¤@»ô©ñ¸m©ó D:\2014¦~¾÷²¼°O¿ý\ ¥Ø¿ý¤U¡A¥H¤è«K´ú¸Õ¤ÎÆ[¹î¸ê®ÆÅܤÆ)
¤T¡B ÂI¿ï¸Ó¦¸¥Ø¿ý D:\2014¦~¾÷²¼°O¿ý\2014¦~¾÷²¼°O¿ý.xls «K¥i°õ¦æ¡C
00.png
2013-12-26 15:45

01.png
2013-12-26 15:46

TOP

¦^´_ 22# JEAN
02.png
2013-12-26 15:47

03.png
2013-12-26 15:47

TOP

¦^´_ 22# JEAN
04.png
2013-12-26 15:56

05.png
2013-12-26 15:56

TOP

¦^´_ 25# c_c_lai


±z¦n:
ÁÂÁ±zªº¾÷²¼§@·~,½Ð°Ý§Ú¬O§_¥i¥H§ï¬°§â¦W¦r¥Î¿ï¶µ¤è¦¡¿é¤J.....µM«á¦Û°Ê±a¤J"³æ¦ì"³o­ÓÄæ¦ì¡C
´N¹³§Ú"¾÷²¼"¸Ì­±ªº¦W¦r....·|¦Û°Ê±a¤J"³æ¦ì"¡C

½Ð°ÝSQL¬Oµn¤J¨ìACCESSªº·N«ä¶Ü??  ¥i¬O§Ú¥Î³£¤£¦æ~~

TOP

¦^´_  c_c_lai


±z¦n:
ÁÂÁ±zªº¾÷²¼§@·~,½Ð°Ý§Ú¬O§_¥i¥H§ï¬°§â¦W¦r¥Î¿ï¶µ¤è¦¡¿é¤J.....µM«á¦Û°Ê±a¤J ...
JEAN µoªí©ó 2013-12-27 13:32

·íµM¥i¦æ¡A¥u­n¬O VBA »yªk¯à³B¸Ìªº¡A´X¥G³£¦³¹ê²{¤§¥i¯à¡C
¥t¥~§ÚºK¿ý¤F SQL ¤@µüªº¸ÑÄÀ¡G
µ²ºc¤Æ¬d¸ß»y¨¥¡]Structured Query Language¡AÁY¼g¬°SQL¡^¡A¤@ºØµ{¦¡»y¨¥¡A¥Î©ó¸ê®Æ®w¤¤ªº¼Ð·Ç¸ê®Æ¬d¸ß»y¨¥¡AIBM¤½¥q³Ì¦­¨Ï¥Î¦b¨ä¶}µoªº¸ê®Æ®w¨t²Î¤¤¡C1986¦~10¤ë¡A¬ü°ê°ê®a¼Ð·Ç¾Ç·|¡]ANSI¡^¹ïSQL¶i¦æ³W½d«á¡A¥H¦¹§@¬°ÃöÁp¦¡¸ê®Æ®wºÞ²z¨t²Îªº¼Ð·Ç»y¨¥¡]ANSI X3. 135-1986¡^¡A1987¦~±o¨ì°ê»Ú¼Ð·Ç²Õ´ªº¤ä´©¤U¦¨¬°°ê»Ú¼Ð·Ç¡C¤£¹L¦UºØ³q¦æªº¸ê®Æ®w¨t²Î¦b¨ä¹ê½î¹Lµ{¤¤³£¹ïSQL³W½d§@¤F¬Y¨Ç½s§ï©MÂX¥R¡C©Ò¥H¡A¹ê»Ú¤W¤£¦P¸ê®Æ®w¨t²Î¤§¶¡ªºSQL¤£¯à§¹¥þ¬Û¤¬³q¥Î¡C
¬Ý§¹¤F³o¬q»¡©ú¡A§AÀ³¸Ó«K¯à¤F¸Ñ¨ì SQL ¥¦§êºtªº¥u¬O¤@¯ëµ{¦¡»y¨¥»P¸ê®Æ®w¶¡ªº¤@­Ó¤¶­±»y¨¥¡C¨Ò¦p¡G
¥»µ{¦¡½X¸Ìªº¡G
  1.     If ExcelData.Value = True Then
  2.         '  strSQL = "Select Distinct [ñÃÒ¤º®e] From [ñÃÒ¶µ¥Ø$] Order by [ñÃÒ¤º®e]"
  3.         strSQL = "Select * From [ñÃÒ¶µ¥Ø$] Order by [ñÃÒ¤º®e]"
  4.     Else
  5.         '  strSQL = "Select Distinct ñÃÒ¤º®e From ñÃÒ¶µ¥Ø Order by ñÃÒ¤º®e"
  6.         strSQL = "Select * From ñÃÒ¶µ¥Ø Order by ñÃÒ¤º®e"
  7.     End If
  8.    
  9.     '  closeRS
  10.     OpenDB
  11.   
  12.     rs.Open strSQL, cnn, 1, 3     '  adOpenKeyset, adLockOptimistic
  13.     If rs.RecordCount > 0 Then
  14.        .
  15.        .
  16.     End If
  17.       .
½Æ»s¥N½X
µ¥»P¸ê®Æ®w¦s¨ú¦³Ãö»yªk¬ÒÄݤ§¡C

TOP

¦^´_ 26# JEAN
¤S¡B©p»¡¥Î³£¤£¦æ¡H
¦¹¸Ü«ç»¡¡H
©p¬O«üª½±µÂI¿ï "¾÷²¼°O¿ý©ú²Óªí.mdb" ³£µLªk¶i¤J¨ì Access ¶Ü¡H

TOP

¦^´_ 28# c_c_lai


  ±z¦n:

§Úªº·N«ä¬O»¡:¦pªG¿ï¾ÜSQL DATA¤è¦¡ ¿é¤J¸ê®Æ....¥LªºÀx¦s©ú²ÓªíÀ³¸Ó¬O¦bACCESS§a....¥i¬O§Ú¶}±ÒACCESS©ú²Óªí,¥²¨S¦³§Ú¿é¤Jªº¸ê®Æ¡C

§Ú·|·Q¥Î¨ìACCESS¸ê®Æ®w¸Ì­±¬O¤è«K¬d¸ß,¦Ó¥B¥i¥H¤@ª½¼W¥[¦~«×...Ä´¦p©ú¦~¬O2014¦~¥Î§¹,§ÚÁÙ¥i¥H¦bACCESS©ú²Óªíª½±µ¦b¥[¤W2015¦~«×´N¥i¥H¤F¡C

³o¼Ë§Ú´N¤£¥Î¨C¦~·Q­n¦p¦ó¥h°µ,¦Ó¥B§Ú¥´ºâ¤µ¦~§âªí®æ·d©w¦n§â©Ò¦³ªº¯ÊÂI©MÀuÂI¾ã¦X¥X¨Ó¡C  ¥H«á¤]¥i¥H´£¨Ñ¨ä¥Lºô¤Í°µ¨Ï¥Î¡C  

¥t¥~¯uªº«Ü·PÁ c_c_lai À°§U¤~¯à§¹¦¨ªí®æ¡C

TOP

¦^´_  c_c_lai


±z¦n:
ÁÂÁ±zªº¾÷²¼§@·~,½Ð°Ý§Ú¬O§_¥i¥H§ï¬°§â¦W¦r¥Î¿ï¶µ¤è¦¡¿é¤J.....µM«á¦Û°Ê±a¤J ...
JEAN µoªí©ó 2013-12-27 13:32


Private Sub DeleteData_Click()
    Dim nCode As Range, ret As Boolean
   
    With Sheets("data")
        Set nCode = .[B:B].Find(CallID.Text, , , 1)
        .Rows(Val(Mid(nCode.Address, 4))).EntireRow.Delete Shift:=xlUp
    End With
   
    ret = ExcelData.Value
    ExcelData.Value = False
   
    closeRS
    OpenDB
   
    strSQL = "DELETE FROM ¾÷²¼°O¿ý WHERE ¦W¦r = '" & CallID.Text & "'"
    cmd.CommandText = strSQL
   
    cmd.ActiveConnection = cnn
    cmd.Execute
    cnn.Close
   
    Confirm.Enabled = True
    ExcelData.Value = ret
    ResetData_Click
End Sub


½Ð°Ý¬O§ï³oÃä¶Ü?

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD