visual basic数据库开发疑难问题解.txt

Visual Basic数据库开发疑难问题解 。

TABLE_NAME 
COLUMN_NAME 
adSchemaPrimaryKeys PK_TABLE_CATALOG 
PK_TABLE_SCHEMA 
PK_TABLE_NAME 
adSchemaProcedureColumns PROCEDURE_CATALOG 
PROCEDURE_SCHEMA 
PROCEDURE_NAME 
COLUMN_NAME 
adSchemaProcedureParameters PROCEDURE_CATALOG 
PROCEDURE_SCHEMA 
PROCEDURE_NAME 
PARAMTER_NAME 
adSchemaProcedures PROCEDURE_CATALOG 
PROCEDURE_SCHEMA 
PROCEDURE_NAME 
PROCEDURE_TYPE 
adSchemaProviderSpecific 参见说明 
adSchemaProviderTypes DATA_TYPE 
BEST_MATCH 
adSchemaReferentialConstraints CONSTRAINT_CATALOG 
CONSTRAINT_SCHEMA 
CONSTRAINT_NAME 
adSchemaSchemata CATALOG_NAME 
SCHEMA_NAME 
SCHEMA_OWNER 
adSchemaSQLLanguages <无> 
adSchemaStatistics TABLE_CATALOG 
TABLE_SCHEMA 
TABLE_NAME 
adSchemaTableConstraints CONSTRAINT_CATALOG 
CONSTRAINT_SCHEMA 
CONSTRAINT_NAME 
TABLE_CATALOG 
TABLE_SCHEMA 
TABLE_NAME 
CONSTRAINT_TYPE 
adSchemaTablePrivileges TABLE_CATALOG 
TABLE_SCHEMA 
TABLE_NAME 
GRANTOR 
GRANTEE 
adSchemaTables TABLE_CATALOG 
TABLE_SCHEMA 
TABLE_NAME 
TABLE_TYPE 
adSchemaTranslations TRANSLATION_CATALOG 
TRANSLATION_SCHEMA 
TRANSLATION_NAME 
adSchemaUsagePrivileges OBJECT_CATALOG 
OBJECT_SCHEMA 
OBJECT_NAME 
OBJECT_TYPE 
GRANTOR 
GRANTEE 
adSchemaViewColumnUsage VIEW_CATALOG 
VIEW_SCHEMA 
VIEW_NAME 
adSchemaViewTableUsage VIEW_CATALOG 
VIEW_SCHEMA 
VIEW_NAME 
adSchemaViews TABLE_CATALOG 
TABLE_SCHEMA 
TABLE_NAME 


SchemaID OLE DB 规范没有定义用于提供者模式查询的 GUID。如果 QueryType 设置为 adSchemaProviderSpecific，则需要该参数，否则不使用它。 

说明 

OpenSchema 方法返回与数据源有关的信息，例如关于服务器上的表以及表中的列等信息。 

Criteria 参数是可用于限制模式查询结果的值数组。每个模式查询有它支持的不同参数集。实际模式由 IDBSchemaRowset 接口下的 OLE DB 规范定义。ADO 中所支持的参数集已在上面列出。 

如果提供者定义未在上面列出的非标准模式查询，则常量 adSchemaProviderSpecific 将用于 QueryType 参数。在使用该常量时需要 SchemaID 参数传递模式查询的 GUID 以用于执行。如果 QueryType 设置为 adSchemaProviderSpecific 但是没有提供 SchemaID，将导致错误。 

提供者不需要支持所有的 OLE DB 标准模式查询，只有 adSchemaTables、adSchemaColumns 和 adSchemaProviderTypes 是 OLE DB 规范需要的。但是对于这些模式查询，提供者不需要支持上面列出的 Criteria 条件约束。 

远程数据服务用法 OpenSchema 方法在客户端 Connection 对象上无效。 

注意 在 Visual Basic 中，在由 Connection 对象的 OpenSchema 方法所返回的 Recordset 中有 4 字节无符号整型 (DBTYPE UI4) 的列无法与其他变量比较。有关 OLE DB 数据类型的详细信息，请参阅“Microsoft OLE DB 程序员参考”的第十章和附录 A。 

