Re: Trouble using ADOX to create linked tables in jet database fro



"Bob Barrows [MVP]" wrote:

sudhi wrote:
<snip>
Dim ADOConnection As New ADODB.Connection

Bad habit. You should avoid the Dim ... As New ... construct. Don't be lazy.
Use the two lines of code:

Dim ADOConnection As ADODB.Connection
Set ADOConnection = New ADODB.Connection

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

Isn't this classic VB? Unless you are using VB.Net, these previous three
lines are missing the Set keyword. When dealing with objects, the Set
keyword must be used.

<snip>
' Set the properties to create the link.
Dim adoxPro As ADOX.Property

This is also an object so this

adoxPro = .Properties("Jet OLEDB:Create Link")
should be this:
Set adoxPro = .Properties("Jet OLEDB:Create Link")
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey",
ADOX.KeyTypeEnum.adKeyPrimary, "Code")

I'm surprised the above line is not causing an exception. For two reasons:
1. It should be:

tblLink.Keys.Append "PrimaryKey", _
ADOX.KeyTypeEnum.adKeyPrimary, "Code"

No parentheses. Only use parentheses when calling a function that returns a
value you intend to consume:
x=somefunction(arg1, arg2)


'Append the table to the Tables collection.
'******The exception occurs on the following
line*********** catDB.Tables.Append(tblLink)

Again, remove the parentheses. Because the method only has a single
argument, it's not as critical in this case:

catDB.Tables.Append tblLink

2. Now to the actual root of your error. When using Append method for the
Keys collection, the first argument needs to be a Key object, not a string:

Dim kPrimary as ADOX.Key
Set kPrimary = ADOX.Key
With kPrimary
.Name="PrimaryKey"
.Type= ADOX.KeyTypeEnum.adKeyPrimary
.Columns.Append "Code"
End With
tblLink.Keys.Append kPrimary

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"






First of all thank you very much for your response

Actually I am using VB.Net not the classic VB

I tried the code you suggested , but i am getting an "Invalid argument".
ComException while I try to add the table to the catalog

My latest code is as follows


Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal
strProviderString As String, ByVal strSourceTbl As String, ByVal
strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table

Dim ADOConnection As ADODB.Connection
ADOConnection = New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & strTargetDB & ";User Id=admin;Password=;")

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

With tblLink

' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB

' Set the properties to create the link.
Dim adoxPro As ADOX.Property

adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True

adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString

adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl

End With

Dim kPrimary As ADOX.Key
kPrimary = New ADOX.Key
With kPrimary
.Name = "PrimaryKey"
.Type = ADOX.KeyTypeEnum.adKeyPrimary
.Columns.Append("Code")
End With
tblLink.Keys.Append(kPrimary)

'Append the table to the Tables collection.
catDB.Tables.Append(tblLink)

catDB = Nothing

End Sub





Any idea why this is happening ?

Thanks in advance
Sudhi






.

Relevant Pages