Linked SQL Table Problem

[SmokyMtnzz]

Hi everyone,

I am trying to link to a SQL table through ODBC. Everything links
perfectly until I try to open the linked table. That's when I get the
dreaded #Deleted# info everywhere. I read about a fix somewhere that
said that when linking it will ask for me to choose the primary keys,
but it never did that. I can import and then view/use the table with
no problems at all.

I was hoping that linkings would be just as simple as linking to an
Access table. I'm not needing to add info to the SQL table, just use
it to gather updated info to create queries and reports.

One other thing is that I can not make any changes to the SQL database
because it is run by the parent company. I just want to link to it so
I can custom create my own reports from the info gathered in the SQL
database. I am fairly new to Access, so I hope that there is a simple
solution.

Thank you for any help you can provide.
Bob

 

[Rick Brandt]

Hi everyone,

I am trying to link to a SQL table through ODBC. Everything links
perfectly until I try to open the linked table. That's when I get the
dreaded #Deleted# info everywhere. I read about a fix somewhere that
said that when linking it will ask for me to choose the primary keys,
but it never did that. I can import and then view/use the table with
no problems at all.

I was hoping that linkings would be just as simple as linking to an
Access table. I'm not needing to add info to the SQL table, just use
it to gather updated info to create queries and reports.

One other thing is that I can not make any changes to the SQL database
because it is run by the parent company. I just want to link to it so
I can custom create my own reports from the info gathered in the SQL
database. I am fairly new to Access, so I hope that there is a simple
solution.

Thank you for any help you can provide.
Bob

This can happen if the Primary Key on the table is of a DataType that isn't
properly supported in Access (like decimal). It is usually resolved by
avoiding that type for the PK or by adding a Timestamp column to the table
definition. If you have no ability to alter the table you might have a
problem that is unresolvable.

Do you need to edit the data or only to read it? If the latter try a
PassThrough query instead. Also I have found that in some of these cases
the #DELETED only appears in datasheet view and when using a non-continuous
form showing only one record a t a time that everything is ok.

 

[SmokyMtnzz]

This can happen if the Primary Key on the table is of a DataType that isn't
properly supported in Access (like decimal). It is usually resolved by
avoiding that type for the PK or by adding a Timestamp column to the table
definition. If you have no ability to alter the table you might have a
problem that is unresolvable.

Do you need to edit the data or only to read it? If the latter try a
PassThrough query instead. Also I have found that in some of these cases
the #DELETED only appears in datasheet view and when using a non-continuous
form showing only one record a t a time that everything is ok.

Thank you Rick so much for the quick reply. I am needing to only read
this data. I'm not familiar with pass-through queries (I am familiar
with Union queries), but I will do some research on this subject when
I get to work, and will let you know how it turns out. (If you know of
a good website that explains Union queries, please let me know.)

Thanks,
Bob

 

[Rick Brandt]

Thank you Rick so much for the quick reply. I am needing to only read
this data. I'm not familiar with pass-through queries (I am familiar
with Union queries), but I will do some research on this subject when
I get to work, and will let you know how it turns out. (If you know of
a good website that explains Union queries, please let me know.)

A UNION query is not relevant to this problem. What you need is a
Passthrough query. Open a new query and on the query menu select "SQL
Specific - Pass-Through".

The window will change to the SQL view since you have to write SQL for
Passthrough queries. Open the property sheet and click the build [...]
button to the right of the ODBC Connect Str property and select the ODBC
data source that you want the query to use. It will be the same one you
used to create the link.

As a simple test you can just try the SQL...

SELECT * FROM TableName

....and switch to Datasheet view to see if it works. If so then you can
modify the SQL for just the fields you need and add any criteria that might
be required.

 

[SmokyMtnzz]

A UNION query is not relevant to this problem. What you need is a
Passthrough query. Open a new query and on the query menu select "SQL
Specific - Pass-Through".

The window will change to the SQL view since you have to write SQL for
Passthrough queries. Open the property sheet and click the build [...]
button to the right of the ODBC Connect Str property and select the ODBC
data source that you want the query to use. It will be the same one you
used to create the link.

As a simple test you can just try the SQL...

SELECT * FROM TableName

...and switch to Datasheet view to see if it works. If so then you can
modify the SQL for just the fields you need and add any criteria that might
be required.

Wonderful. That worked perfect, even the simple test. Now I'm off to
learn some simple SQL language from a couple of Access books I have.
Thanks so much for your help, Rick.

Bob