By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,160 Members | 1,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,160 IT Pros & Developers. It's quick & easy.

How can I get all table names from MS Access

P: n/a
I'm using VB and I need to get all table names in database. How can I do
that?

Thank you.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Mlaky wrote:
I'm using VB and I need to get all table names in database. How can I do
that?

Thank you.

select name from msysobjects where type=1

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #2

P: n/a
Public Function GetTableNamesDAO(ByVal strDatabase As String) As String()

'requires reference to DAO object library

Dim straTableNames() As String
Dim db As DAO.Database
Dim lngLoop As Long

Set db = DBEngine.OpenDatabase(strDatabase)
ReDim straTableNames(db.TableDefs.Count - 1)
For lngLoop = 0 To db.TableDefs.Count - 1
straTableNames(lngLoop) = db.TableDefs(lngLoop).Name
Next lngLoop
db.Close
GetTableNamesDAO = straTableNames

End Function

Public Sub TestGetTableNamesDAO()

'used for testing within Access - will need modification for VB

Dim straTableNames() As String
Dim lngLoop As Long

straTableNames = GetTableNamesDAO(CurrentProject.FullName)
For lngLoop = LBound(straTableNames) To UBound(straTableNames)
Debug.Print straTableNames(lngLoop)
Next lngLoop

End Sub

Public Function GetTableNamesADOX(ByVal TheConnection As ADODB.Connection)
As String()

'requires references to ADODB and ADOX object libraries

Dim straTableNames() As String
Dim cat As ADOX.Catalog
Dim lngLoop As Long
Dim boolOpenedConnection As Boolean

If TheConnection.State <> adStateOpen Then
TheConnection.Open
boolOpenedConnection = True
End If
Set cat = New ADOX.Catalog
cat.ActiveConnection = TheConnection
ReDim straTableNames(cat.Tables.Count - 1)
For lngLoop = 0 To cat.Tables.Count - 1
straTableNames(lngLoop) = cat.Tables(lngLoop).Name
Next lngLoop
GetTableNamesADOX = straTableNames
If boolOpenedConnection Then
TheConnection.Close
End If

End Function

Public Function TestGetTableNamesADOX()

'used for testing within Access - will need modification for VB

Dim straTableNames() As String
Dim lngLoop As Long

straTableNames = GetTableNamesADOX(CurrentProject.Connection)
For lngLoop = LBound(straTableNames) To UBound(straTableNames)
Debug.Print straTableNames(lngLoop)
Next lngLoop

End Function

--
Brendan Reynolds
"Mlaky" <ml************@email.htnet.hr> wrote in message
news:c0**********@ls219.htnet.hr...
I'm using VB and I need to get all table names in database. How can I do
that?

Thank you.

Nov 12 '05 #3

P: n/a
I can't do that. I've got following message:

Error: Record(s) cannot be read; no read permission on 'msysobjects'.
select name from msysobjects where type=1

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #4

P: n/a
"Mlaky" <ml************@email.htnet.hr> wrote in news:c0sto0$knj$1
@ls219.htnet.hr:
I can't do that. I've got following message:
Error: Record(s) cannot be read; no read permission on 'msysobjects'.
select name from msysobjects where type=1
--
Bas Cost Budde


For Version >= 2K:

Public Function GetTableNames() As ADODB.Recordset
Set GetTableNames = CurrentProject.Connection.OpenSchema
(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
End Function

Sub test()
Debug.Print GetTableNames.GetString(, , ",", , vbTab)
End Sub
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.