creating linked-tables in Access by code

[ba_sagar]

I need to create a linked table from a table in SQL-server database.
I know it can be done by way of creating a DSN first and then linking using the code

tabledef1.name = "A"
tableDef1.Soucertablename="SQLA"
tabledef1.Connect="ODBC;Driver=SQL Server;......"

But I need to do all this DSN-less..Is it possible.

Please comment on this.I need this info urgent.
Thanks in advance

Anand Sagar
ba_sagar@yahoo.com

[Iouri]

Try this. It will link table to Access db

'ref ADO and ADOX

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnAccess As New ADODB.Connection

Private Sub cmdDeleteLinkedTable_Click()

cat.Tables.Delete "lnkAuthors"
MsgBox "Link Deleted"

End Sub

Private Sub Form_Load()

cmdDeleteLinkedTable.Caption = "Delete Linked Table"
cmdLinkTable.Caption = "Link Table"
optNoRefresh.Caption = "Don't Refresh Link"
optRefresh.Caption = "Refresh Link"

cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb;"
cat.ActiveConnection = cnAccess

End Sub

Private Sub Form_Unload(Cancel As Integer)

'Clean up code
Set cat = Nothing

cnAccess.Close
Set cnAccess = Nothing

End Sub

Private Sub cmdLinkTable_Click()

Dim rs As New ADODB.Recordset

tbl.Name = "lnkAuthors"
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<servername>;database=pubs;uid=sa;pwd=;"
tbl.Properties("Jet OLEDB:Remote Table Name") = "Authors"
tbl.Properties("Jet OLEDB:Create Link") = True

cat.Tables.Append tbl

If optNoRefresh.Value = False Then
'Executing the following line will refresh the link and the table will be Updateable.
tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<servername>;database=pubs;uid=sa;pwd=;"
End If

rs.CursorLocation = adUseClient
rs.Open "Select * from lnkAuthors", cnAccess, adOpenStatic, adLockOptimistic

rs!State = "WA"
rs.Update 'The error occurs here if the link is not refreshed.

MsgBox "Recordset updated"

rs.Close
Set rs = Nothing

End Sub

Iouri Boutchkine
iouri@hotsheet.com