--------------------------------------------------------------- 

OpenSchema 方法范例 
该范例使用 OpenSchema 方法显示 Pubs 数据库内每个表的名称和类型。 

Public Sub OpenSchemaX() 

Dim cnn1 As ADODB.Connection 
Dim rstSchema As ADODB.Recordset 
Dim strCnn As String 

Set cnn1 = New ADODB.Connection 
strCnn = "Provider=sqloledb;" & _ 
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " 
cnn1.Open strCnn 

Set rstSchema = cnn1.OpenSchema(adSchemaTables) 

Do Until rstSchema.EOF 
Debug.Print "Table name: " & _ 
rstSchema!TABLE_NAME & vbCr & _ 
"Table type: " & rstSchema!TABLE_TYPE & vbCr 
rstSchema.MoveNext 
Loop 
rstSchema.Close 

cnn1.Close 

End Sub 

该范例在 OpenSchema 方法的 Criteria 参数中指定 TABLE_TYPE 查询约束。因此，只返回在 Pubs 数据库中指定视图的模式信息。然后该范例显示每个表的名称和类型。 

Public Sub OpenSchemaX2() 

Dim cnn2 As ADODB.Connection 
Dim rstSchema As ADODB.Recordset 
Dim strCnn As String 

Set cnn2 = New ADODB.Connection 
strCnn = "Provider=sqloledb;" & _ 
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " 
cnn2.Open strCnn 

Set rstSchema = cnn2.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW")) 

Do Until rstSchema.EOF 
Debug.Print "Table name: " & _ 
rstSchema!TABLE_NAME & vbCr & _ 
"Table type: " & rstSchema!TABLE_TYPE & vbCr 
rstSchema.MoveNext 
Loop 
rstSchema.Close 

cnn2.Close 

End Sub 


--------------------------------------------------------------- 

ado的recordset 对象在返回记录集时，同时也有各字段的名字和类型。 
具体是：recordset.Fields.Item(i).Name 
recordset.Fields.Item(i).Type 
类型是枚举型，你可以查对应的值 
还有其它属性： 
recordset.Fields.Item(i).DefinedSize 字段的定义宽度 
recordset.Fields.Item(i).ActualSize 字段的实际宽度 

.... 
--------------------------------------------------------------- 

其他我不想再说了，看我用过的： 
for i=0 to rs.recordcount-1 
print rs.Fields(i).Name & " " & rs.Fields(i).Type "(" & rs.Fields(i).ActualSize & ")" 
next 


--------------------------------------------------------------- 



Private Sub Command1_Click() 
Dim Cn As New ADODB.Connection 
Dim Rs_Table As New ADODB.Recordset 
Dim Rs_Colums As New ADODB.Recordset 

With Cn '定义连接 
.CursorLocation = adUseClient 
.Provider = "sqloledb" 
.Properties("Data Source").Value = "LIHG" 
.Properties("Initial Catalog").Value = "NorthWind" 
.Properties("User ID") = "sa" 
.Properties("Password") = "sa" 
.Properties("prompt") = adPromptNever 
.ConnectionTimeout = 15 
.Open 

If .State = adStateOpen Then 
Rs_Table.CursorLocation = adUseClient '得到所有表名 
Rs_Table.Open "SELECT name From sysobjects WHERE xtype = 'u'", Cn, adOpenDynamic, adLockReadOnly 
Rs_Table.MoveFirst 
Do While Not Rs_Table.EOF 
Debug.Print Rs_Table.Fields("name") 
Rs_Colums.CursorLocation = adUseClient 
Rs_Colums.Open "select * from [" & Rs_Table.Fields("name") & "]", Cn, adOpenStatic, adLockReadOnly 
For I = 0 To Rs_Colums.Fields.Count - 1 ' 循环所有列 
Debug.Print Rs_Colums.Fields(I).Name '字段名 
Debug.Print FieldType(Rs_Colums.Fields(I).Type) '字段类型 
Debug.Print Rs_Colums.Fields(I).DefinedSize '宽度 
Next 
Rs_Colums.Close  
问：怎么把Sqlserver 的数据转换并输出成Xml格式文件？

