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

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"


.

Relevant Pages