?
Solved

Using external database table in Access 2007

Posted on 2011-04-19
12
Medium Priority
?
512 Views
Last Modified: 2012-05-11
I am using Access 2007. I have used a very old version of Access many years ago so I cannot claim to be an expert.

I have a main table called “Test Booking System”.
On this table is a field called “Serial Plate No”

I also have a link to a database with a table called “Serial Nos”

I have a form that displays each record in “Test Booking System”.
Each item can be modified – this works OK.

The problem:
I want the item “Serial Plate No” to display a field/record from “Serial Nos”.
(The field name in "Serial Nos" is called "Serial No")

But the “Serial Plate No” box on my form shows “#Name?”

There is an Arrow to the left of the linked table so it must be linked OK - right?.

How do I get data from “Serial Nos”.

(A bit of VBA code would be good if there are any suggestions.)
0
Comment
Question by:usatrfe
  • 6
  • 6
12 Comments
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 35422885
Please check this out.  It might help.. Could be as simple as a spelling error or an extra space in the field name where it shouldn't be.

http://office.microsoft.com/en-us/access-help/i-see-name-displayed-in-a-control-HA001181447.aspx
0
 

Author Comment

by:usatrfe
ID: 35422975
Thank you for that.
I used the expression builder to make this:
"= [SerialNos]![Serial No]"
(I have noted that I typed Serial Nos with a space but that was only my typing error. There is no space!).

If the expression builder can find the data elements then surely the expression has been constructed correctly?
I have tried to build expressions for other items but the #Name? is always the result.
0
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 35423114
Can you run the underlying data in a query and see all of the data in datasheet mode?  This is the first step in troubleshooting this.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:usatrfe
ID: 35423202
I have created a Simple query and the datasheet view clearly shows the data that is in the external database (SerialNos). (sorry if this message has been duplicated).
0
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 35423222
Ok, but what I'm asking is have you run the SAME query (joining the databases and tables) that you use behind the form incorporating ALL of the tables and fields desired on the form at the same time.  I get that you can see the SerialNos.SerialNo in a query, but have you gotten the query to show Test Booking System.Serial Plate No AND SerialNos.SerialNo all in the same query.
0
 

Author Comment

by:usatrfe
ID: 35423326
Sorry about my misunderstanding.
This time I have run a simple query but selected a field from my main "Test Booking System" as "Serial Plate No" AND the linked "SerialNos" as "SeriaNo".
The only thing that I have had to do is make a relation ship between these to fields (this didn't exist before!).

This time there is NO data displayed in the datasheet view for this new query - very odd!.

0
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 35423349
This is the root of the problem.   All of the fields have to be available in the underlying data otherwise, the expression [SerialNos]![Serial No] doesn't mean anything and will produce a #Name? error.
0
 

Author Comment

by:usatrfe
ID: 35423438
Maybe I have missed something but surely if the external database is linked then the fields in that external table are available?.
Could you expand a little on how 'All of the fields have to be available in the underlying data'.

I may show my ignorance here but it sounds like i need to create extra columns in "Test Booking System" then create relationships to fields in "SerialNos".
Or am I off the track?!

0
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 35423505
The data is certainly available, but it looks like the joins are not working.   On what fields did you create these "relationships" - or joins as they are called.  You linked the two tables but what fields from the two are bringing them together?  Meaning - what field in one table has the same value in the other table? The data type might not be the same, or one field could be a text field and the other a numeric field, etc..  If you don't have a way to "link" or join them, a query cannot bring the two together to be shown in the same query.  Usually you have a Key and a Foreign Key relationship whereby each table has a unique ID field (usually numeric) and the other table refers to this ID in some way.  

If the underlying data (all of the fields you want to show in the form)  shows NO data, then that's the reason why you are getting that #Name? error.  You can't just list fields in the expression builder on tables that are not joined.  Relationships are not the same as joins - especially when the two tables will never be "related" because there isn't any "joining" data to link them.
0
 
LVL 9

Accepted Solution

by:
Ken Fayal earned 1500 total points
ID: 35423509
4AM here.. Gotta go to bed.
0
 

Author Comment

by:usatrfe
ID: 35423972

I haven't yet solved my problem as yet but armed with your kind help I believe that I can progress to a resolve.

Thank you for the information.

Pete

0
 

Author Closing Comment

by:usatrfe
ID: 36009703
Sorry about the delay
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question