Solved

Object Definitions Update for 2010

Posted on 2013-01-20
2
269 Views
Last Modified: 2013-01-20
In Access 2003, a friend helped me to identify objects in a database with the code below.  It no longer works for Office 2010.  Can someone please help me update this code so it's functional and outputs to Excel 2010?

Function tableflds() As String
Dim Dbs As Database
Dim xlapp As Object
Dim hdr As Object
Dim tbls  As RecordsetDefs
Dim fld As Fields
Dim tbl As Integer, r As Integer, c As Integer
Dim x As Object, t As Object
Set Dbs = CurrentDb
Set tbls = Application.CurrentDb.TableDefs
Set xlapp = CreateObject("excel.Application")
xlapp.workbooks.Add
Set hdr = xlapp.activeworkbook.worksheets(1).range("A:A")
xlapp.Visible = True
c = 0
tbl = -1
For Each t In tbls
tbl = tbl + 1
r = 2
c = c + 1
hdr.cells(1, c).value = t.Name
Set fld = tbls(tbl).PageFields 'MOD: Changed from .fieldsl

For Each x In fld
hdr.cells(r, c).value = x.Name
r = r + 1
Next x
Set fld = Nothing
Next t
End Function
0
Comment
Question by:CFMI
2 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 38798476
Try this code:
Public Function tableflds() As String
Dim xlapp As Object
Dim hdr As Object
Dim tbls  As Object
Dim fld As Object
Dim tbl As Integer, r As Integer, c As Integer
Dim x As Object, t As Object
On Error GoTo err1
tableflds = True
Set tbls = Application.CurrentDb.TableDefs
Set xlapp = CreateObject("excel.Application")
xlapp.workbooks.Add
Set hdr = xlapp.activeworkbook.worksheets(1).range("A:A")
xlapp.Visible = True
c = 0
tbl = -1
For Each t In tbls
tbl = tbl + 1
r = 2
c = c + 1
hdr.cells(1, c).Value = t.Name
Set fld = tbls(tbl).Fields 'MOD: Changed from .fieldsl

For Each x In fld
hdr.cells(r, c).Value = x.Name
r = r + 1
Next x
Set fld = Nothing
Next t
Exit Function
err1:
    tableflds = False
End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 38798515
Perfect.  Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question