How to get table's name and field's name?

Sorry: mental lapse. I read Workspace for Worksheet.

Change

Dim ws As Worksheet

to

Dim ws As Object.

Now, the fact that deleting the line of code worked for you indicates that
you haven't told Access that all variables must be declared. In my opinion,
that's a huge mistake. Not requiring declaration of all variables can make
it difficult to find typos and other subtle errors in your code.

Each code module should have a line

Option Explicit

at the top (usually the second line, after Option Compare Database). You'll
have to add that line manually to all existing modules, but you can set up
the VB Editor to add that line automatically to all future modules by
selecting Tools | Options while in the VB Editor, finding the Module tab (I
believe it is) and ensuring that the "Require Variable Declaration" check
box is checked.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"XS" wrote in message
...
I checked "Microsoft DAO 3..6 Object Library ". But still has the same
error
for "Dim ws As Worksheet"....Is there anything else should be check?

I tried to delet the line of "Dim ws As Worksheet". It can be RUN and I
get
a Excel file.

Thanks very much!
XS


"Douglas J. Steele" wrote:

Go into Tools | References while in the VB Editor and make sure that the
entry for Microsoft DAO 3..6 Object Library is checked.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"XS" wrote in message
...
Hey kc-mass:

Thanks very much!!! that's what I want!

But when I use the code, it comes out
---------------------------
Compile error:

User-defined type not defined
---------------------------
for "Dim ws As Worksheet"

What should I do to fix that?

Thanks!!!!!!!!!!
XS


"kc-mass" wrote:

Try this it will put out the table and field names to an Excel file:

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count -
1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


Regards

Kevin



"XS" wrote in message
...
When I get 300 tables from SQL database, I want to get a report of
each
table's name and what contents they have. Is there any function or
using
VBA
to get table's and field's name???

Thank you very much!

It works! Thanks!

"Douglas J. Steele" wrote:

Sorry: mental lapse. I read Workspace for Worksheet.

Change

Dim ws As Worksheet

to

Dim ws As Object.

Now, the fact that deleting the line of code worked for you indicates that
you haven't told Access that all variables must be declared. In my opinion,
that's a huge mistake. Not requiring declaration of all variables can make
it difficult to find typos and other subtle errors in your code.

Each code module should have a line

Option Explicit

at the top (usually the second line, after Option Compare Database). You'll
have to add that line manually to all existing modules, but you can set up
the VB Editor to add that line automatically to all future modules by
selecting Tools | Options while in the VB Editor, finding the Module tab (I
believe it is) and ensuring that the "Require Variable Declaration" check
box is checked.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"XS" wrote in message
...
I checked "Microsoft DAO 3..6 Object Library ". But still has the same
error
for "Dim ws As Worksheet"....Is there anything else should be check?

I tried to delet the line of "Dim ws As Worksheet". It can be RUN and I
get
a Excel file.

Thanks very much!
XS


"Douglas J. Steele" wrote:

Go into Tools | References while in the VB Editor and make sure that the
entry for Microsoft DAO 3..6 Object Library is checked.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"XS" wrote in message
...
Hey kc-mass:

Thanks very much!!! that's what I want!

But when I use the code, it comes out
---------------------------
Compile error:

User-defined type not defined
---------------------------
for "Dim ws As Worksheet"

What should I do to fix that?

Thanks!!!!!!!!!!
XS


"kc-mass" wrote:

Try this it will put out the table and field names to an Excel file:

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count -
1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


Regards

Kevin



"XS" wrote in message
...
When I get 300 tables from SQL database, I want to get a report of
each
table's name and what contents they have. Is there any function or
using
VBA
to get table's and field's name???

Thank you very much!

The gentleman who suggested setting a reference to the Excel library is
correct. That will solve the problem.

Regards

Kevin

"XS" wrote in message
...
Hey kc-mass:

Thanks very much!!! that's what I want!

But when I use the code, it comes out
---------------------------
Compile error:

User-defined type not defined
---------------------------
for "Dim ws As Worksheet"

What should I do to fix that?

Thanks!!!!!!!!!!
XS


"kc-mass" wrote:

Try this it will put out the table and field names to an Excel file:

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


Regards

Kevin



"XS" wrote in message
...
When I get 300 tables from SQL database, I want to get a report of each
table's name and what contents they have. Is there any function or
using
VBA
to get table's and field's name???

Thank you very much!

While setting the reference to Excel will solve the problem, all the rest of
the code is consistent with Late Binding, so there's no need to set a
reference. You can only set a reference to a single version of Excel, so if
your users have a mix of versions installed, you'll run into problems.
Changing the declaration from Worksheet to Object will work with no
reference required, which means it's more flexible.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"kc-mass" connearney_AT_comcast_DOT_net wrote in message
...
The gentleman who suggested setting a reference to the Excel library is
correct. That will solve the problem.

Regards

Kevin

"XS" wrote in message
...
Hey kc-mass:

Thanks very much!!! that's what I want!

