L
lpblove
I do not want to use the documenter, because I want a file that I can export
to excel, not a report format.
to excel, not a report format.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
6
'69 Camaro
Hi.
If you're interested in the field names as columns in the spreadsheet, then
use a query to export an empty table, with only the column headers, to a
spreadsheet:
SELECT * INTO [Text;HDR=YES;DATABASE=C:\Data\].FieldNames.CSV
FROM MyTable
WHERE (1 = 0);
Then open the C:\Data\FieldNames.CSV file in Excel.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
I do not want to use the documenter, because I want a file that I can
export
to excel, not a report format.
If you're interested in the field names as columns in the spreadsheet, then
use a query to export an empty table, with only the column headers, to a
spreadsheet:
SELECT * INTO [Text;HDR=YES;DATABASE=C:\Data\].FieldNames.CSV
FROM MyTable
WHERE (1 = 0);
Then open the C:\Data\FieldNames.CSV file in Excel.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
L
lpblove
hat was a great suggestion. Thank you. Unfortunately, I didn't explain
fully, I apologize...
I want to have the information as if I had used the documenter, just not the
report format the documenter generates. So Field Names and the field
Properties. It would be fine to even build a table with this info so I can
export it when I want to. I am having trouble finding out how to get both
the field names and properties. Thank you.
fully, I apologize...
I want to have the information as if I had used the documenter, just not the
report format the documenter generates. So Field Names and the field
Properties. It would be fine to even build a table with this info so I can
export it when I want to. I am having trouble finding out how to get both
the field names and properties. Thank you.
'69 Camaro said:Hi.
I do not want to use the documenter, because I want a file that I can
export
to excel, not a report format.
If you're interested in the field names as columns in the spreadsheet, then
use a query to export an empty table, with only the column headers, to a
spreadsheet:
SELECT * INTO [Text;HDR=YES;DATABASE=C:\Data\].FieldNames.CSV
FROM MyTable
WHERE (1 = 0);
Then open the C:\Data\FieldNames.CSV file in Excel.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
6
'69 Camaro
Hi.
Use the DAO library for the Field.Name, Field.Property.Name, and
Field.Property.Value Properties. For an example, first create a query that
creates a table to hold the data:
CREATE TABLE tblColNames
(ID COUNTER (1, 1),
TableName Text (64) NOT NULL,
ColName Text (64) NOT NULL,
PropName Text (35) NOT NULL,
PropValue Text (255),
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
CONSTRAINT UQ_IDX UNIQUE (TableName, ColName, PropName));
And then try the following VBA procedure:
Public Function getColNamesAndProps()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim sqlStmt As String
Dim fSkipExecute As Boolean
Set db = CurrentDb()
For Each tbl In db.TableDefs
If ((Mid$(tbl.Name, 1, 4) <> "MSys") And _
(Mid$(tbl.Name, 1, 7) <> "~TMPCLP")) Then
For Each fld In tbl.Fields
For Each prp In fld.Properties
sqlStmt = "INSERT INTO tblColNames (TableName, " & _
"ColName, PropName, PropValue) " & _
"VALUES ('" & Replace(tbl.Name, "'", "''", 1, -1, _
vbDatabaseCompare) & "', '" & Replace(fld.Name,
_
"'", "''", 1, -1, vbDatabaseCompare) & "', '" &
_
prp.Name & "', '" & Replace(CStr(prp.Value),
"'", "''", _
1, -1, vbDatabaseCompare) & "');"
If (Not (fSkipExecute)) Then
db.Execute sqlStmt, dbFailOnError
Else
fSkipExecute = False ' Reset.
End If
Next prp
Next fld
End If
Next tbl
CleanUp:
Set prp = Nothing
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Function
ErrHandler:
If ((Err.Number = 3219) Or (Err.Number = 3251) Or (Err.Number = 3267))
Then
Err.Clear
fSkipExecute = True
Resume Next
Else
MsgBox "Error in getColNamesAndProps( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
GoTo CleanUp
End Function
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
I am having trouble finding out how to get both
the field names and properties.
Use the DAO library for the Field.Name, Field.Property.Name, and
Field.Property.Value Properties. For an example, first create a query that
creates a table to hold the data:
CREATE TABLE tblColNames
(ID COUNTER (1, 1),
TableName Text (64) NOT NULL,
ColName Text (64) NOT NULL,
PropName Text (35) NOT NULL,
PropValue Text (255),
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
CONSTRAINT UQ_IDX UNIQUE (TableName, ColName, PropName));
And then try the following VBA procedure:
Public Function getColNamesAndProps()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim sqlStmt As String
Dim fSkipExecute As Boolean
Set db = CurrentDb()
For Each tbl In db.TableDefs
If ((Mid$(tbl.Name, 1, 4) <> "MSys") And _
(Mid$(tbl.Name, 1, 7) <> "~TMPCLP")) Then
For Each fld In tbl.Fields
For Each prp In fld.Properties
sqlStmt = "INSERT INTO tblColNames (TableName, " & _
"ColName, PropName, PropValue) " & _
"VALUES ('" & Replace(tbl.Name, "'", "''", 1, -1, _
vbDatabaseCompare) & "', '" & Replace(fld.Name,
_
"'", "''", 1, -1, vbDatabaseCompare) & "', '" &
_
prp.Name & "', '" & Replace(CStr(prp.Value),
"'", "''", _
1, -1, vbDatabaseCompare) & "');"
If (Not (fSkipExecute)) Then
db.Execute sqlStmt, dbFailOnError
Else
fSkipExecute = False ' Reset.
End If
Next prp
Next fld
End If
Next tbl
CleanUp:
Set prp = Nothing
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Function
ErrHandler:
If ((Err.Number = 3219) Or (Err.Number = 3251) Or (Err.Number = 3267))
Then
Err.Clear
fSkipExecute = True
Resume Next
Else
MsgBox "Error in getColNamesAndProps( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
GoTo CleanUp
End Function
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
H
HELENA SMITH
[email protected] hi are you
H
HELENA SMITH
sweet37helena@googlecom hi are you
L
lpblove
Worked perfect. Thank you so much!
'69 Camaro said:Hi.
Use the DAO library for the Field.Name, Field.Property.Name, and
Field.Property.Value Properties. For an example, first create a query that
creates a table to hold the data:
CREATE TABLE tblColNames
(ID COUNTER (1, 1),
TableName Text (64) NOT NULL,
ColName Text (64) NOT NULL,
PropName Text (35) NOT NULL,
PropValue Text (255),
CONSTRAINT PrimaryKey PRIMARY KEY (ID),
CONSTRAINT UQ_IDX UNIQUE (TableName, ColName, PropName));
And then try the following VBA procedure:
Public Function getColNamesAndProps()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim sqlStmt As String
Dim fSkipExecute As Boolean
Set db = CurrentDb()
For Each tbl In db.TableDefs
If ((Mid$(tbl.Name, 1, 4) <> "MSys") And _
(Mid$(tbl.Name, 1, 7) <> "~TMPCLP")) Then
For Each fld In tbl.Fields
For Each prp In fld.Properties
sqlStmt = "INSERT INTO tblColNames (TableName, " & _
"ColName, PropName, PropValue) " & _
"VALUES ('" & Replace(tbl.Name, "'", "''", 1, -1, _
vbDatabaseCompare) & "', '" & Replace(fld.Name,
_
"'", "''", 1, -1, vbDatabaseCompare) & "', '" &
_
prp.Name & "', '" & Replace(CStr(prp.Value),
"'", "''", _
1, -1, vbDatabaseCompare) & "');"
If (Not (fSkipExecute)) Then
db.Execute sqlStmt, dbFailOnError
Else
fSkipExecute = False ' Reset.
End If
Next prp
Next fld
End If
Next tbl
CleanUp:
Set prp = Nothing
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Function
ErrHandler:
If ((Err.Number = 3219) Or (Err.Number = 3251) Or (Err.Number = 3267))
Then
Err.Clear
fSkipExecute = True
Resume Next
Else
MsgBox "Error in getColNamesAndProps( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
GoTo CleanUp
End Function
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
6
'69 Camaro
Worked perfect. Thank you so much!
You're welcome! Glad it helped.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
You're welcome! Glad it helped.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.