　　解决方法:

SQL Server7的用法 

Private Sub Command1_Click() 
Dim strmResults As New ADODB.Stream 
Dim cn As New ADODB.Connection 
Dim cmd As New ADODB.Command 
Dim cnstr As String 
cnstr = "Provider = SQLOLEDB.1;Password=121231;Persist Security Info=True;User ID=sa;Initial Catalog=zydb;Data Source=wzsswz" 
cn.ConnectionString = cnstr 
cn.Open 
Set cmd.ActiveConnection = cn 
cmd.CommandText = "select * from company for XML auto" 
strmResults.Open 
cmd.Properties("Output Stream").Value = strmResults 
cmd.Properties("xml root") = "root" 
cmd.Execute , , adExecuteStream 
strmResults.Position = 0 
strmResults.SaveToFile App.Path & "\outfile.xml" 
strmResults.Close 
Set strmResults = Nothing 
End Sub 

　　2000的用select * from company for XML auto就可以了 

Option Explicit 

Dim conDB As New ADODB.Connection 

Private Sub cmdGetData_Click() 
' Get inventory data 
Dim rsInventory As New ADODB.Recordset 
Dim stmInventory As New ADODB.Stream 
' set db connection 
conDB.ConnectionString = ("Provider=SQLOLEDB.1;Server=Pinatubo;User ID=training; PWD=password;Initial Catalog=XMLTraining;") 
conDB.Open 

' set recordset 
rsInventory.Open "tblInventory", conDB, adOpenDynamic, adLockOptimistic 
rsInventory.Save stmInventory, adPersistXML 
' Save ADO XML to file 
stmInventory.SaveToFile App.Path & "\inventory.xml", adSaveCreateOverWrite 
' dispaly xml file to UI 
wbXML.Navigate App.Path & "\inventory.xml" 
' close database connection 
conDB.Close 
End Sub 

Private Sub cmdGetDetail_Click() 
' Get product detail 
Dim rsProductDetail As New ADODB.Recordset 
Dim stmProduct As New ADODB.Stream 

' Set connection string 
conDB.ConnectionString = ("Provider=SQLOLEDB.1;Server=Pinatubo;User ID=training; PWD=password;Initial Catalog=XMLTraining;") 
conDB.Open 

' set recordset 
rsProductDetail.Open "SELECT *, tblProductDetail.* FROM tblInventory INNER JOIN " & _ 
"tblProductDetail ON tblInventory.fldProductID = tblProductDetail." & _ 
"fldProductID", conDB, adOpenDynamic, adPersistXML 
rsProductDetail.Save stmProduct, adPersistXML 

' Save ADO XML file to disk 
stmProduct.SaveToFile App.Path & "\ProductDetail.xml", adSaveCreateOverWrite 
' Load xml document in UI 
wbXML.Navigate App.Path & "\ProductDetail.xml" 
' Close database connection string 
conDB.Close 
End Sub 

　　在.NET中就方面多了 

select * from table for xml auto 　　  

　　问：如何终止一个被其它进程打开的Access文件？

　　解决方法：

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long 
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long 
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long 
Const SW_SHOWNORMAL = 1 
Const WM_CLOSE = &H10 
Const gcClassnameMSWord = "OpusApp" 
Const gcClassnameMSExcel = "XLMAIN" 
Const gcClassnameMSIExplorer = "IEFrame" 
Const gcClassnameMSVBasic = "wndclass_desked_gsk" 
Const gcClassnameNotePad = "Notepad" 
Const gcClassnameMyVBApp = "ThunderForm" 
Private Sub Form_Load() 
Dim WinWnd As Long, Ret As String, RetVal As Long, lpClassName As String 
'Search the window 
WinWnd = FindWindow(vbNullString, "Microsoft Access") 
If WinWnd = 0 Then MsgBox "Couldn't find the window ...": Exit Sub 
'Show the window 
ShowWindow WinWnd, SW_SHOWNORMAL 
'Post a message to the window to close itself 
PostMessage WinWnd, WM_CLOSE, 0&, 0& 
End Sub