回答編集履歴
2
コードを書き換え
test
CHANGED
|
@@ -1,27 +1,29 @@
|
|
|
1
|
+
```vba
|
|
2
|
+
' iUser ORACLE に接続するユーザ名(テーブルが存在するスキーマ名を兼ねます)
|
|
3
|
+
' iPassword ORACLE に接続するパスワード
|
|
4
|
+
' iDSN ORACLE に接続する DSN
|
|
5
|
+
' iTableName テーブル名
|
|
6
|
+
Sub CreateLinkTable(ByVal iUser As String, ByVal iPassword As String, ByVal iDSN As String, ByVal iTableName As String)
|
|
7
|
+
Dim strConnect As String
|
|
8
|
+
Dim attatchName As String
|
|
1
|
-
|
|
9
|
+
Dim db As Database
|
|
10
|
+
Dim tdf As TableDef
|
|
11
|
+
|
|
12
|
+
strConnect = "ODBC;DSN=" & iDSN & ";Uid=" & iUser & ";Pwd=" & iPassword
|
|
13
|
+
attatchName = iUser & "_" & iTableName
|
|
14
|
+
|
|
15
|
+
Set db = CurrentDb
|
|
16
|
+
|
|
17
|
+
On Error Resume Next
|
|
2
|
-
|
|
18
|
+
db.TableDefs.Delete attatchName
|
|
19
|
+
On Error GoTo 0
|
|
20
|
+
|
|
21
|
+
Set tdf = db.CreateTableDef(attatchName)
|
|
22
|
+
tdf.Connect = strConnect
|
|
23
|
+
tdf.SourceTableName = iUser & "." & iTableName
|
|
24
|
+
tdf.Attributes = tdf.Attributes Or dbAttachSavePWD
|
|
25
|
+
db.TableDefs.Append tdf
|
|
26
|
+
End Sub
|
|
27
|
+
```
|
|
28
|
+
DSN は ODBC アドミニストレータで定義しますが、32bit/64bit に注意してください。
|
|
3
29
|
|
|
4
|
-
```vb
|
|
5
|
-
'iDestDB Oracle に ODBC 接続した Database オブジェクト
|
|
6
|
-
'iMyDb リンクテーブルを作る Database オブジェクト
|
|
7
|
-
'iUser Oracle 接続ユーザ
|
|
8
|
-
'iTableName テーブル名
|
|
9
|
-
Function DoAttach(iDestDB As Database, iMyDb As Database, iUser As String, iTablename As String)
|
|
10
|
-
Dim pAttachName As String
|
|
11
|
-
Dim pMsg As String
|
|
12
|
-
Dim pAttachTBL As TableDef
|
|
13
|
-
|
|
14
|
-
pAttachName = iUser & "_" & iTablename
|
|
15
|
-
Set pAttachTBL = iMyDb.CreateTableDef(pAttachName)
|
|
16
|
-
pAttachTBL.Connect = iDestDB.Connect
|
|
17
|
-
pAttachTBL.SourceTableName = iUser & "." & iTablename
|
|
18
|
-
pAttachTBL.Attributes = pAttachTBL.Attributes Or dbAttachSavePWD
|
|
19
|
-
iMyDb.TableDefs.Append pAttachTBL
|
|
20
|
-
If Err.Number <> 0 Then
|
|
21
|
-
Exit Function
|
|
22
|
-
End If
|
|
23
|
-
DoAttach = True
|
|
24
|
-
On Error GoTo 0
|
|
25
|
-
End Function
|
|
26
|
-
```
|
|
27
|
-
|
1
メッセージ削除
test
CHANGED
|
@@ -18,7 +18,6 @@
|
|
|
18
18
|
pAttachTBL.Attributes = pAttachTBL.Attributes Or dbAttachSavePWD
|
|
19
19
|
iMyDb.TableDefs.Append pAttachTBL
|
|
20
20
|
If Err.Number <> 0 Then
|
|
21
|
-
ShowErrMsg "アタッチテーブル作成エラー", DBEngine.Errors(0).Description
|
|
22
21
|
Exit Function
|
|
23
22
|
End If
|
|
24
23
|
DoAttach = True
|