我们在和数据库连接时,可以使用DSN方式,如果是access这类型的数据库,DSN会要求选择连接的数据库文件路径,也就是说DSN的配置信息中会有路径信息,如果这个数据库文件挪动了位置,虽然内容没变,如果不修改DSN,就会出现连接不上的情况。为了应对这样的情形,需要在wincc项目启动时,做一下初始化,自动根据数据库文件的路径重新生成一下DSN文件。
要实现上面的需求,我做了一个练习,在wincc项目文件夹下建立access数据库文件myDB,其下建立数据表myTable。先手工创建一个ODBC连接,名字叫做myAccess,指向这个新建的数据库。打开注册表文件,找到和myAccess有关信息,导出来,整合成一个.reg文件,内容如下:
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"myAccess"="Microsoft Access Driver (*.mdb)"
"myAccess"="Microsoft Access Driver (*.mdb)"
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess]
"Driver"="C:\\Windows\\system32\\odbcjt32.dll"
"DBQ"="D:\\Wincc73DBTest\\myDB.mdb"
"Description"="这是一个测试"
"DriverId"=dword:00000019
"FIL"="MS Access;"
"SafeTransactions"=dword:00000000
"UID"=""
"Driver"="C:\\Windows\\system32\\odbcjt32.dll"
"DBQ"="D:\\Wincc73DBTest\\myDB.mdb"
"Description"="这是一个测试"
"DriverId"=dword:00000019
"FIL"="MS Access;"
"SafeTransactions"=dword:00000000
"UID"=""
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess\Engines]
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess\Engines\Jet]
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="Yes"
[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\ODBC Data Sources]
"myAccess"="Microsoft Access Driver (*.mdb)"
"myAccess"="Microsoft Access Driver (*.mdb)"
[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\myAccess\Engines]
[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\myAccess\Engines\Jet]
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="Yes"
[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\ODBC Data Sources]
"myAccess"="Microsoft Access Driver (*.mdb)"
"myAccess"="Microsoft Access Driver (*.mdb)"
在wincc的画面上添加一个按钮,按钮的鼠标点击事件添加以下脚本:
Sub OnClick(ByVal Item)
Dim prjpath,prjpath1,fname,fso,objfile,batfile,ws
Set fso = CreateObject("scripting.FileSystemObject")
prjpath=HMIRuntime.ActiveProject.Path
prjpath1=Replace(prjpath,"\","\\")
fname=prjpath1 & "\\CreateODBC.reg"
Set objfile=fso.CreateTextFile(fname,True)'覆盖文件
Set batfile=fso.CreateTextFile(prjpath & "\mybat.bat",True)'覆盖文件
objfile.writeline("Windows Registry Editor Version 5.00")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]")
objfile.writeline("""" & "myAccess" & """" & "=" & """" & "Microsoft Access Driver (*.mdb)" & """")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess]")
objfile.writeline("""" & "Driver" & """" & "=" & """" & "C:\\Windows\\system32\\odbcjt32.dll" & """")
objfile.writeline("""" & "DBQ" & """" & "=" & """" & fname & """")
objfile.writeline("""" & "Description" & """" & "=" & """" & "这是一个测试" & """")
objfile.writeline("""" & "DriverId" & """" & "=dword:00000019")
objfile.writeline("""" & "FIL" & """" & "=" & """" & "MS Access;" & """")
objfile.writeline("""" & "SafeTransactions" & """" & "=dword:00000000")
objfile.writeline("""" & "UID" & """" & "=" & """" & """")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess\Engines]")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess\Engines\Jet]")
objfile.writeline("""" & "ImplicitCommitSync" & """" & "=" & """" & """")
objfile.writeline("""" & "MaxBufferSize" & "=dword:00000800")
objfile.writeline("""" & "PageTimeout" & """" & "=dword:00000005")
objfile.writeline("""" & "Threads" & """" & "=dword:00000003")
objfile.writeline("""" & "UserCommitSync" & """" & "=" & """" & "Yes" & """")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\ODBC Data Sources]")
objfile.writeline("""" & "myAccess" & """" & "=" & """" & "Microsoft Access Driver (*.mdb)" & """")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\myAccess\Engines]")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\myAccess\Engines\Jet]")
objfile.writeline("""" & "ImplicitCommitSync" & """" & "=" & """")
objfile.writeline("""" & "MaxBufferSize" & """" & "=dword:00000800")
objfile.writeline("""" & "PageTimeout" & """" & "=dword:00000005")
objfile.writeline("""" & "Threads" & """" & "=dword:00000003")
objfile.writeline("""" & "UserCommitSync" & """" & "=" & """" & "Yes" & """")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\ODBC Data Sources]")
objfile.writeline("""" & "myAccess" & """" & "=" & """" & "Microsoft Access Driver (*.mdb)" & """")
objfile.close()
Set objfile=Nothing
batfile.writeline("reg import " & prjpath & "\CreateODBC.reg")
batfile.close()
Set batfile=Nothing
Set ws=Createobject("wscript.shell")
ws.run prjpath & "\mybat.bat",0
Set ws=Nothing
Dim prjpath,prjpath1,fname,fso,objfile,batfile,ws
Set fso = CreateObject("scripting.FileSystemObject")
prjpath=HMIRuntime.ActiveProject.Path
prjpath1=Replace(prjpath,"\","\\")
fname=prjpath1 & "\\CreateODBC.reg"
Set objfile=fso.CreateTextFile(fname,True)'覆盖文件
Set batfile=fso.CreateTextFile(prjpath & "\mybat.bat",True)'覆盖文件
objfile.writeline("Windows Registry Editor Version 5.00")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]")
objfile.writeline("""" & "myAccess" & """" & "=" & """" & "Microsoft Access Driver (*.mdb)" & """")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess]")
objfile.writeline("""" & "Driver" & """" & "=" & """" & "C:\\Windows\\system32\\odbcjt32.dll" & """")
objfile.writeline("""" & "DBQ" & """" & "=" & """" & fname & """")
objfile.writeline("""" & "Description" & """" & "=" & """" & "这是一个测试" & """")
objfile.writeline("""" & "DriverId" & """" & "=dword:00000019")
objfile.writeline("""" & "FIL" & """" & "=" & """" & "MS Access;" & """")
objfile.writeline("""" & "SafeTransactions" & """" & "=dword:00000000")
objfile.writeline("""" & "UID" & """" & "=" & """" & """")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess\Engines]")
objfile.writeline("[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\myAccess\Engines\Jet]")
objfile.writeline("""" & "ImplicitCommitSync" & """" & "=" & """" & """")
objfile.writeline("""" & "MaxBufferSize" & "=dword:00000800")
objfile.writeline("""" & "PageTimeout" & """" & "=dword:00000005")
objfile.writeline("""" & "Threads" & """" & "=dword:00000003")
objfile.writeline("""" & "UserCommitSync" & """" & "=" & """" & "Yes" & """")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\ODBC Data Sources]")
objfile.writeline("""" & "myAccess" & """" & "=" & """" & "Microsoft Access Driver (*.mdb)" & """")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\myAccess\Engines]")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\myAccess\Engines\Jet]")
objfile.writeline("""" & "ImplicitCommitSync" & """" & "=" & """")
objfile.writeline("""" & "MaxBufferSize" & """" & "=dword:00000800")
objfile.writeline("""" & "PageTimeout" & """" & "=dword:00000005")
objfile.writeline("""" & "Threads" & """" & "=dword:00000003")
objfile.writeline("""" & "UserCommitSync" & """" & "=" & """" & "Yes" & """")
objfile.writeline("[HKEY_USERS\S-1-5-21-3659616746-1859550896-936897286-1000\Software\ODBC\ODBC.INI\ODBC Data Sources]")
objfile.writeline("""" & "myAccess" & """" & "=" & """" & "Microsoft Access Driver (*.mdb)" & """")
objfile.close()
Set objfile=Nothing
batfile.writeline("reg import " & prjpath & "\CreateODBC.reg")
batfile.close()
Set batfile=Nothing
Set ws=Createobject("wscript.shell")
ws.run prjpath & "\mybat.bat",0
Set ws=Nothing
End Sub
保存,然后删除刚才建立的ODBC连接,运行,点击按钮,可以看到ODBC连接又创建了。退出wincc,把项目程序移动一个位置,再次运行,点击按钮,可以看到ODBC正常创建,随着项目程序位置变动,ODBC连接指向的位置也随着改变。
整个思路就是先生成一个注册表文件,然后生成一个批处理文件,批处理文件用于无条件导入注册表文件,然后隐式执行批处理文件。这些脚本可以放在一个start页面的打开事件中,然后跳转到main页面,start页面做小一点,比如1*1,并作为起始页面,这么一来每次运行项目,首先确保ODBC链接被创建。
今天先对access的动态生成ODBC连接vbs脚本做练习,后面有时间练练sql数据库的。