Access - see details behind a data type that was created using the lookup wizard


In MS Access you can use the Lookup Wizard for a Data Type in a table.
If there is an existing table how do I see how it was created or the properties around this?

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: The Ten Commandmends of Access. Number 2.

You can see it in the Field Properites while being in the tables Design View.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Please don't use lookups at the table level. The ONLY positive thing about them is they save you a few seconds when you add a combo to a form, since they'll automatically set the Rowsource and such. Outside of that, they obfuscate data, make upsizing much more difficult, and make complex queries and reports more difficult to maintain.

Just Say No ...
ste5anSenior DeveloperCommented:
..and they hurt performance..
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

John TsioumprisSoftware & Systems EngineerCommented:
If you want to examine Access tables and check if they use the LookUp wizard then use need to use the tabledef object and then the properties collection to iterate and check what you want...check here for a start
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for the advice
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  To answer the question a bit more, it's always a long that is stored, which is what is used by the autonumber data type.

   As the others have said, for the typical developer, this is one feature that gets more in the way than anything.    Performance is really not a major impact with this, just that it's hard to see what's going on.

  One feature you will want to turn off though is subdatasheets on tables.  that does chew up some cycles in using it.  I have code for that if you want it.

  This feature and subdatasheets (which gives you "drill down" to related records when working with tables), were added for the casual end users that would be entering data more like they do in Excel directly in tables.

 For an application developer, they just get in the way because you will be providing them forms, etc to do the work.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much for the advice. I must admit I had been wondering about those sub data sheets
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Here's the code to turn off the subdatasheets.


Sub TurnOffSubDataSheets()

    Dim MyDB As DAO.Database
    Dim MyProperty As DAO.Property
    Dim propName As String, propVal As String, rplpropValue As String
    Dim propType As Integer, i As Integer
    Dim intCount As Integer

10  On Error GoTo tagError

20  Set MyDB = CurrentDb
30  propName = "SubDataSheetName"
40  propType = 10
50  propVal = "[None]"
60  rplpropValue = "[Auto]"
70  intCount = 0

80  For i = 0 To MyDB.TableDefs.Count - 1
90      If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
100         If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
110             MyDB.TableDefs(i).Properties(propName).Value = propVal
120             intCount = intCount + 1
130         End If
140     End If
150 Next i

160 MyDB.Close

170 If intCount > 0 Then
180     MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
190 End If

200 Exit Sub

210 If Err.Number = 3270 Then
220     Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
230     MyProperty.Type = propType
240     MyProperty.Value = propVal
250     MyDB.TableDefs(i).Properties.Append MyProperty
260     intCount = intCount + 1
270     Resume tagFromErrorHandling
280 Else
290     MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets RoutineName."
300 End If
End Sub

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Excellent! Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.