But when I use the code, it comes out
---------------------------
Compile error:

User-defined type not defined
---------------------------
for "Dim ws As Worksheet"

What should I do to fix that?

Thanks!!!!!!!!!!
XS


"kc-mass" wrote:

Try this it will put out the table and field names to an Excel file:

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


Regards

Kevin



"XS" wrote in message
...
When I get 300 tables from SQL database, I want to get a report of
each
table's name and what contents they have. Is there any function or
using
VBA
to get table's and field's name???

Thank you very much!

Douglas J. Steele wrote:
While setting the reference to Excel will solve the problem, all the rest of
the code is consistent with Late Binding, so there's no need to set a
reference. You can only set a reference to a single version of Excel, so if
your users have a mix of versions installed, you'll run into problems.
Changing the declaration from Worksheet to Object will work with no
reference required, which means it's more flexible.

The code includes several Excel constants (xlCenter, xlNone, xlEdgeTop,
etc.) Using those named constants requires a reference to Excel. If he
wants to eliminate the Excel reference he should revise the subroutine
to replace the named constants with the corresponding values.

"Hans Up" wrote in message
...
Douglas J. Steele wrote:
While setting the reference to Excel will solve the problem, all the rest
of the code is consistent with Late Binding, so there's no need to set a
reference. You can only set a reference to a single version of Excel, so
if your users have a mix of versions installed, you'll run into problems.
Changing the declaration from Worksheet to Object will work with no
reference required, which means it's more flexible.

The code includes several Excel constants (xlCenter, xlNone, xlEdgeTop,
etc.) Using those named constants requires a reference to Excel. If he
wants to eliminate the Excel reference he should revise the subroutine to
replace the named constants with the corresponding values.

You're right. I obviously missed the use of the constants.

On the other hand, the OP did report that changing the declaration to As
Object solved the problem, so perhaps the constants have been declared
already.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Hi kc-mass,

Thank you very much for your code. It really help.

Do you know how can I get the first record of each table?

I tried to use
"
..Range("E" & lngRow) = db.TableDefs(lngTable).Fields(lngField).**
"
is there any function to get the fist row?

Many thanks!

"kc-mass" wrote:

Try this it will put out the table and field names to an Excel file:

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyl e = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


Regards

Kevin



"XS" wrote in message
...
When I get 300 tables from SQL database, I want to get a report of each
table's name and what contents they have. Is there any function or using
VBA
to get table's and field's name???

Thank you very much!

On Fri, 10 Jul 2009 08:31:01 -0700, XS wrote:


Thank you very much for your code. It really help.

Do you know how can I get the first record of each table?

I tried to use
"
.Range("E" & lngRow) = db.TableDefs(lngTable).Fields(lngField).**
"
is there any function to get the fist row?

Access tables should be viewed as unordered "heaps" of data. A table has no
defined sort order, so there really is no "first" or "last" record. If you
want to see records in a particular order you must use a Query based on the
table with a sort order specified.
--

John W. Vinson [MVP]

Actually, I do not want to sort my data. I just want to get a sample data
from each table. Now I have already get the table's name and field's name, I
want it to be like this:
TableName FieldName RecordSample
PolicyFile ad_ID ????

Since I need to report on 600+ tables' name and contents. So I want to get
the sample of record for each table automaticlly.

Thanks!

"John W. Vinson" wrote:

On Fri, 10 Jul 2009 08:31:01 -0700, XS wrote:


Thank you very much for your code. It really help.

Do you know how can I get the first record of each table?

I tried to use
"
.Range("E" & lngRow) = db.TableDefs(lngTable).Fields(lngField).**
"
is there any function to get the fist row?

Access tables should be viewed as unordered "heaps" of data. A table has no
defined sort order, so there really is no "first" or "last" record. If you
want to see records in a particular order you must use a Query based on the
table with a sort order specified.
--

John W. Vinson [MVP]

The TableDef object does not let you get at the data contained within a
table.

You'd need either to create a recordset that returns one or more rows of
data and loop through the fields in that recordset, or do a number of
DLookups on the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"XS" wrote in message
...
Actually, I do not want to sort my data. I just want to get a sample data
from each table. Now I have already get the table's name and field's name,
I
want it to be like this:
TableName FieldName RecordSample
PolicyFile ad_ID ????

Since I need to report on 600+ tables' name and contents. So I want to get
the sample of record for each table automaticlly.

Thanks!

"John W. Vinson" wrote:

On Fri, 10 Jul 2009 08:31:01 -0700, XS
wrote:


Thank you very much for your code. It really help.

Do you know how can I get the first record of each table?

I tried to use
"
.Range("E" & lngRow) = db.TableDefs(lngTable).Fields(lngField).**
"
is there any function to get the fist row?

Access tables should be viewed as unordered "heaps" of data. A table has
no
defined sort order, so there really is no "first" or "last" record. If
you
want to see records in a particular order you must use a Query based on
the
table with a sort order specified.
--

John W. Vinson [MVP]