SQL Connection Error with MS Access

  • Thread starter Thread starter Datasort
  • Start date Start date
D

Datasort

I have been connecting (linked table) to SQL Server 2005 for some time from
an Access 2007 database. I now try to open the connection and get the
following error.

Could not execute query; could not find linked table
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
`dbo.tablename’. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared. (#8180)
My connection string is:
ODBC;DRIVER=SQL Server;SERVER=10.56.161.104;APP=2007 Microsoft Office
system;;TABLE=dbo.TableName
I use a ODBC connection that test correctly.

Things I have tried:
Compact and repair
Refresh the link (Link table manager)
Remove the linked SQL table then Compact and repair and relink. When I do
this I still have the error but I can see that the metadata for the table is
available. For instance this table has 2 columns and 10 records. I see
#name? in each field of each record in the table.
I have rebuilt the access database and attached the SQL tables. This works
fine until I close the database then reopen. Then the problems reoccur.


Any thoughts on how I can reset this would be appreciated
 
First, this newsgroup is about ADP and SQL-Server and has nothing to do with
ODBC Linked Tables; you should ask in a more appropriate newsgroup such as
m.p.access.odbcclientsvr.

Second, in your case, maybe your problem is a corruption problem with the
querydef itself and not with the linked table. Instead of just compacting &
repairing - which often don't solve any problem, I would do a full
decompilation of the MDB file by using the "Add or remove a dummy a
reference Trick" or by using the parameters /decompile /compress /repair.
Even better: create a blank MDB file and import everything.

Finally, I would also clear up (read: remove) the extended properties on the
SQL-Server table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Firstly, as Sylvain pointed out, this NG is for Access ADP.

Secondly, is "dbo.TableName" a database name or a table's name in a
database? If it is latter, of course you cannot connect to: Connection is to
connect to a database, not a table.

Where do you try to open the connection? in your VBA code?
 
Dear Ms. Lafontaine,


Thanks for pointing out other group. I found SQL server and assumed this was
the place to be. I will place a post in that group. Secondly, I have tried
to "start from scratch" and get the same problem. I also checked the extended
properties for the table and there were not any.

So ... if I start a new DB and odbc link to any table things work fine. When
I close the DB and restart I get the same problem.


--
Stewart Rogers
DataSort Software, L.C.


Sylvain Lafontaine said:
First, this newsgroup is about ADP and SQL-Server and has nothing to do with
ODBC Linked Tables; you should ask in a more appropriate newsgroup such as
m.p.access.odbcclientsvr.

Second, in your case, maybe your problem is a corruption problem with the
querydef itself and not with the linked table. Instead of just compacting &
repairing - which often don't solve any problem, I would do a full
decompilation of the MDB file by using the "Add or remove a dummy a
reference Trick" or by using the parameters /decompile /compress /repair.
Even better: create a blank MDB file and import everything.

Finally, I would also clear up (read: remove) the extended properties on the
SQL-Server table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Datasort said:
I have been connecting (linked table) to SQL Server 2005 for some time
from
an Access 2007 database. I now try to open the connection and get the
following error.

Could not execute query; could not find linked table
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
`dbo.tablename'. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared. (#8180)
My connection string is:
ODBC;DRIVER=SQL Server;SERVER=10.56.161.104;APP=2007 Microsoft Office
system;;TABLE=dbo.TableName
I use a ODBC connection that test correctly.

Things I have tried:
Compact and repair
Refresh the link (Link table manager)
Remove the linked SQL table then Compact and repair and relink. When I do
this I still have the error but I can see that the metadata for the table
is
available. For instance this table has 2 columns and 10 records. I see
#name? in each field of each record in the table.
I have rebuilt the access database and attached the SQL tables. This
works
fine until I close the database then reopen. Then the problems reoccur.


Any thoughts on how I can reset this would be appreciated
Click to expand...
Click to expand...
 
Anything particular with the structure of this table, for example is there
any BIGINT in there or is there another table with the same name but a
different user; for example user.TableName instead of dbo.TableName.

Second, is the name 'dbo.TableName' a verbatim or if you change its name
before posting your message. What's the exact name of the local ODBC linked
table?

Also, have you full right on this database or if you are member of only some
roles?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Datasort said:
Dear Ms. Lafontaine,


