linked table does not appear in DB frontend listbox

[accessdan]

Hi there,

I was wondering if someone could help me with this problem. I have this linked table that for some reason does not appear in a listbox on a form. However if I import the table instead of linking it to the database, then the table appears in the listbox.

Anyone have any ideas on this one?

Thanks in advance!

Dan.

 

[Bry12345]

Try unlinking the table in the front end .. i.e. delete the link, and then re-link it. This sounds like a corruption issue.

 

[accessdan]

yeah i've tried deleting the linked tables and then creating a new one a couple of times.. doesn't solve the problem. Maybe it has something to do with the data in the form not being local (i.e in the actual database itself).

 

[Ed2020]

What's the row source for your listbox? How are you picking the table names up?

Ed Metcalfe.

 

[jebry]

Hi!

To elaborate on Ed's question, if you are using MSysObjects and limiting it by the Type field, linked tables have a type 6 instead of 1 like local tables.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[accessdan]

Hi,
thanks for all your replies. Ed2020, the row source for the listbox is
SELECT MsysObjects.Name AS ObjectName, IIf([type]=6,&quot;Table&quot;,&quot;Query&quot AS ObjectType FROM MsysObjects WHERE (((Left$([Name],1))<>&quot;~&quot AND ((Left$([Name],4))<>&quot;Msys&quot AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16)) ORDER BY MsysObjects.Name;

Is it as simple as changing the Type to 6 (as I have done, in bold)?

thanks.

 

[jebry]

Hi!

I'm not sure you need to worry about the Flags, but I don't think it will hurt either. If you have local tables as well as linked tables, I would word your IIf statement the other way: IIf(Type = 5, &quot;Query&quot;, &quot;Table&quot since both 1 and 6 designate tables. Other than that, it looks like you will pull out the information you want.

Jeff Bridgham
bridgham@purdue.edu

 

[accessdan]

Hi Jeff,

Thanks for your continuing help! Much appreciated...
Still can't get that linked table to appear in that list box. I'm determined to get it going though!
If you have any more ideas I'd love to hear them.

Thanks.

Dan.

 

[accessdan]

thanks to everyone who helped with this.. i have found the problem. If it's any use to you, I removed the criterion set in Flags (in the SQL query). This allowed all linked tables to be shown.