Connecting to SQL Server?

[Bob]

I am connecting to an SQL server d/b. THe d/b was not originally mine,
so I am unfamiliar with it. Also, I've never connected to SQL server
from Access before so I am new at that too. The original developer is
not available for questions.

In the remote SQL database, there are a couple of files that are named
dbo_syssegments and dbo_sysconstraints. Am I correct in assuming that
these are SQL Server system tables of some sort?

Thanks

 

[Bob]

Followup question: the other tables seem to be prefaced by the dbname
and suffixed by "extract" e.g. dbname_customerExtract. Is this because
they are pulled from the SQL relationship into an Access like table
i.e. "extracted" ?

thanks,

 

[Brendan Reynolds]

The tables with names beginning with 'sys' are system tables, yes. But the
tables with names ending in 'Extract' could be anything, the word doesn't,
as far as I am aware, have any specific standard or conventional
significance.

 

[Sylvain Lafontaine]

I believe that SysSegments and SysConstraints are old system tables from SQL
Server 6.5 and that they are no longer in use with SQL-2000.

When linking from Access, many wizards will add the owner of the SQL table
to its linked table, ie. dbname.CustomerExtract will be linked as
dbname_CustomerExtract in Access. However, this is only for convenience and
is not mandatory. For the word "Extract", either it's part of the name of
the table on SQL-Server or the original programmer add it for some purposes.

In all cases, you should look at the link property to see what's the name of
the real table associated on the SQL-Server side.

 

[Bob]

I believe that SysSegments and SysConstraints are old system tables from SQL
Server 6.5 and that they are no longer in use with SQL-2000.

When linking from Access, many wizards will add the owner of the SQL table
to its linked table, ie. dbname.CustomerExtract will be linked as
dbname_CustomerExtract in Access. However, this is only for convenience and
is not mandatory. For the word "Extract", either it's part of the name of
the table on SQL-Server or the original programmer add it for some purposes.

In all cases, you should look at the link property to see what's the name of
the real table associated on the SQL-Server side.

Thanks all... how do I find the "link" property.

Also, I am having a problem making changes to the tables linked
through Access. Any changes cannot be saved. From reading the help, I
think it may be because the tables have no keys, although I would
think I could change the table definition. (?)

Should I be able to change the table definition to add the key I need,
then update the keys? Or am I in a catch -22 since there is no unique
key on the tables and they are already in SQL Server format?

Is there any way to easily transfer an entire table back to SQL
Server? Perhaps I could make a copy, fix it locally, then move it
(easily?) back to SQL Server ?

Thanks for any pointers.

 

[Sylvain Lafontaine]

For the "Link" property, open the linked table in design mode then open the
properties window from the contextual menu (right click of the mouse). The
"Description" property is the link property that you are looking for.

From Access/MDB file, you cannot change the design of a SQL-Server table,
you must open/create/use an ADP project connected to the server: you can do
this from Access or use the Enterprise Manager for SQL-Server.

I don't really understand your question about transferring back and forward
a table: are you talking about the design (or schema) or about the data?

However, SQL-Server is a big topic and you should study it a little more
before messing up your application. There are also other newsgroups more
appropriate than this, m.p.a.odbcclientsvr for example.

 

[dbahooker]

man these MDB-sissies are crazy

use an Access Data Project.
it is about 100 times easier to deal with.

one connection link-- in one place-- and working between multiple SQL
databases is about 10 times easier than linking crap mdb files

-aaron