trying to create view on linked server

[Joss]

Plesae excuse if I make any howlers here, I am on the steep part of the
curve.

I have created a link in SQL to an AS400 but cannot create a view to it in
Access ADP. The SQL that I am using is

'SELECT fieldname FROM as400sver..dbo.linkedTableNAme;

the error that I get when I try to save/view the view is ADO error: line 4:
Incorrect syntax near ';'.

I believe I saw the format with two dots in another post, so assume this may
apply if there is only one database? Anyway, there is only one but I do not
know how to find its name.

The DSN works because I can use it to link to a table from a .MDB.

linked server was made using the following, I added 'EXEC' when it would not
run lines two and three, but the first one gos OK.:

sp_addlinkedserver @server = 'as400sver' , @srvproduct = 'AS400 Client
Access', @provider = 'MSDASQL' , @datasrc = 'AS400SQL', @provstr =
'UID=;PWD=;'

EXEC sp_serveroption @server= 'as400sver' , @optname= 'data access' ,
@optvalue= TRUE

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'as400sver' ,@useself =
true,@rmtuser = '',@rmtpassword = ''

I know that this ran OK, because the output file reads:

(1 row affected)
(1 row affected)
Server added.
(1 row affected)
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)

Not sure exactly what this means, but 'Server added' seems pretty positive.

Can somebody help me please?

thanks,
Jo

 

[Sue Hoegemeier]

If the linked server to your AS400 works and is called
as400sver, execute the following in Query Analyzer:
sp_tables_ex 'as400sver'
Then check the Catalog listed for the table.

To Query, try:
select *
from as400sver.Database.Library.LogicalOrPhysicalFile
or
select *
from Openquery(as400sver, 'Select * from
library.LogicalOrPhysicalFile')

-Sue

 

[Joss]

thanks, Sue. Yes, the format was all wrong. I had to use the catalogue as
database name and library instead of dbo. I am now able to create views
based on the linked tables and use them. The next question though is- How do
I create indexes on the linked tables? I have tried 'CREATE INDEX..' but the
4 part naming (server,catalogue, library,table) does not work. Maximum no.
of prefixes is apparently 2.

thanks for your time,
jo

 

[Sue Hoegemeier]

Use the Openquery function when trying to execute something
(often ddl) that will only allow a two part name. For the
AS400, you'd reference a table with Library.FileName

-Sue

 

[Joss]

Sorry, that went straight over my head. Since posting that question, I have
found that the linking should be done though the Access Project, rather than
through OSQL to the MSDE. Is that correct? If so, the latest attempt is to
use the following code:

Function CreateIndex()
Set dbs = CurrentDb
dbs.Execute "CREATE UNIQUE INDEX ixTABLE ON vTABLE (FIELD1,FIELD2)WITH
DISALLOW NULL;"
dbs.Close
End Function

but, it doesn't work ('object variable or with block variable not set').
Where am I going wrong? The code does work if it is in an .mdb and referring
to linked tables, but not in the .adp with views on table in a linked
server.

Thanks, for help. It is appreciated. As you'll no doubt appreciate, I am new
to this.

Jo

 

[Sue Hoegemeier]

Linking only through the Access Project rather than OSQL or
MSDE....I have no idea what the means. It doesn't make much
sense. A linked server is a linked server. Its a reference
to an external data source that you create in SQL Server.
You can certainly create a linked server using osql or
reference a linked server through OSQL.
In terms of your code and how it works with an mdb, you are
trying to use DAO code to index a table that is seen as
"local" by an mdb. A linked server is nothing like a linked
table in an mdb. Creating indexes is something different
with linked tables and an mdb as well. You aren't using
linked tables when using linked servers.
In the AS400 world, you'd actually want to create a logical
file...that's more along the lines of an indexed physical
file. The AS400 has physical files and logical files. These
are what you see as tables when you linked tables in an MDB.
You really should be contacting the administrator for the
AS400 if you needed to access files with different indexes.
With a linked server and executing ddl, you are actually
trying to change the data source. With linked tables and an
Access mdb, you can create "fake" indexes that are only
local to your link and don't really change the data source.
I think a big part of the problem is that you are seeing
linked servers in SQL Server as the same as linked tables in
an Access mdb. They aren't the same thing at all. You can
get the same type of functionality to some degree and both
are used for external data sources but they are implemented
quite differently.

-Sue

 

[Joss]

Thanks for the detailed reply. You have given me a lot of information there
and I appreciate the time and effort you have spent on it. I have enough
there to go away and work on for now. Thanks again.

Jo