ADOX连接有安全机制的mdb文件的资料表, 发生错误, 请教高手!

min_summer

我想在当前数据库中取得另一个资料库里的表连接, 目的表的数据数据库是有安全机制的,  可是我运行此代码时, 提示" 文件名无效" , 请问, 这是怎么回事啊,  是我在 tb.Properties("Jet OLEDB:Link Datasource"  这段写错了, 还是??? 请求帮助

Private Sub Command12_Click()
Dim ct As New ADOX.Catalog
Dim tb As New ADOX.Table
ct.ActiveConnection = CurrentProject.Connection
tb.Name = "验收单子表连接"
Set tb.ParentCatalog = ct
tb.Properties("Jet OLEDB:Create Link") = True
tb.Properties("Jet OLEDB:Link Datasource") = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\sorm2\实验\实验财务系统物流系统.mdb;User ID=Maggie  ;Password=123;Jet OLEDB:Database Password='';Jet OLEDB:System database=D:\sorm2\实验\Security.mdw")

tb.Properties("Jet OLEDB:remote table name") = "供应商验收单子表"
ct.Tables.Append tb
Set tb = Nothing: Set ct = Nothing

End Sub



而 连接一个没有安全机制的表完全没有问题, 下面这段代码是可以的" ,
Dim ct As New ADOX.Catalog
Dim tb As New ADOX.Table
ct.ActiveConnection = CurrentProject.Connection
tb.Name = "表1连接"
Set tb.ParentCatalog = ct
tb.Properties("Jet OLEDB:Create Link") = True
tb.Properties("Jet OLEDB:Link Datasource") = "D:\sorm2\实验\123.mdb"
tb.Properties("Jet OLEDB:remote table name") = "表1"
ct.Tables.Append tb
Set tb = Nothing: Set ct = Nothing

是不是在tb.Properties("Jet OLEDB:Link Datasource") =  后面的错了呢, 可是该怎么写呢, 版主帮我看看

[ 本帖最后由 min_summer 于 2007-10-11 10:59 编辑 ]

min_summer

自己顶一下,希望版主们帮帮我!

wwwwa

似乎不行，用ADOX、ADOX、DAO都可以连接有MDW文件的MDB，建立链接表不行

min_summer

这样啊,  多遗憾的一件事情啊,  ---- 多谢你wwwwa, 刚来这个论坛时, 你也指点了我很多查询的技巧, 谢谢你!

在请教你一个问题啊,
我该如何把用ADO查询出来的记录 导入到当前数据库的一个表里啊, 如下面这段代码,我把从有安全机制的数据库里查找到的rst1 的记录集, 我想添加到当前数据库的表2中, 该如何做啊,


Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn1.Open "Data Source=abc.mdb; User ID=Maggie:Password=123;"
      
Dim cmd1 As ADODB.Command
Dim str1 As String
Dim fld1 As ADODB.Field
Set cmd1 = New ADODB.Command
With cmd1
    .ActiveConnection = cnn1
    .CommandText = "SELECT  * form 表1"
    .CommandType = adCmdText
End With
    Set rst1 = cmd1.Execute

Do Until rst1.EOF
str1 = ""
For Each fld1 In rst1.Fields
str1 = str1 & fld1.Value & vbTab
Next fld1
Debug.Print str1
rst1.MoveNext
Loop
rst1.Close

Set fld1 = Nothing
Set rst1 = Nothing
Set cmd1 = Nothing

End Sub

[ 本帖最后由 min_summer 于 2007-10-11 16:07 编辑 ]

andymark

The Properties Collection of the Table Object

The following tables describe both standard and Microsoft Jet provider-specific properties exposed in the Properties collections of the ADOX Table object. Use these settings to specify additional table properties when creating tables.

Table 8. Standard ADO Table Object properties

Property Name Description
Temporary Table A Boolean value (read-only) that specifies whether the table is temporary. For the Microsoft Jet provider, the value is always False, which indicates that the table is created permanently.


Table 9. Provider-specific Table Object properties

Property Name Description
Jet OLEDB:Create Link A Boolean value (read/write) that determines that using the Append method of the ADOX Tables collection should create a link to a remote data source instead of creating a table in the native store.
Jet OLEDB:Exclusive Link A Boolean value (read/write) that determines if a link should be created so that the remote source is opened exclusively. This is only used when the Jet OLEDB:Create Link property is set to True.
Jet OLEDBink Datasource A String value (read/write) that specifies the database to use when creating a linked table that is linked to a table in another Microsoft Jet (Access) database. This is only used when the Jet OLEDB:Create Link property is set to True.
Jet OLEDBink Provider String A String value (read/write) that specifies the Microsoft Jet connection string to be used when connecting to a remote I-ISAM or ODBC data source to create a linked table. This is similar in function to the Extended Properties property used to connect to a remote data source with a Connection object, except that it only applies to a single table. This is only used when the Jet OLEDB:Create Link property is set to True.
Jet OLEDB:Remote Table Name A String value (read/write) that specifies the name of the remote table to be used to create a linked table. The name assigned to the linked table itself can be different from the remote table's name. This is only used when the Jet OLEDB:Create Link property is set to True.
Jet OLEDB:Cache Link Name/Password A Boolean value (read/write) that specifies whether authentication information for the link to a remote ODBC data source should be cached in the Microsoft Jet database. This is only used when the Jet OLEDB:Create Link property is set to True.
Jet OLEDB:Table Validation Rule A String value (read/write) that specifies the expression to be evaluated on a table in order to validate the values of a record before saving the record's changes. This operates in a fashion similar to SQL-92 CHECK clauses. This is also very similar to the Jet OLEDB:Column Validation Rule property, but this rule can refer to multiple fields within the table, allowing for more complicated restrictions.
Jet OLEDB:Table Validation Text A String value (read/write) that specifies the error string to display when the validation rule specified in the Jet OLEDB:Table Validation Rule property is not met.
Jet OLEDB:Table Hidden in Access A Boolean value (read/write) that specifies whether the object should be displayed in the Microsoft Access Database window and other areas of the Access user interface.
This property has no effect on whether the object is displayed through the OLE DB layer. As such, ADO will also display hidden objects, even when running under Microsoft Access.


没找到有支持表的安全机制连接表的相关属性

andymark

不需要追加到本地表,也并不一定要连接表才能查询的

wwwwa

示例：
sConnString = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\zz\sjch\sjch.mdb;User ID=ylwassword=lwgzsylw;Jet OLEDBatabase Password='';Jet OLEDB:System database=d:\zz\sjch\sjch.mdw"
ert.Open sConnString
ert.Execute ("select * into [;database=d:\zz\db1.mdb].fgfg from a")

wwwwa

示例：
sConnString = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\zz\sjch\sjch.mdb;User ID=ylwassword=lwgzsylw;Jet OLEDBatabase Password='';Jet OLEDB:System database=d:\zz\sjch\sjch.mdw"
ert.Open sConnString
ert.Execute ("select * into [;database=d:\zz\db1.mdb].fgfg from a")

wwwwa

示例：
sConnString = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\zz\sjch\sjch.mdb;User ID=ylwassword=lwgzsylw;Jet OLEDBatabase Password='';Jet OLEDB:System database=d:\zz\sjch\sjch.mdw"
ert.Open sConnString
ert.Execute ("select * into [;database=d:\zz\db1.mdb].fgfg from a")

min_summer

  谢谢ANDMARK!