Acess 2003 connection problem to SQL Server

I have a small group who uses Access 2003 to connect to SQL Server. The
users run XP pro and Access 2003. A common DSN is created on each user’s
box. They are connecting to a box with Windows 2000 Standard Svc Pack 4 &
SQL Server 2000 Standard Svc Pack 3a. They are linking to the SQL Server
tables. We are using windows authentication.
The two users are not able to access SQL Server at the same time. The error
message they are receiving is:
Microsoft Office Access
Could not execute query; could not find liked table.
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
‘dbo.MAIN. (#208)
Interesting though is that if the user drops the e linked table and try to
relink it the objects that appear are from the Master database.
I verified the Group the users belong to have the correct default database
set.
I am at a loss and any help or hints to get me going in the right direction
would be appreciated.
It will work better for you if you ditched the DSNs and wrote VBA/DAO
code to relink the tables. You can supply ODBC connection string
information directly when the application loads, and delete the
TableDef objects when the application closes. You supply a source
table name, and the name of the link in the FE separately. This way
you pick up any schema changes to the underlying tables, which
invalidates old links. You can get the correct connection string from
http://able-consulting.com/ADO_Conn.htm. I recommend using DAO for
this instead of ADOX because there have been problems in the past with
linked tables coming in read-only.
--Mary
On Thu, 12 Aug 2004 09:21:02 -0700, "T Man"
<TMan@.discussions.microsoft.com> wrote:

>I have a small group who uses Access 2003 to connect to SQL Server. The
>users run XP pro and Access 2003. A common DSN is created on each users
>box. They are connecting to a box with Windows 2000 Standard Svc Pack 4 &
>SQL Server 2000 Standard Svc Pack 3a. They are linking to the SQL Server
>tables. We are using windows authentication.
>The two users are not able to access SQL Server at the same time. The error
>message they are receiving is:
>Microsoft Office Access
>Could not execute query; could not find liked table.
>[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>dbo.MAIN. (#208)
>
>Interesting though is that if the user drops the e linked table and try to
>relink it the objects that appear are from the Master database.
>I verified the Group the users belong to have the correct default database
>set.
>I am at a loss and any help or hints to get me going in the right direction
>would be appreciated.
|||Mary,
I am confused regarding the stance on the usage of DAO... In an email to
another user it appears you do not favor the use of DAO...
(At this point in time, DAO 3.6 is truly ancient...), however, in this case
it appears preferred.
Is it OK for linking, but not OK to perform updates ? Just want to know the
difference.
Thanks
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:s0oph0tv4tkue147148v4qccketajvdetj@.4ax.com... [vbcol=seagreen]
> It will work better for you if you ditched the DSNs and wrote VBA/DAO
> code to relink the tables. You can supply ODBC connection string
> information directly when the application loads, and delete the
> TableDef objects when the application closes. You supply a source
> table name, and the name of the link in the FE separately. This way
> you pick up any schema changes to the underlying tables, which
> invalidates old links. You can get the correct connection string from
> http://able-consulting.com/ADO_Conn.htm. I recommend using DAO for
> this instead of ADOX because there have been problems in the past with
> linked tables coming in read-only.
> --Mary
> On Thu, 12 Aug 2004 09:21:02 -0700, "T Man"
> <TMan@.discussions.microsoft.com> wrote:
&[vbcol=seagreen]
error[vbcol=seagreen]
to[vbcol=seagreen]
database[vbcol=seagreen]
direction
>