1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
| ' Création de tables liées dans une BD ciblée (oTargetDB).
' La liste des tables est issue de la source de données sDataSource dans une BD source.
'
' La structure de sDataSource doit être conforme à celle attendue:
' Au moins 3 champs dont les noms sont listés ci-dessous
' * CONNECT_STRING
' * LOCAL_NAME
' * REMOTE_NAME
' * PSEUDO_INDEX_NAME
' * PSEUDO_INDEX_FIELDS
'
Public Sub CreateLinkedTables(sDataSource As String, _
oSourceDB As DAO.Database, _
oTargetDB As DAO.Database, _
Optional bDeleteBefore As Boolean = False)
Dim oRS As DAO.Recordset, oTD As DAO.TableDef
Dim sConnect As String, sLocal As String, sRemote As String
Dim sIndex As String, sFields As String, SQL As String
On Error GoTo 0
Set oRS = oSourceDB.OpenRecordset(sDataSource, dbOpenSnapshot)
Do Until oRS.EOF
sConnect = oRS!CONNECT_STRING
sLocal = oRS!LOCAL_NAME
sRemote = oRS!REMOTE_NAME
sIndex = Nz(oRS!PSEUDO_INDEX_NAME, vbNullString)
sFields = Nz(oRS!PSEUDO_INDEX_FIELDS, vbNullString)
' Effacer d'éventuelles tables attachées + Resume next
If bDeleteBefore Then
On Error Resume Next
oTargetDB.TableDefs.Delete sLocal
oTargetDB.TableDefs.Refresh
End If
On Error GoTo 0
If sConnect Like "ODBC;*" Then
Set oTD = oTargetDB.CreateTableDef(sLocal, dbAttachSavePWD, sRemote, sConnect)
Else
Set oTD = oTargetDB.CreateTableDef(sLocal)
oTD.SourceTableName = sRemote
oTD.Connect = sConnect
End If
oTargetDB.TableDefs.Append oTD
Set oTD = Nothing
If LenB(sIndex) > 0 And LenB(sFields) > 0 Then
SQL = "CREATE INDEX [" & sIndex & "] ON [" & sLocal & "] (" & sFields & ") WITH PRIMARY"
oTargetDB.Execute SQL
End If
oRS.MoveNext
Loop
oTargetDB.TableDefs.Refresh
oRS.Close
Set oRS = Nothing
End Sub |
Partager