发表于 2009-1-5 19:48
| 
鐢╒B澶囦唤鍜屾仮澶峉QL Server鏁版嵁搴撶殑鏂规硶
鍙戝竷: 2008-6-26 12:17 | 浣滆: admin | 鏌ョ湅: 12娆
鐢╒B澶囦唤鍜屾仮澶峉QL Server鏁版嵁搴撶殑鏂规硶
鎽樿 澶囦唤涓庢仮澶嶆槸鏁版嵁搴撶鐞嗗憳缁存姢鏁版嵁搴撳畨鍏ㄦу拰瀹屾暣鎬х殑閲嶈鎿嶄綔銆傛湰鏂囧垎鏋愬苟浠嬬粛鐢ˋccess澶囦唤SQL鏁版嵁搴撶殑鏂规硶锛屽緢濂藉湴杈惧埌浜嗘暟鎹浠界殑鐩殑銆傚悓鏃讹紝灏盇ccess 鏁版嵁搴撶殑瀹夊叏鎺ュ彛浣滀簡绠鐣ヨ璁猴紝浣垮浠藉悗涓嶈嚧涓уけ鏁版嵁瀹夊叏鎬с傚苟鎻愬嚭浜嗘敼杩涘浠戒笌鎭㈠绛栫暐鐨勪竴浜涘缓璁
銆銆鍏抽敭璇 SQL Server鏁版嵁搴擄紱 Visual Basic 6.0锛 鏁版嵁澶囦唤锛涙暟鎹仮澶嶏紱 Access
銆銆寮曡█
銆銆瀵逛簬鍏抽敭搴旂敤鐨勬暟鎹簱绯荤粺搴旀牴鎹叿浣撶幆澧冨拰鏉′欢鍒惰涓涓畬鍠勫彲琛岀‘淇濈郴缁熷畨鍏ㄧ殑澶囦唤璁″垝,娌℃湁澶囦唤鐨勭郴缁熸槸涓嶅彲闈犮佸嵄闄╃殑锛屽悗鏋滃皢鏄弗閲嶇殑锛岀牬鍧忔х梾姣掋佽鎿嶄綔銆佽嚜鐒剁伨瀹崇瓑绛夐兘鍙兘浼氬鏁版嵁搴撶郴缁熼犳垚闅句互浼伴噺鐨勭牬鍧忋傚挨鍏跺涓涓俊鎭寲绋嬪害杈冮珮鐨勪紒涓氭潵璇达紝鏈鏈変环鍊肩殑璐骇鎴栬灏辨槸浼佷笟鏁版嵁搴撲腑鐨勬暟鎹備竴涓郴缁熶腑鏈閲嶈鐨勬槸澶ч噺鐨勫疄鏃舵暟鎹,娌℃湁澶囦唤鍔熻兘鏄剧劧鏄笉瀹夊叏鍜屼笉瀹屽杽鐨勩傜粡杩囧疄璺垫懜绱㈠嚭Visual Basic 6.0寮鍙戠殑澶囦唤绋嬪簭锛岃兘灏嗘暟鎹簱澶囦唤鍒癆ccess鏁版嵁搴擄紙Access 涓虹洰鍓嶆祦琛岀殑妗岄潰鍨嬫暟鎹簱浜у搧, 鍏锋湁瀛樺偍鎿嶄綔鐏垫椿銆佷究鍒╃瓑浼樼偣, 骞朵笖鍥犲畠涓嶴QL Server 鍚屽睘寰蒋鏁版嵁搴撲骇鍝, 浜岃呰兘寰堝ソ鍦板吋瀹癸級涓紝骞跺Access鏁版嵁搴撹繘琛屽姞瀵嗕互闃叉暟鎹闈炴硶璁块棶銆傝绋嬪簭杩樿兘鐢ˋccess澶囦唤搴撶殑鏁版嵁鎭㈠SQL Server鏁版嵁搴擄紝浠庤屽噺灏戝洜璇搷浣滄崯鍧忔暟鎹唴瀹硅岄犳垚鐨勬崯澶便備娇鐢ㄨ鏂规硶锛岀敤鎴蜂笉椤绘坊鍔犻澶栬澶囷紝鍙』鍦ㄥ師鏈夋湇鍔″櫒涓婅繍琛岃澶囦唤绋嬪簭锛屽叾鐣岄潰绠鍗曟柟渚匡紝閫傚悎鏅氱敤鎴蜂娇鐢ㄣ
澶囦唤涓庢仮澶嶆杩
銆銆澶囦唤鏄暟鎹簱绠＄悊鍛樺畾鏈熷湴灏嗘暣涓暟鎹簱澶嶅埗鍒扮甯︽垨鍙︿竴涓鐩樹笂淇濆瓨璧锋潵鐨勮繃绋嬨傚父鐢ㄧ殑鏁版嵁澶囦唤绫诲瀷鏈:瀹屽叏澶囦唤銆佸樊寮傚浠姐佷簨鍔℃棩蹇楀浠姐佹枃浠舵垨鏂囦欢缁勫浠姐備竴涓氱敤鐨勫浠界瓥鐣ユ槸锛氫互杈冮暱鐨勬椂闂撮棿闅旀墽琛屽畬鍏ㄥ浠斤紝浠ラ備腑鐨勬椂闂撮棿闅旇繘琛屽樊寮傚浠斤紝骞朵互杈冪煭鐨勬椂闂撮棿闅旇繘琛屼簨鍔℃棩蹇楀浠姐傚綋鏁版嵁閬埌鐮村潖鍚庡氨鍙互鍒╃敤澶囦唤鏉ユ仮澶嶆暟鎹簱銆傛仮澶嶆暟鎹簱鏄竴涓杞芥暟鎹簱鐨勫浠斤紝鐒跺悗搴旂敤浜嬪姟鏃ュ織閲嶅缓鐨勮繃绋嬨備竴涓氱敤鐨勬仮澶嶇瓥鐣ユ槸锛氶鍏堟仮澶嶅畬鍏ㄥ浠界殑鏁版嵁锛岀劧鍚庢仮澶嶅樊寮傚浠芥暟鎹紝鏈鍚庢牴鎹簨鍔℃棩蹇楀浠借繘琛屾仮澶嶃
銆銆1銆佸浠芥柟妗
銆銆寮鍙戞墍闇杞欢锛歏isual Basic 6.0 Access澶囦唤绋嬪簭鐨勮璁℃柟娉曪細
銆銆锛1锛 鐢ㄦ帶鍒堕潰鐗堢殑ODBC璁剧疆锛岀粰SQL Server涓殑鏁版嵁搴撴坊鍔犱竴涓暟鎹簮鍚嶇О銆
銆銆锛2锛 鍦ˋccess涓柊寤轰竴涓暟鎹簱锛屽懡鍚嶄负backup.mdb銆
銆銆锛3锛 鐢╒B瀹氫箟涓や釜涓昏杩囩▼锛欰ttach_Table锛堬級鍜孋reate_Table锛堬級銆侫ttach_Table鐨勪綔鐢ㄦ槸灏哠QL Server鏁版嵁搴撲腑鐨勬墍鏈夐渶瑕佸浠界殑琛ㄩ摼鎺ュ埌Access鏁版嵁搴撲腑锛屽湪姝よ繃绋嬩腑瑕佹帓闄ょ郴缁熻〃锛屽洜涓洪偅鏄疭QL Server鑷垱寤虹殑锛岃〃涓棤鐢ㄦ埛鏁版嵁銆侰reate_Table鐨勪綔鐢ㄦ槸鍦╞ackup.mdb涓垱寤虹洰鐨勮〃锛屽嵆鍦ㄨ繖涓浠紸ccess搴撲腑鍒涘缓琛ㄧ敤鏉ヤ繚瀛樻墍鏈夊瓨鍦⊿QL鏁版嵁搴撲腑鐨勭敤鎴锋暟鎹傚彟澶栵紝鍦ㄩ摼鎺ヨ繃绋嬩腑锛岃鍘绘帀SQL Server鑷甫鐨勬嫢鏈夎呭悕绉般
2銆佺▼搴忕殑閫昏緫缁撴瀯
銆銆鈶 閫氳繃绗竴姝ュ垱寤虹殑ODBC鏁版嵁婧愭墦寮瑕佸浠界殑SQL鏁版嵁搴擄紱
銆銆鈶 浣跨敤Attach_Table锛堬級灏嗚鏁版嵁搴撲腑鐨勬墍鏈夌敤鎴疯〃閾炬帴鍒癰ackup.mdb涓紝鎺掗櫎绯荤粺琛ㄧ殑鏂规硶鏄細
For Each tdf in backup_db.TableDefs
銆If (tdf.attributes And dbSystemObject)=0 Then
銆銆//鏉′欢鎴愮珛锛岃鏄庤琛ㄦ槸鐢ㄦ埛瀹氫箟鐨勮〃锛屽彲浠ラ摼鎺ワ紝鍚﹀垯璺宠繃
銆End if
Next
銆銆鈶 浣跨敤Create_Table锛堬級瀵规瘡涓〃鍒涘缓涓庝箣瀵瑰簲鐨勫浠借〃锛屽彇鍚嶅彲閬靛惊濡備笅鍘熷垯锛 鑻ュ師琛ㄥ彨table_name锛 鍒欏浠借〃鍙玝_table_name銆
銆銆鈶 灏唗able_name琛ㄤ腑鐨勬墍鏈夎褰曞鍒跺埌b_table_name涓
銆銆鈶や粠澶囦唤搴撳垹闄ゅSQL鏁版嵁琛ㄧ殑閾炬帴銆
3銆佺▼搴忓師鐮
銆銆閲囩敤寰蒋DAO (Data Access Object) 鏁版嵁妯″瀷, 鎵撳紑Access 鏈湴鏁版嵁搴, 骞惰繛鎺ヤ竴涓閮∣DBC鏁版嵁琛, 鎷疯礉璇ヨ〃缁撴瀯瀹屾垚, 鏈変互涓嬭繃绋嬪潡CopyStru :
Private Sub copyStru ()
銆Set dbsTemp = wrkjet. OpenDatabase (tagFilName)// 閾炬帴琛ㄧ殑杩囩▼
銆For i = 0 To tabN - 1
銆銆Set tdfLinked = dbsTemp. CreateTableDef (鈥渓inkTab鈥)
銆銆tdfLinked. Connect = 鈥淥dbC; DATABASE = xgsbgsys ; UID =
銆銆sa ; PWD = ; DSN = xgsdb ;鈥
銆銆tdfLinked. SourceTableName = tabName (i)
銆銆dbsTemp. TableDefs. Append tdfLinked
銆銆Set temp Tab = dbsTemp. CreateTableDef ()
銆銆temp Tab. Name = tabName (i)
銆銆//鍒涘缓鏂拌〃鐨勮繃绋
銆銆For Each fld In tdfLinked. Fields
銆銆銆Set newFil = temp Tab. CreateField (fld. Name , fld. Type ,fld. Size)
銆銆銆newFil. OrdinalPosition = fld. OrdinalPosition
銆銆銆newFil. Required = fld. Required
銆銆銆temp Tab. Fields. Append newFil
銆銆Next
銆銆//鍒涘缓绱㈠紩
銆銆For Each idx In tdfLinked. Indexes
銆銆銆Set newIdx = temp Tab. CreateIndex ()
銆銆銆With newIdx
銆銆銆銆Name = tabName (i) & 鈥 x鈥
銆銆銆銆Fields = idx. Fields
銆銆銆銆Unique = idx. Unique
銆銆銆銆Primary = idx. Primary
銆銆銆End With
銆銆銆temp Tab. Indexes. Append newIdx
銆銆Next
銆銆dbsTemp. TableDefs. Append temp Tab
銆銆Set temp Tab = Nothing
銆銆dbsTemp. TableDefs. Delete 鈥漧inkTab鈥
銆Next i
銆dbsTemp. Close
銆Set dbsTemp = Nothing
銆wrkjet. Close
銆Set wrkjet = Nothing
End Sub
End Sub