Thanks for pointing out other group. I found SQL server and assumed this
was
the place to be. I will place a post in that group. Secondly, I have
tried
to "start from scratch" and get the same problem. I also checked the
extended
properties for the table and there were not any.

So ... if I start a new DB and odbc link to any table things work fine.
When
I close the DB and restart I get the same problem.


--
Stewart Rogers
DataSort Software, L.C.


Sylvain Lafontaine said:
First, this newsgroup is about ADP and SQL-Server and has nothing to do
with
ODBC Linked Tables; you should ask in a more appropriate newsgroup such
as
m.p.access.odbcclientsvr.

Second, in your case, maybe your problem is a corruption problem with the
querydef itself and not with the linked table. Instead of just
compacting &
repairing - which often don't solve any problem, I would do a full
decompilation of the MDB file by using the "Add or remove a dummy a
reference Trick" or by using the parameters /decompile /compress /repair.
Even better: create a blank MDB file and import everything.

Finally, I would also clear up (read: remove) the extended properties on
the
SQL-Server table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Datasort said:
I have been connecting (linked table) to SQL Server 2005 for some time
from
an Access 2007 database. I now try to open the connection and get the
following error.

Could not execute query; could not find linked table
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
`dbo.tablename'. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be
prepared. (#8180)
My connection string is:
ODBC;DRIVER=SQL Server;SERVER=10.56.161.104;APP=2007 Microsoft Office
system;;TABLE=dbo.TableName
I use a ODBC connection that test correctly.

Things I have tried:
Compact and repair
Refresh the link (Link table manager)
Remove the linked SQL table then Compact and repair and relink. When I
do
this I still have the error but I can see that the metadata for the
table
is
available. For instance this table has 2 columns and 10 records. I see
#name? in each field of each record in the table.
I have rebuilt the access database and attached the SQL tables. This
works
fine until I close the database then reopen. Then the problems
reoccur.


Any thoughts on how I can reset this would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your continued support!

1. I did change the table name. I have tried several tables.
Cust which has been working for a long time
Cust_Drvier which is a new table.
Other tables as well.

2. Your issue regarding security made me try some things with an "sa"
password as well as a restricted user. As a result I noticed that there were
2 ODBC connections. On as Windows Server 2003 there are two ODBC connections
with the same name one is a User DSN where the driver says SQL server and the
other is a File dsn. I do not know why there are 2 but the file DSN is the
one I was using. I have changed it to the User DSN and now things are
working. hmmm. I will let the clients test for a while and see if it
stablizes.

The clients box has a User DSN and thus there should not be any conflict.
This is all strange to me.

Thanks again,
--
Stewart Rogers
DataSort Software, L.C.


Sylvain Lafontaine said:
Anything particular with the structure of this table, for example is there
any BIGINT in there or is there another table with the same name but a
different user; for example user.TableName instead of dbo.TableName.

Second, is the name 'dbo.TableName' a verbatim or if you change its name
before posting your message. What's the exact name of the local ODBC linked
table?

Also, have you full right on this database or if you are member of only some
roles?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Datasort said:
Dear Ms. Lafontaine,


Thanks for pointing out other group. I found SQL server and assumed this
was
the place to be. I will place a post in that group. Secondly, I have
tried
to "start from scratch" and get the same problem. I also checked the
extended
properties for the table and there were not any.

So ... if I start a new DB and odbc link to any table things work fine.
When
I close the DB and restart I get the same problem.


--
Stewart Rogers
DataSort Software, L.C.


Sylvain Lafontaine said:
First, this newsgroup is about ADP and SQL-Server and has nothing to do
with
ODBC Linked Tables; you should ask in a more appropriate newsgroup such
as
m.p.access.odbcclientsvr.

Second, in your case, maybe your problem is a corruption problem with the
querydef itself and not with the linked table. Instead of just
compacting &
repairing - which often don't solve any problem, I would do a full
decompilation of the MDB file by using the "Add or remove a dummy a
reference Trick" or by using the parameters /decompile /compress /repair.
Even better: create a blank MDB file and import everything.

Finally, I would also clear up (read: remove) the extended properties on
the
SQL-Server table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)



I have been connecting (linked table) to SQL Server 2005 for some time
from
an Access 2007 database. I now try to open the connection and get the
following error.

Could not execute query; could not find linked table
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
`dbo.tablename'. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be
prepared. (#8180)
My connection string is:
ODBC;DRIVER=SQL Server;SERVER=10.56.161.104;APP=2007 Microsoft Office
system;;TABLE=dbo.TableName
I use a ODBC connection that test correctly.

Things I have tried:
Compact and repair
Refresh the link (Link table manager)
Remove the linked SQL table then Compact and repair and relink. When I
do
this I still have the error but I can see that the metadata for the
table
is
available. For instance this table has 2 columns and 10 records. I see
#name? in each field of each record in the table.
I have rebuilt the access database and attached the SQL tables. This
works
fine until I close the database then reopen. Then the problems
reoccur.


Any thoughts on how I can reset this would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
I suppose that this is only a configuration or a permission problem. Your
best bet would be to use a System DSN without a file DSN.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Datasort said:
Thanks for your continued support!

1. I did change the table name. I have tried several tables.
Cust which has been working for a long time
Cust_Drvier which is a new table.
Other tables as well.

2. Your issue regarding security made me try some things with an "sa"
password as well as a restricted user. As a result I noticed that there
were
2 ODBC connections. On as Windows Server 2003 there are two ODBC
connections
with the same name one is a User DSN where the driver says SQL server and
the
other is a File dsn. I do not know why there are 2 but the file DSN is
the
one I was using. I have changed it to the User DSN and now things are
working. hmmm. I will let the clients test for a while and see if it
stablizes.

The clients box has a User DSN and thus there should not be any conflict.
This is all strange to me.

Thanks again,
--
Stewart Rogers
DataSort Software, L.C.


Sylvain Lafontaine said:
Anything particular with the structure of this table, for example is
there
any BIGINT in there or is there another table with the same name but a
different user; for example user.TableName instead of dbo.TableName.

Second, is the name 'dbo.TableName' a verbatim or if you change its name
before posting your message. What's the exact name of the local ODBC
linked
table?

Also, have you full right on this database or if you are member of only
some
roles?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Datasort said:
Dear Ms. Lafontaine,


Thanks for pointing out other group. I found SQL server and assumed
this
was
the place to be. I will place a post in that group. Secondly, I have
tried
to "start from scratch" and get the same problem. I also checked the
extended
properties for the table and there were not any.

So ... if I start a new DB and odbc link to any table things work fine.
When
I close the DB and restart I get the same problem.


--
Stewart Rogers
DataSort Software, L.C.


:

First, this newsgroup is about ADP and SQL-Server and has nothing to
do
with
ODBC Linked Tables; you should ask in a more appropriate newsgroup
such
as
m.p.access.odbcclientsvr.

Second, in your case, maybe your problem is a corruption problem with
the
querydef itself and not with the linked table. Instead of just
compacting &
repairing - which often don't solve any problem, I would do a full
decompilation of the MDB file by using the "Add or remove a dummy a
reference Trick" or by using the parameters /decompile /compress
/repair.
Even better: create a blank MDB file and import everything.

Finally, I would also clear up (read: remove) the extended properties
on
the
SQL-Server table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and
SQL-Server
(French)



I have been connecting (linked table) to SQL Server 2005 for some
time
from
an Access 2007 database. I now try to open the connection and get
the
following error.

Could not execute query; could not find linked table
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
`dbo.tablename'. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could
not
be
prepared. (#8180)
My connection string is:
ODBC;DRIVER=SQL Server;SERVER=10.56.161.104;APP=2007 Microsoft
Office
system;;TABLE=dbo.TableName
I use a ODBC connection that test correctly.

Things I have tried:
Compact and repair
Refresh the link (Link table manager)
Remove the linked SQL table then Compact and repair and relink.
When I
do
this I still have the error but I can see that the metadata for the
table
is
available. For instance this table has 2 columns and 10 records. I
see
#name? in each field of each record in the table.
I have rebuilt the access database and attached the SQL tables.
This
works
fine until I close the database then reopen. Then the problems
reoccur.


Any thoughts on how I can reset this would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
..ÎÒÏë½á½»Ò»Ð©Ï²»¶ÒôÀÖ.Ö÷³Ö¸ÐÐËȤµÄÈË,ÇëÓëÎÒÁªÏµ¡£
 
Back
Top