how do I check the table type

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

How do I loop through all the tables in an external database, and check to
see if it is a linked table:

something like this:



Set acc = CreateObject("Access.Application")

acc.OpenCurrentDatabase ("\\Van-citrix\Van-Citrix Volume
1\Apps\CST\CST_SUM_FRONT_END.mdb")

For j = 0 To acc.CurrentData.AllTables.Count - 1

With acc.CurrentData.AllTables

' here's where I don't know how to check to see if the table is
a linked table

End With

Next j
 
Tony

I use the system tables to get that sort of info. Assuming there are no ODBC
linked tables, this should work:
SELECT [Name],
IIF(IsNull([Database])=False,"Linked","Local")
FROM MSysObjects
WHERE [Type] = 1
AND [Name] NOT LIKE 'MSys*'

Linked tables will have the database path in the [Database] field.
 
Bill: Type = 1 is guaranteed not to be linked tables.

Linked tables will (usually) be Type = 6 (ODBC linked tables will be Type =
5)

SELECT MSysObjects.Name,
IIf(Type = 1, "Local", "Linked") AS TableType
FROM MSysObjects
WHERE (MSysObjects.Type IN (1, 5, 6))
AND (MSysObjects.Name Not Like 'MSys*')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bill Mosca said:
Tony

I use the system tables to get that sort of info. Assuming there are no
ODBC linked tables, this should work:
SELECT [Name],
IIF(IsNull([Database])=False,"Linked","Local")
FROM MSysObjects
WHERE [Type] = 1
AND [Name] NOT LIKE 'MSys*'

Linked tables will have the database path in the [Database] field.

--
Bill Mosca, MS Access MVP


Anthony said:
How do I loop through all the tables in an external database, and check
to see if it is a linked table:

something like this:



Set acc = CreateObject("Access.Application")

acc.OpenCurrentDatabase ("\\Van-citrix\Van-Citrix Volume
1\Apps\CST\CST_SUM_FRONT_END.mdb")

For j = 0 To acc.CurrentData.AllTables.Count - 1

With acc.CurrentData.AllTables

' here's where I don't know how to check to see if the table
is a linked table

End With

Next j
Click to expand...
Click to expand...
 
Doh! Thanks for pointing out my error, Doug. I should have looked at one of
my databases before responding instead of relying on my memory.

Sorry for misleading you, Tony.

--
Bill Mosca, MS Access MVP


Douglas J. Steele said:
Bill: Type = 1 is guaranteed not to be linked tables.

Linked tables will (usually) be Type = 6 (ODBC linked tables will be Type
= 5)

SELECT MSysObjects.Name,
IIf(Type = 1, "Local", "Linked") AS TableType
FROM MSysObjects
WHERE (MSysObjects.Type IN (1, 5, 6))
AND (MSysObjects.Name Not Like 'MSys*')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bill Mosca said:
Tony

I use the system tables to get that sort of info. Assuming there are no
ODBC linked tables, this should work:
SELECT [Name],
IIF(IsNull([Database])=False,"Linked","Local")
FROM MSysObjects
WHERE [Type] = 1
AND [Name] NOT LIKE 'MSys*'

Linked tables will have the database path in the [Database] field.

--
Bill Mosca, MS Access MVP


Anthony said:
How do I loop through all the tables in an external database, and check
to see if it is a linked table:

something like this:



Set acc = CreateObject("Access.Application")

acc.OpenCurrentDatabase ("\\Van-citrix\Van-Citrix Volume
1\Apps\CST\CST_SUM_FRONT_END.mdb")

For j = 0 To acc.CurrentData.AllTables.Count - 1

With acc.CurrentData.AllTables

' here's where I don't know how to check to see if the table
is a linked table

End With

Next j
Click to expand...
Click to expand...
Click to expand...
 
Doug, I'm thinking that ODBC linked tables are type 4?
From memory, 5 returns queries.

Adds to the fun when we all chime in. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Douglas J. Steele said:
Bill: Type = 1 is guaranteed not to be linked tables.

Linked tables will (usually) be Type = 6 (ODBC linked tables will be Type
= 5)

SELECT MSysObjects.Name,
IIf(Type = 1, "Local", "Linked") AS TableType
FROM MSysObjects
WHERE (MSysObjects.Type IN (1, 5, 6))
AND (MSysObjects.Name Not Like 'MSys*')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bill Mosca said:
Tony

I use the system tables to get that sort of info. Assuming there are no
ODBC linked tables, this should work:
SELECT [Name],
IIF(IsNull([Database])=False,"Linked","Local")
FROM MSysObjects
WHERE [Type] = 1
AND [Name] NOT LIKE 'MSys*'

Linked tables will have the database path in the [Database] field.

--
Bill Mosca, MS Access MVP


Anthony said:
How do I loop through all the tables in an external database, and check
to see if it is a linked table:

something like this:

Set acc = CreateObject("Access.Application")

acc.OpenCurrentDatabase ("\\Van-citrix\Van-Citrix Volume
1\Apps\CST\CST_SUM_FRONT_END.mdb")

For j = 0 To acc.CurrentData.AllTables.Count - 1

With acc.CurrentData.AllTables

' here's where I don't know how to check to see if the table
is a linked table

End With

Next j
Click to expand...
Click to expand...
Click to expand...
 
Yeah, I think you're right. I used to know this stuff! <g>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Allen Browne said:
Doug, I'm thinking that ODBC linked tables are type 4?
From memory, 5 returns queries.

Adds to the fun when we all chime in. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Douglas J. Steele said:
Bill: Type = 1 is guaranteed not to be linked tables.

Linked tables will (usually) be Type = 6 (ODBC linked tables will be Type
= 5)

SELECT MSysObjects.Name,
IIf(Type = 1, "Local", "Linked") AS TableType
FROM MSysObjects
WHERE (MSysObjects.Type IN (1, 5, 6))
AND (MSysObjects.Name Not Like 'MSys*')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bill Mosca said:
Tony

I use the system tables to get that sort of info. Assuming there are no
ODBC linked tables, this should work:
SELECT [Name],
IIF(IsNull([Database])=False,"Linked","Local")
FROM MSysObjects
WHERE [Type] = 1
AND [Name] NOT LIKE 'MSys*'

Linked tables will have the database path in the [Database] field.

--
Bill Mosca, MS Access MVP


How do I loop through all the tables in an external database, and check
to see if it is a linked table:

something like this:

Set acc = CreateObject("Access.Application")

acc.OpenCurrentDatabase ("\\Van-citrix\Van-Citrix Volume
1\Apps\CST\CST_SUM_FRONT_END.mdb")

For j = 0 To acc.CurrentData.AllTables.Count - 1

With acc.CurrentData.AllTables

' here's where I don't know how to check to see if the
table is a linked table

End With

Next j
Click to expand...
Click to expand...
Click to expand...
 
Back
Top