access vba 创建连接表

主要流程是配置odbc字符串

创建连接表

添加主键，否则不能更新

' This function returns the full DSN-less connect string
Private Function ODBC_String() As String
    ' In the real world there are several constants and variable in there
    ODBC_String = "ODBC;DRIVER={SQL Server};SERVER=aaa;DATABASE=bbb;UID=ccc;PWD=ccc;LANGUAGE=us_english;TRUSTED_CONNECTION=No"
End Function

Cmd.TransferDatabase acLink, "ODBC", ODBC_String(), acTable, strTable, strTable, False, True

S = "CREATE INDEX PrimaryKey ON MyViewName (MyPrimaryKeyField) WITH PRIMARY"
DB.Execute S

参考中还提供了管理主键和批量创建主键的代码

参考2提供的方式，后续也要建主键

' Deletes the old table and relinks it
' to the new schema
Sub DeleteRelinkToViewAndRename(loginInfo As AuthInfoz, tblName, tblTarget)

    Dim objAccess As Access.application
    Dim db As DAO.database

    Dim tdf As DAO.TableDef

    ' Open the thing manually first...
    'Shell """C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE""" & " " & loginInfo.dbs
    Set objAccess = GetObject(loginInfo.dbs).application
    Set db = objAccess.CurrentDb

    ' Delete the Linked Table...THIS WORKS UNCOMMENT LATER...
    objAccess.DoCmd.DeleteObject acTable, tblName

    ' Relink the old table to the new schema
    Set tdf = db.CreateTableDef(tblName, 0, tblTarget, "ODBC;DSN=MEDSN;Database=MEDATABASE;Trusted_Connection=Yes")

    ' Add the new table def
    db.TableDefs.Append tdf

End Sub

参考

https://stackoverflow.com/questions/32316669/how-to-programmatically-create-an-odbc-linked-table-to-a-sql-server-view-and-hav

https://stackoverflow.com/questions/38254776/selecting-a-unique-record-identifier-dynamically-with-vba/38266765#38266765

© 2017, 新之助meow. 原创文章转载请注明： 转载自http://www.xinmeow.com