GET BACK-END TABLES

[Glint]

Hi Guys,
I need a query in my front end to retrieve the names of the tables in the
back end. The two databases are in the same pc right now but will connect
accross a network later. Please help.

 

[Skip]

Hi Guys,
I need a query in my front end to retrieve the names of the tables in the
back end. The two databases are in the same pc right now but will connect
accross a network later. Please help.

Would this help? Look at the last Debug.Print statement and the "Remote
Table"
'============================================
Public Sub DisplayTableSource()
Dim tbd As New TableDef 'needs "Microsoft DAO 3.6 Object Library"
For Each tbd In CurrentDb.TableDefs
If InStr(1, tbd.Connect, "ODBC") > 0 Then
Debug.Print "ODBC linked table: " & tbd.Name & ". Connect=" &
tbd.Connect
ElseIf Trim(tbd.Connect) = "" Then
Debug.Print "Local table: " & tbd.Name
ElseIf Left(tbd.Connect, 10) = ";DATABASE=" Then
Debug.Print "Access linked table: " & tbd.Name & ". Remote
table:" & tbd.SourceTableName & ". Connect=" & tbd.Connect
End If
Next tbd
End Sub

 

[Marshall Barton]

I need a query in my front end to retrieve the names of the tables in the
back end. The two databases are in the same pc right now but will connect
accross a network later.

This code should put the list of tables in a recordset.

Dim dbBE As Database
Dim BEpath As String
Dim SQL As String

SQL = "SELECT [Name] FROM MSysObjects " _
& "WHERE [Type] = 1 AND Not [Name] Like 'MSys*' "
BEpath = Mid(CurrentDb.TableDefs("any linked
table").Connect, 11)
Set dbBE = OpenDatabase(BEpath)
Set rs = dbBE.OpenRecordset(SQL, dbOpenSnapshot,
dbReadOnly)
Do Until rs.EOF
' do your thing here
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set dbBE = Nothing

If you only want a query and you can hard code the path,
then

SELECT [Name]
FROM MSysObjects IN "path to back end file"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'

 

[Glint]

Thanks, Guys.
I am real grateful.
--
Glint

I need a query in my front end to retrieve the names of the tables in the
back end. The two databases are in the same pc right now but will connect
accross a network later.

This code should put the list of tables in a recordset.

Dim dbBE As Database
Dim BEpath As String
Dim SQL As String

SQL = "SELECT [Name] FROM MSysObjects " _
& "WHERE [Type] = 1 AND Not [Name] Like 'MSys*' "
BEpath = Mid(CurrentDb.TableDefs("any linked
table").Connect, 11)
Set dbBE = OpenDatabase(BEpath)
Set rs = dbBE.OpenRecordset(SQL, dbOpenSnapshot,
dbReadOnly)
Do Until rs.EOF
' do your thing here
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set dbBE = Nothing

If you only want a query and you can hard code the path,
then

SELECT [Name]
FROM MSysObjects IN "path to back end file"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'

 

[Glint]

Sorry to bother you again, Marshall, but I am having problem building the
string correctly for the sql and the query.
The back end is at C:\ECK ADMIN\AreaAdminBEMDE.mde
How should I put it into the code?
--
Glint

I need a query in my front end to retrieve the names of the tables in the
back end. The two databases are in the same pc right now but will connect
accross a network later.

This code should put the list of tables in a recordset.

Dim dbBE As Database
Dim BEpath As String
Dim SQL As String

SQL = "SELECT [Name] FROM MSysObjects " _
& "WHERE [Type] = 1 AND Not [Name] Like 'MSys*' "
BEpath = Mid(CurrentDb.TableDefs("any linked
table").Connect, 11)
Set dbBE = OpenDatabase(BEpath)
Set rs = dbBE.OpenRecordset(SQL, dbOpenSnapshot,
dbReadOnly)
Do Until rs.EOF
' do your thing here
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set dbBE = Nothing

If you only want a query and you can hard code the path,
then

SELECT [Name]
FROM MSysObjects IN "path to back end file"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'

 

[Dale Fye]

Pardon my asking, but why would you make the backend database an MDE file?

I think what you are looking for is:

SELECT [Name]
FROM MSysObjects IN "C:\ECK ADMIN\AreaAdminBEMDE.mde"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'

Dale

Sorry to bother you again, Marshall, but I am having problem building the
string correctly for the sql and the query.
The back end is at C:\ECK ADMIN\AreaAdminBEMDE.mde
How should I put it into the code?
--
Glint

I need a query in my front end to retrieve the names of the tables in
the
back end. The two databases are in the same pc right now but will
connect
accross a network later.

This code should put the list of tables in a recordset.

Dim dbBE As Database
Dim BEpath As String
Dim SQL As String

SQL = "SELECT [Name] FROM MSysObjects " _
& "WHERE [Type] = 1 AND Not [Name] Like 'MSys*' "
BEpath = Mid(CurrentDb.TableDefs("any linked
table").Connect, 11)
Set dbBE = OpenDatabase(BEpath)
Set rs = dbBE.OpenRecordset(SQL, dbOpenSnapshot,
dbReadOnly)
Do Until rs.EOF
' do your thing here
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set dbBE = Nothing

If you only want a query and you can hard code the path,
then

SELECT [Name]
FROM MSysObjects IN "path to back end file"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'

 

[Glint]

Thanks for your help, Dale. Yes, that is exactly what I was looking for.

I made the back end mde in an attempt to minimize accidental corruption by
the various 'smart' users who liked to tinker with the file. I did not know
any other way to discourage them.
--
Glint

Pardon my asking, but why would you make the backend database an MDE file?

I think what you are looking for is:

SELECT [Name]
FROM MSysObjects IN "C:\ECK ADMIN\AreaAdminBEMDE.mde"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'

Dale

Sorry to bother you again, Marshall, but I am having problem building the
string correctly for the sql and the query.
The back end is at C:\ECK ADMIN\AreaAdminBEMDE.mde
How should I put it into the code?
--
Glint

Glint wrote:
I need a query in my front end to retrieve the names of the tables in
the
back end. The two databases are in the same pc right now but will
connect
accross a network later.


This code should put the list of tables in a recordset.

Dim dbBE As Database
Dim BEpath As String
Dim SQL As String

SQL = "SELECT [Name] FROM MSysObjects " _
& "WHERE [Type] = 1 AND Not [Name] Like 'MSys*' "
BEpath = Mid(CurrentDb.TableDefs("any linked
table").Connect, 11)
Set dbBE = OpenDatabase(BEpath)
Set rs = dbBE.OpenRecordset(SQL, dbOpenSnapshot,
dbReadOnly)
Do Until rs.EOF
' do your thing here
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set dbBE = Nothing

If you only want a query and you can hard code the path,
then

SELECT [Name]
FROM MSysObjects IN "path to back end file"
WHERE [Type] = 1 AND Not [Name] Like 'MSys*'