Adding Description to Table in vba

  • Thread starter Thread starter Claudette Hennessy
  • Start date Start date
C

Claudette Hennessy

Not sure which forum this belongs in: I am creating a table in order to
pass the contents to Word in a mail merge. I do this because the data to be
merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM qryDealerMailMerge
WHERE (((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table with
date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where clause I
guess I wouldn't have to do this?
 
Claudette,
have you looked into TransferText
DoCmd.TransferText - the VBA help on transfer text explains how to do it
You can pass the SQL of the query or the name of a saved query

Jeanette Cunningham
 
Description is one of those properties that doesn't exist until you assign
it a value. That means that if the table doesn't already have a description,
referring to CurrentDb.TableDefs(ObjectName).Properties("Description") will
raise error 3270 (Property not found). That means that when you're trying to
assign a value for the property, you need to trap that error and create the
property if it occurs:

Sub SetTableDescription(TableName As String, Description As String)
On Error GoTo SetTableDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpNew As DAO.Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
Set tdfCurr.Properties("Description") = Description

End_SetTableDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

Err_SetTableDescription:
Select Case Err.Number
Case 3270 ' Property Not Found
Set prpNew = tdfCurr.CreateDescription( _
"Description", dbText, Description)
tdfCurr.Properties.Append prpNew
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume End_SetTableDescription

End Sub
 
Thank you Douglas. this helps me understand.

I changed the SQL statement to include a date in the table name..
strYear = Year(Date)
strtablename = "tblSpringUpdate" & strYear

SQL = "SELECT qryDealerMailMerge.ShowID, ...
qryDealerMailMerge.NYS_Tax_ID INTO " & strtablename & " FROM
qryDealerMailMerge WHERE (((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

this produces a table named tblSpringUpdate 2008 .

DoCmd.RunSQL SQL
Up to here it works fine.


Call SetTableDescription(strtablename, "Contains Spring Dealer data")

I created a sub with your code (Again thanks)

Sub SetTableDescription(TableName As String, Description As String)
On Error GoTo SetTableDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpNew As DAO.Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
Set tdfCurr.Properties("Description") = Description !It fails here with a
compile error, invalid use of property.

End_SetTableDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

Err_SetTableDescription:
Select Case Err.Number
Case 3270 ' Property Not Found
Set prpNew = tdfCurr.CreateDescription( _
"Description", dbText, Description)
tdfCurr.Properties.Append prpNew
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume End_SetTableDescription

End Sub

Any ideas?

Claudette
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Claudette Hennessy said:
Not sure which forum this belongs in: I am creating a table in order to
pass the contents to Word in a mail merge. I do this because the data to
be merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table
with date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where clause
I guess I wouldn't have to do this?
Click to expand...
Click to expand...
 
Thank you, Jeannette, but the issue is the criterion is chosen from a form
by the user, and TransferText does not accept SQL, it must be a saved query.
So I elected to create a table in SQL and create a description for it.

Claudette
Jeanette Cunningham said:
Claudette,
have you looked into TransferText
DoCmd.TransferText - the VBA help on transfer text explains how to do it
You can pass the SQL of the query or the name of a saved query

Jeanette Cunningham

Claudette Hennessy said:
Not sure which forum this belongs in: I am creating a table in order to
pass the contents to Word in a mail merge. I do this because the data to
be merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table
with date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where clause
I guess I wouldn't have to do this?
Click to expand...
Click to expand...
 
Sorry, my fault. You don't need the Set keyword there. It should just be

tdfCurr.Properties("Description") = Description


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Claudette Hennessy said:
Thank you Douglas. this helps me understand.

I changed the SQL statement to include a date in the table name..
strYear = Year(Date)
strtablename = "tblSpringUpdate" & strYear

SQL = "SELECT qryDealerMailMerge.ShowID, ...
qryDealerMailMerge.NYS_Tax_ID INTO " & strtablename & " FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

this produces a table named tblSpringUpdate 2008 .

DoCmd.RunSQL SQL
Up to here it works fine.


Call SetTableDescription(strtablename, "Contains Spring Dealer data")

I created a sub with your code (Again thanks)

Sub SetTableDescription(TableName As String, Description As String)
On Error GoTo SetTableDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpNew As DAO.Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
Set tdfCurr.Properties("Description") = Description !It fails here with
a compile error, invalid use of property.

End_SetTableDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

Err_SetTableDescription:
Select Case Err.Number
Case 3270 ' Property Not Found
Set prpNew = tdfCurr.CreateDescription( _
"Description", dbText, Description)
tdfCurr.Properties.Append prpNew
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume End_SetTableDescription

End Sub

Any ideas?

Claudette
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Claudette Hennessy said:
Not sure which forum this belongs in: I am creating a table in order to
pass the contents to Word in a mail merge. I do this because the data
to be merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table
with date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where clause
I guess I wouldn't have to do this?
Click to expand...
Click to expand...
Click to expand...
 
Claudette,
it is fairly easy to create a saved query that collects the criterion from
the form and export the saved query using TransferText.

Jeanette Cunningham

Claudette Hennessy said:
Thank you, Jeannette, but the issue is the criterion is chosen from a form
by the user, and TransferText does not accept SQL, it must be a saved
query. So I elected to create a table in SQL and create a description
for it.

Claudette
Jeanette Cunningham said:
Claudette,
have you looked into TransferText
DoCmd.TransferText - the VBA help on transfer text explains how to do it
You can pass the SQL of the query or the name of a saved query

Jeanette Cunningham

Claudette Hennessy said:
Not sure which forum this belongs in: I am creating a table in order to
pass the contents to Word in a mail merge. I do this because the data
to be merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table
with date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where clause
I guess I wouldn't have to do this?
Click to expand...
Click to expand...
Click to expand...
 
Fixed that, then it failed at

Set prpNew = tdfCurr.CreateDescription("Description", dbText, Description)

Changed it to:
Set prpNew = tdfCurr.CreateProperty("Description", dbText, Description) and
it worked fine.
Thank God for you folks, I had no idea that dbText and its cousins even
existed.
Claudette

Set prpNew = tdfCurr.CreateProperty("Description", db, Description)
Douglas J. Steele said:
Sorry, my fault. You don't need the Set keyword there. It should just be

tdfCurr.Properties("Description") = Description


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Claudette Hennessy said:
Thank you Douglas. this helps me understand.

I changed the SQL statement to include a date in the table name..
strYear = Year(Date)
strtablename = "tblSpringUpdate" & strYear

SQL = "SELECT qryDealerMailMerge.ShowID, ...
qryDealerMailMerge.NYS_Tax_ID INTO " & strtablename & " FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

this produces a table named tblSpringUpdate 2008 .

DoCmd.RunSQL SQL
Up to here it works fine.


Call SetTableDescription(strtablename, "Contains Spring Dealer data")

I created a sub with your code (Again thanks)

Sub SetTableDescription(TableName As String, Description As String)
On Error GoTo SetTableDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpNew As DAO.Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
Set tdfCurr.Properties("Description") = Description !It fails here with
a compile error, invalid use of property.

End_SetTableDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

Err_SetTableDescription:
Select Case Err.Number
Case 3270 ' Property Not Found
Set prpNew = tdfCurr.CreateDescription( _
"Description", dbText, Description)
tdfCurr.Properties.Append prpNew
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume End_SetTableDescription

End Sub

Any ideas?

Claudette
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Not sure which forum this belongs in: I am creating a table in order
to pass the contents to Word in a mail merge. I do this because the
data to be merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table
with date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where
clause I guess I wouldn't have to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top