Display data from ODBC table

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

Guest

I am trying to display the data from an imported ODBC table. The fields in
question all have #NAME? shown instead of the data. If I open the imported
table in my access database, the data shows just fine. But I am unable to
get the data to show on my form. ACCESS 2000. Thanks
 
It sounds to me that the error(s) are in the Form, not the (imported) Table.

Have you checked the Form's RecordSource?
 
Originally the Form's RecordSource was pointing to just the original table.
I changed the RecordSource to be a query to merge the original table with the
imported table. And changed all the individual fields to point to the query
results. I still get #NAME? showing in each field on the form, coming from
the imported ODBC table.
 
Open the Query by itself, i.e. not through the Form and see if the returned
Recordset is what you want.

Not sure what you have in "a query to merge the original table with the
imported table." but there are potential problems, especially with duplicate
Field names.

Post the details of your original Table, your ODBC-linked Table and the SQL
String.

Try a query just with the ODBC-linked Table only and post result.

--
HTH
Van T. Dinh
MVP (Access)
 
I ran the query by itself, and it displays all the data as desired. But when
I open the form, it gives the #name?. The query:
SELECT [PL-XP-2003].*, dbo_tblSoftware.*
FROM dbo_tblSoftware INNER JOIN [PL-XP-2003] ON (dbo_tblSoftware.SWID =
[PL-XP-2003].SWID) AND (dbo_tblSoftware.SWVer = [PL-XP-2003].Vers)
WHERE ((([PL-XP-2003].SWID)=[dbo_tblSoftware.swid]) AND
(([PL-XP-2003].Vers)=[dbo_tblSoftware.SWVer]) AND
((dbo_tblSoftware.PlantID)="PL") AND ((dbo_tblSoftware.Active)=True));

The two keys that link the tables are SWID defined as text 50, and the
version (Vers, SWVers) is text 25. Otherwise there are no fields the same in
both tables.

I am not sure how to post the details of the actual tables... screen
prints? (then how to attach a jpg to this?)

Original Access table:
 
Do you have the same Field names in the 2 Tables?

If you do and you only use the Field names (without the Table qualifier) in
the ControlSources, Access won't recognise the Field names only and you
need to use the Table qualifier(s).

Open the DatasheetView of the Query. If Access shows the Table qualifier(s)
in the column headings, you need to use the same in the ControlSources.
 
Note: On the form, when I display data from the access table, it displays
ok. But when I tell it to pull the data from the query instead, those fields
also show #NAME?, just like the ODBC fields. Is this a clue as to what is
wrong?

Also, the ODBC table is coming from an Access Project (I think) database
..MDE instead of .MDB.

THANK YOU SO MUCH for your help.
Gail
 
See earlier reply.

I am still not sure what you meant by ""a query to merge the original table
with the
imported table."

MDE is a compile version of the MDB, NOT an Access Project. Access Projects
have file extension ADP / ADE.

How did you modify the Query in an MDE? AFAIK, the design of the MDE
(including the design of Queries) cannot be modified in an MDE.
 
The MDE database contains only part of the data I use, in table
dbo_tblSoftware of the MDE. So I built a new MDB access database with a new
table PL-XP-2003 of the extra information I needed, and imported table
dbo_tblSoftware from the MDE into my MDB. I created a new query in the MDB
to join dbo_tblSoftware and PL-XP-2003. I only want to display the data
(read-only) from the imported dbo_tblSoftware, and make updateable the data
from PL-XP-2003 in my form, which is built in the MDB.

SELECT [PL-XP-2003].*, dbo_tblSoftware.*
FROM dbo_tblSoftware INNER JOIN [PL-XP-2003]
ON (dbo_tblSoftware.SWID = [PL-XP-2003].SWID)
AND (dbo_tblSoftware.SWVer = [PL-XP-2003].Vers)
WHERE ((([PL-XP-2003].SWID)=[dbo_tblSoftware.swid])
AND (([PL-XP-2003].Vers)=[dbo_tblSoftware.SWVer])
AND ((dbo_tblSoftware.PlantID)="PL")
AND ((dbo_tblSoftware.Active)=True));
 
OK... I have the data now displaying! I was using a ControlSource in the
form =[qryMerge.SWID] type of format, but when I changed it to SWID it worked
great!

Except for one thing... now all my data on the form is non-updateable. I
want the data from the MDE imported table to be non-updateable, but I need to
be able to update the data from my MDB table PL-XP-2003. Since the data on
the form is coming from a query instead of a table, how do I force the
ability to update certain fields?

THANK YOU. YOU HAVE BEEN A GREAT HELP.

