建立ODBC連結表連SQL原始碼示例

建立ODBC連結表連SQL原始碼示例




Private Sub 建立_Click()
    Dim cnn As String
    Dim dbs As Object    'Database
    Dim tdf As Object    'DAO.TableDef
    Set dbs = CurrentDb
    '下面是ODBC連線SQL字串
    strConnect = "ODBC;DRIVER=SQL Server" & _
                 ";SERVER=192.168.6.128" & _
                 ";DATABASE=BSX" & _
                 ";UID=sa" & _
                 ";PWD=123"




    Set tdf = dbs.CreateTableDef("tblcode_kh_b")  '建立連結表,命名為tblcode_kh_b
    tdf.Connect = strConnect
    tdf.SourceTableName = "tblcode_kh"    'SQL源表
    dbs.TableDefs.Append tdf
    Set dbs = Nothing
    Set tdf = Nothing
    Application.RefreshDatabaseWindow  '重新整理
    MsgBox "建立成功，請檢視錶"
End Sub






Private Sub 刪除_Click()
    DoCmd.DeleteObject acTable, "tblcode_kh_b"
    Application.RefreshDatabaseWindow  '重新整理
    MsgBox "刪除成功"


End Sub