Linking SQL Server tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to link tables from SQL Server to my Access 2003 database.
When I do File>Get External Data>Link Tables I get a File Dialog where I can
choose "everything" BUT SQL Server!? I did install Jet SP8! According to the
help I should start a Wizard! Any hints??

This SHOULD happen according to help but does not!
Open an Access project, or switch to the Database window for the open Access
project.
On the File menu, point to Get External Data, and then click Link Tables.
In the first screen of the Link Table Wizard, select the type of link you
want to create, either Linked SQL or Transact SQL, and then click Next.
If you select Linked SQL, Access creates a linked server which provides the
most functionality, including the ability to update data if the OLE DB data
source allows, and permanently stores the data source connection information
in the Microsoft SQL Server database. Also, if the SQL Server database you
are linking to is on another SQL Server, in most cases, SQL Server will
optimize join operations. However, you must be a member of either the
sysadmin or setupadmin roles.
 
I notice the help topic you quoted is talking about an Access project (ADP).
Are you using an ADP, or an MDB? In an MDB, select ODBC from the 'files of
type' list. In the Select Data Source dialog, click 'New' if you don't
already have a DSN set up. The procedure is fairly straight forward. Post
back here again if you get stuck.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thanks!
I created a table Findings in SQLServer. It turned up as dbo.Findings in
Access when I linked it. I then deleted Findings in Access and renamed
dbo.Findings to Findings. When I run the form I can see data from SQL Server
but I cannot update! Seems to be read only. I gave all permissions to public
in SQL sever.
Can I change it (in Access) so I can update? Does it have anything to do
with how I set up the data source??
 
The linked table will be read only if there is no unique index or constraint
defined in the source table.

You *may* find that you have to delete and then recreate the link after
creating the unique index or constraint. I can't remember for sure, but if
the table is still read only after you create the unique index or
constraint, you can give that a try.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thank you, your post was very useful to me.
Hugo

Brendan Reynolds said:
The linked table will be read only if there is no unique index or constraint
defined in the source table.

You *may* find that you have to delete and then recreate the link after
creating the unique index or constraint. I can't remember for sure, but if
the table is still read only after you create the unique index or
constraint, you can give that a try.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
Click to expand...
 
Back
Top