Unable to Link table as external datasource in adp

[X-Men]

I encountered a very strange problem. In a adp I try to link to a database
on SQL server through menu item: File->Get External Data-> Link table.
However this morning something strange happened: the "Link table" item
disappeared, and only thing left is "Import".
Does anyone know what's wrong?

 

[Jerry Boone]

Table linking is only supported in Access 2003 for ADP projects, unlike MDB
situations where linking is availabe since 2.0 or 95 (can't remember that
far back). Even then, it is limited by only allowing you to have read only
links to sources other than those OLEDB sources that can be directly linked
by Sql server.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

 

[X-Men]

Thank you for your information Jerry. I found it in the Access help later.

The company just assigned me a new pc which has MS office 2000 installed.
But this ADP was programmed using my own laptop, which has OfficeXP. Then I
copied the ADP file to the new PC.....

It's amazing the tables I linked using OfficeXP still work in Office2000,
but no longer I can link to any new tables......

 

[Jerry Boone]

I'm confused as to why you are linking the tables? Are they not on the same
sql server?

If you are using an ADP, the connection will show you the tables in the
database you selected in the connection parameters then display only the
ones you have security to view. If you have a DBA or something, you might
want to check with them.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

 

[Trevor]

I have the same problem,
Yes I am connected to the server and I can view all the
tables in the database. But I would like to link to a
different database on a different server all together.

I want to share data between a database on the local sql
server and data from the sql server on a different network.

Please help.

 

[Jerry Boone]

You need to open Enterprise Manager and do this:

Goto Security-Linked Servers
Right click - New linked server
Fill in those properties and you will have a link.

Then when you need to call on data from that server, use the prefix...

mylinkedserver.mylinkeddatabase.dbo.mylinkedtable

For instance, if I had a linked server named NewYork1, the database on that
server was named Sales, I would do this...

Select * from NewYork1.Sales.dbo.Salesmen

I find it is sometimes easier if you make a view on your local database that
selects the data. At that point you have a middle layer where you can do
some adjustment in case the table design changes on the other server. It
also saves you from having to type the entire linked server qualifier on the
queries you write by allowing you to simply use the view name instead.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access