Gail said:
The MDE database contains only part of the data I use, in table
dbo_tblSoftware of the MDE. So I built a new MDB access database with a new
table PL-XP-2003 of the extra information I needed, and imported table
dbo_tblSoftware from the MDE into my MDB. I created a new query in the MDB
to join dbo_tblSoftware and PL-XP-2003. I only want to display the data
(read-only) from the imported dbo_tblSoftware, and make updateable the data
from PL-XP-2003 in my form, which is built in the MDB.

SELECT [PL-XP-2003].*, dbo_tblSoftware.*
FROM dbo_tblSoftware INNER JOIN [PL-XP-2003]
ON (dbo_tblSoftware.SWID = [PL-XP-2003].SWID)
AND (dbo_tblSoftware.SWVer = [PL-XP-2003].Vers)
WHERE ((([PL-XP-2003].SWID)=[dbo_tblSoftware.swid])
AND (([PL-XP-2003].Vers)=[dbo_tblSoftware.SWVer])
AND ((dbo_tblSoftware.PlantID)="PL")
AND ((dbo_tblSoftware.Active)=True));

Van T. Dinh said:
See earlier reply.

I am still not sure what you meant by ""a query to merge the original table
with the
imported table."

MDE is a compile version of the MDB, NOT an Access Project. Access Projects
have file extension ADP / ADE.

How did you modify the Query in an MDE? AFAIK, the design of the MDE
(including the design of Queries) cannot be modified in an MDE.
Click to expand...
Click to expand...
 
I am getting the message "this recordset is not updateable." I would have to
believe that what I am doing is a common function... updating one table
while displaying (not updating) info from another table. My current
RecordSource for the form is the JOIN query I have shown, joining my
hopefully updateable table PL-XP-2003 in the MDB, with the ODBC imported
table from the MDE dbo_tblSoftware. HELP!

Gail said:
OK... I have the data now displaying! I was using a ControlSource in the
form =[qryMerge.SWID] type of format, but when I changed it to SWID it worked
great!

Except for one thing... now all my data on the form is non-updateable. I
want the data from the MDE imported table to be non-updateable, but I need to
be able to update the data from my MDB table PL-XP-2003. Since the data on
the form is coming from a query instead of a table, how do I force the
ability to update certain fields?

THANK YOU. YOU HAVE BEEN A GREAT HELP.

Gail said:
The MDE database contains only part of the data I use, in table
dbo_tblSoftware of the MDE. So I built a new MDB access database with a new
table PL-XP-2003 of the extra information I needed, and imported table
dbo_tblSoftware from the MDE into my MDB. I created a new query in the MDB
to join dbo_tblSoftware and PL-XP-2003. I only want to display the data
(read-only) from the imported dbo_tblSoftware, and make updateable the data
from PL-XP-2003 in my form, which is built in the MDB.

SELECT [PL-XP-2003].*, dbo_tblSoftware.*
FROM dbo_tblSoftware INNER JOIN [PL-XP-2003]
ON (dbo_tblSoftware.SWID = [PL-XP-2003].SWID)
AND (dbo_tblSoftware.SWVer = [PL-XP-2003].Vers)
WHERE ((([PL-XP-2003].SWID)=[dbo_tblSoftware.swid])
AND (([PL-XP-2003].Vers)=[dbo_tblSoftware.SWVer])
AND ((dbo_tblSoftware.PlantID)="PL")
AND ((dbo_tblSoftware.Active)=True));

Van T. Dinh said:
See earlier reply.

I am still not sure what you meant by ""a query to merge the original table
with the
imported table."

MDE is a compile version of the MDB, NOT an Access Project. Access Projects
have file extension ADP / ADE.

How did you modify the Query in an MDE? AFAIK, the design of the MDE
(including the design of Queries) cannot be modified in an MDE.

--
HTH
Van T. Dinh
MVP (Access)




Note: On the form, when I display data from the access table, it displays
ok. But when I tell it to pull the data from the query instead, those
fields
also show #NAME?, just like the ODBC fields. Is this a clue as to what is
wrong?

Also, the ODBC table is coming from an Access Project (I think) database
.MDE instead of .MDB.

THANK YOU SO MUCH for your help.
Gail
Click to expand...
Click to expand...
Click to expand...
 
Have you tested updating the data in the DatasheetView of the Query?

If you can in the above, you might have set the Recordset Type of the Form
incorrectly.

If you can't update in the DatasheetView then the problem is in the Query.
In this case:

* Check that you have a PK defined in Table [tblSoftware] on the SQL Server.
* Check your permissions on the SQL Server.

Your terminology is slightly confusing. If you imported the Table, then the
imported Table is a (local Access) Table, _not_ "ODBC imported". My guess
is that you only linked the SQL Server Table to your Access database and the
correct term is ODBC-linked Table.

I am not sure whether you linked the Table directly from the SQL Server or
from the other MDE. If you linked from the other MDE, suggest you try to
link the Table directly from the SQL Server rather than via the MDE.
 
Back
Top